Hello,

I need help making a Customer ledger report which will gather all information for a selected customer from 3 different tables, tblInvoices, tblPayments and tblCredits, and then sort it ascending by date. I have created a query for each table and the queries pull the correct information. Can dates be sorted ascending no matter which table the information is taken from?

The table fields are as follows:

tblInvoices

InvoiceID PK
InvoiceNumber
CustomerID Related to tblCustomers
InvoiceDate
InvoiceAmount


tblPayments

PaymentID PK
CustomerID Related to tblCustomers
InvoiceID Related to tblInvoices
PaymentDate
DiscountAmount
PaymentAmount


tblCredits

CreditID PK
CustomerID Related to tblCustomers
InvoiceID Related to tblInvoices
CreditDate
CreditAmount


Thanks in advance for any help and/or suggestions.




D. Bartelt

__________________
D. Bartelt

You have paymentDate, InvoiceDate, CreditDate. Which one you want to use?

??VBA and ACCESS ??

The PaymentDate of a specific Invoice may be 30-60 days after a specific InvoiceDate, however, there will be new Invoices generated between InvoiceDate for Invoice 123 and the PaymentDate for Invoice 123. I need to use all 3 date fields. I need to pull all of the specific customer transactions from all 3 tables and then organize them by date. I don't think this is possible, is it?

I am still using Access 97 at my company. Not my choice!

D. Bartelt

Hi again,

I have solved most of my request by using this select query.

SELECT tblInvoices.CustomerID, tblInvoices.InvoiceID, tblInvoices.InvoiceNumber, tblInvoices.InvoiceDate, tblInvoices.InvoiceTotal, tblPayments.InvoiceID, tblPayments.PaymentID, tblPayments.PaymentDate, tblPayments.CheckNumber, tblPayments.DiscountAmount, tblPayments.PaymentAmount, tblCredits.InvoiceID, tblCredits.CreditID, tblCredits.CreditNumber, tblCredits.CreditDate, tblCredits.CreditAmount
FROM ((tblCustomers RIGHT JOIN tblInvoices ON tblCustomers.CustomerID = tblInvoices.CustomerID) LEFT JOIN tblCredits ON tblInvoices.InvoiceID = tblCredits.InvoiceID) LEFT JOIN tblPayments ON tblInvoices.InvoiceID = tblPayments.InvoiceID
WHERE (((tblInvoices.CustomerID)=[Forms]![frmToday].[CustomerID]) AND ((tblInvoices.InvoiceDate)>=[Forms]![frmToday]![StartDate] And (tblInvoices.InvoiceDate)<=[Forms]![frmToday]![EndDate]))
ORDER BY tblInvoices.CustomerID, tblInvoices.InvoiceID;



I have placed a text box on the ledger named BALANCE. The purpose of the Balance Text Box is to show the "living balance" of the customers' account. The Control Source is: =([InvoiceTotal])-([DiscountAmount]+[PaymentAmount]+[CreditAmount]) and I set it’s properties as running sum - over all. This works fine as long as each invoice is paid in full. When there are 2 or more payments made for the same invoice, it adds the same invoice total again for each payment. How can I select the invoice total only once no matter how many payments or credits there are for a specific invoice?

Thanks for any help!


D. Bartelt