Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 17th, 2004, 10:25 AM
Registered User
 
Join Date: Feb 2004
Location: Lakeland, Florida, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Group By Question

I have a table called InvoiceDetails that has the following fields:

InvoiceID
ProductID
Qty (quantity bought)
Price (product price)

I have a second table called CustPayments with the following fields:

InvoiceID
Date
Payment

What I'm trying to do in one query is to obtain the balance due for each InvoiceID. So far I have the following:

SELECT InvoiceDetails.InvoiceID, Sum([Price]*[Qty]) AS [Total Due], CustPayments.Payment
FROM InvoiceDetails INNER JOIN CustPayments ON InvoiceDetails.InvoiceID = CustPayments.InvoiceID
GROUP BY InvoiceDetails.InvoiceID, CustPayments.Payment;

How do I subtract the payments made from the total due to get the balance due in this Group By query?

This is what is I want to happen, step-by-step:

1. Sum the (Price * Qty) for each InvoiceDetails.InvoiceID
2. Sum the Payment for each CustPayments.InvoiceID
3. Subtract step 2 from step 1 for each InvoiceID to get the balance due


Reply With Quote
  #2 (permalink)  
Old February 18th, 2004, 09:14 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

I assume you have a many-to-many link between InvoiceDetails and CustPayments? If so you won't get very far joining these 2 tables because that will result in some values being counted multiple times. For example if a certain invoice has 3 product records and 2 payment records you'll end up with 6 records in the joined table, which means your "Due" will be counted twice and your "Paid" will be counted 3 times.

Try something like this nasty-looking bit of SQL:
Code:
SELECT tmp.InvoiceID, SUM(tmp.TotalDue) AS BalanceDue
FROM [
SELECT InvoiceDetails.InvoiceID, Sum(InvoiceDetails.Qty*InvoiceDetails.Price) AS TotalDue
FROM InvoiceDetails
GROUP BY InvoiceDetails.InvoiceID
UNION
SELECT CustPayments.InvoiceID, -1*Sum(CustPayments.Payment) AS TotalDue
FROM CustPayments
GROUP BY CustPayments.InvoiceID
]. AS tmp
GROUP BY tmp.InvoiceID;
hth
Phil
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Group Within another Group, xslt1.0 jhansib4u BOOK: XSLT Programmer's Reference, 2nd Edition 4 November 22nd, 2007 01:24 AM
Restart new group number in Group Footer sukarso Crystal Reports 2 October 13th, 2006 12:11 PM
Group by , Sub Group by and Sum mateenmohd SQL Server 2000 1 March 29th, 2005 09:51 AM
Group question Tere Crystal Reports 1 October 19th, 2004 11:08 AM
GROUP BY and ORDER BY question savoym SQL Language 6 June 24th, 2003 08:24 AM



All times are GMT -4. The time now is 08:27 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.