planetdrouin
Technical User
Hello,
I have a query which essentially brings in two tables. They are TblInvoices and TblReceipts.
Both tables look like (with some other fields):
Date InvoiceID ClientID Amount
However, the InvoiceID is unique in TblInvoices but not in TblReceipts as some customers pay in multiple instalments. I am trying to run a query which will provide the following output:
InvoiceID ClientID AmountInvoiced AmountReceived
The catch is that I want the user to be able to enter a parameter which asks for the month the invoice was either sent or received. Since multiple AmountReceived exist for some invoices, the total AmountInvoiced is incorrect (multiplied by the number of instalments the customer made).
My SQL is as follows:
SELECT TblInvoices.InvoiceID, TblInvoices.ClientID, "insert complicated formula" AS AmountInvoiced, "insert less complicated formula" AS AmountReceived, Format(IIf([forms]![frmUserInput].[DateType]="Invoiced",[InvoiceDate],[DateReceived]),"m"
AS [Month]
FROM TblInvoices LEFT JOIN TblReceipts ON TblInvoices.InvoiceID = TblReceipts.InvoiceID
GROUP BY TblInvoices.InvoiceID, TblInvoices.ClientID, Format(IIf([forms]![frmUserInput].[DateType]="Invoiced",[InvoiceDate],[DateReceived]),"m"
What do I need to do in order to have it only count the AmountInvoiced once but all of the AmountReceived while still allowing the user to pick a sent or received date. Any help is greatly appreciated.
Lawrence
I have a query which essentially brings in two tables. They are TblInvoices and TblReceipts.
Both tables look like (with some other fields):
Date InvoiceID ClientID Amount
However, the InvoiceID is unique in TblInvoices but not in TblReceipts as some customers pay in multiple instalments. I am trying to run a query which will provide the following output:
InvoiceID ClientID AmountInvoiced AmountReceived
The catch is that I want the user to be able to enter a parameter which asks for the month the invoice was either sent or received. Since multiple AmountReceived exist for some invoices, the total AmountInvoiced is incorrect (multiplied by the number of instalments the customer made).
My SQL is as follows:
SELECT TblInvoices.InvoiceID, TblInvoices.ClientID, "insert complicated formula" AS AmountInvoiced, "insert less complicated formula" AS AmountReceived, Format(IIf([forms]![frmUserInput].[DateType]="Invoiced",[InvoiceDate],[DateReceived]),"m"
AS [Month]FROM TblInvoices LEFT JOIN TblReceipts ON TblInvoices.InvoiceID = TblReceipts.InvoiceID
GROUP BY TblInvoices.InvoiceID, TblInvoices.ClientID, Format(IIf([forms]![frmUserInput].[DateType]="Invoiced",[InvoiceDate],[DateReceived]),"m"
;What do I need to do in order to have it only count the AmountInvoiced once but all of the AmountReceived while still allowing the user to pick a sent or received date. Any help is greatly appreciated.
Lawrence
