Wrox Programmer Forums
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #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


  #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


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





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