Re: [MS_AccessPros] Order Lines Totals Query

 

Andrew-

That's a common error.  My other favorite is folks putting a filter on the "right" side of a "left" join and then expecting to get the correct results.

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 

http://www.viescas.com/ 

(Paris, France)

On Mar 12, 2016, at 11:07 PM, Andrew Wilson andrew@adwsystems.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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 

http://www.viescas.com/ 

(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: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

---

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.

---

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___