Multiple Tables and Record Counting

  • Thread starter Thread starter Paul Denham
  • Start date Start date
P

Paul Denham

Hi all,

I do hope you can help me on this one.

I am designing a complete office database to sort out the accounting. I
have created the following tables:
[ADDRESSES]
[INVOICES]
[CREDITNOTES]
[PAYMENTS]

The last three tables are linked to the first by the Primary Key Field
(AddressID)

What I am trying to do is create a Query which filters the [INVOICES] to
show only the invoices for that particular customer, and the same with
the [CREDITNOTES] and [PAYMENTS] tables. I need to then add the totals
of all the records in each table for that customer. I then need to do a
calculation like:

((Addresses.StartingBalance - Invoices.InvoicesTotal +
CreditNotes.CreditNotesTotal) + IVA) - Payments.PaymentTotal

Don't worry about the IVA part.

How do I total the records of the records for that particular customer?

Thanks in anticipation of all your much needed help.

Paul Denham
 
Aggregation of data, be it Count or Sum, is performed by using the Group By
Clause with the respective operator.

For example, the following will count the number of products per category
SELECT CategoryID, Count(ProductID) From Products
Group By CategoryID

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Back
Top