Thanks John, you gave me a good nudge in the right direction there.
I already had the LEFT JOIN, but was Grouping and Ordering on the Order-Lines.Order-Number rather than Order-Header.Order-Number & just didn't see it. Using Nz converts everything to zero rather than showing blank fields in the query output which is just what I wanted.
Regards,
Andrew
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 12 March 2016 21:07
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Order Lines Totals Query
Andrew-
Use a LEFT JOIN from Order-Headers to Order-Lines, then use NZ to convert your totals calculation to zero if there are no rows before you do the Sum.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Mar 12, 2016, at 9:43 PM, andrew@adwsystems.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi All,
I'm scratching my head with this one, but i'm sure someone here will have a solution.
I have an Access database with Order-Headers and Order-Lines in a Parent/Child relationship linked on the Order-Number. The Order-Lines table has fields for Order-Number, Item-Number, Qty, Item-Cost, Items-Net, %Markup, VAT, Items-Gross & some other descriptive fields. I want to create a query that shows the Order-Number plus the totals for all the items on that order. This I can do easily enough; where I'm stuck is how to get the query to show totals for any orders that have no lines. I realise that the Totals for an order with no lines will be all zero, but I need to see them in the query results.
Any help greatly appreciated.
Thanks,
Andrew
Posted by: Andrew Wilson <andrew@adwsystems.co.uk>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar