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
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: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Save time and get your email on the go with the Yahoo Mail App
Get the beautifully designed, lighting fast, and easy-to-use, Yahoo Mail app today. Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar