query groups

[PeterS]

How do I group the same number in a query?



I have a query that reflects an invoice number with multiple payments allocated to it:

eg: Inv1234 for R3000.00 has three payments of R1000.00 on different dates
The query lists the invoice three times with the relevant payment date.

Can I get the query to display the invoice once with the allocated sum or the payments?
eg: Inv1234 R3000.00 SumOfPayments R3000.00 Balance_Due R0.00

[CJ_London]

Use a group by query, otherwise known as an aggregate query. See the sigma character on the ribbon in query design view

[ranman256]

in the query turn on GROUPs/summary, (button on toolbar with Greek Sigma)

fields: Inv# , Sum(AmtPd)
this gives 1 record

if you use:

Inv# , PayDate, Sum(AmtPd)
you may get 3 records because there could be 3 dates.

[PeterS]

Thanks for the help but it does not give me the desired result. This is my SQL Statement:

SELECT tblClient.Client_Name, tblCustomer.Customer_Name, tblPmts.Pmt_Date, tblInvoice.Invoice_Amount, tblPmts.Pmt_Amount, tblInvoice.Discount, [Invoice_Amount]-[Pmt_Amount]-[Discount] AS Closed
FROM (tblClient INNER JOIN (tblCustomer INNER JOIN tblInvoice ON tblCustomer.CustomerID_Pk = tblInvoice.CustomerID_Fk) ON tblClient.ClientID_Pk = tblInvoice.ClientID_Fk) LEFT JOIN tblPmts ON tblInvoice.InvoiceID_Pk = tblPmts.InvoiceID_Fk
WHERE (((tblClient.Client_Name)=[Forms]![frmBalance].[cboClientName]));

I created another query as a test which does work. This is the SQL Statement for it;

SELECT tblCustomer.Customer_Name, Sum(tblInvoice.Invoice_Number) AS SumOfInvoice_Number, tblInvoice.Invoice_Amount, Sum(tblPmts.Pmt_Amount) AS SumOfPmt_Amount, tblInvoice.Discount
FROM (tblCustomer INNER JOIN tblInvoice ON tblCustomer.CustomerID_Pk = tblInvoice.CustomerID_Fk) INNER JOIN tblPmts ON tblInvoice.InvoiceID_Pk = tblPmts.InvoiceID_Fk
GROUP BY tblCustomer.Customer_Name, tblInvoice.Invoice_Amount, tblInvoice.Discount;

What am I doing wrong?

[CJ_London]

You need to change group by to sum where you want a total - presumably amount and discount

[PeterS]

OK Now I am stumped....I closed the database, re-opened and summed the query exactly the same way I did it the first time and now it works

[CJ_London]

If you look at the sql you will probably see you are no longer grouping on amount and discount

[PeterS]

Ok so I have got is to sort of work. I just cant get it to work 100%.
This is the table

InvoiceID_Pk	CustomerID_Fk	Invoice_Number	Invoice_Date	Invoice_Amount	Statement_Date	Notes	Finish_Date	ClientID_Fk	PmtDate	Account_TermID_Fk	Discount
17	7	49274	2018/12/31	R7 935,00	2018/12/31	CHECKERS HERMANUS	2019/02/10	1	2019/02/10		R0,00
18	7	48998	2018/11/30	R12 822,50	2018/11/30	CHECKERS HERMANUS	2019/02/12	1	2019/02/12		R0,00
19	7	49436	2019/01/31	R5 068,00	2019/01/31	CHECKERS HERMANUS	2019/02/15	1	2019/02/15		R0,00
20	7	49437	2019/01/31	R5 428,00	2019/01/31	CHECKERS WHALE COAST MALL	2019/02/15	1	2019/02/15		R0,00
21	7	48997	2018/11/18	R12 822,50	2018/11/30	CHECKERS WHALE COAST MALL	2019/02/12	1	2019/02/12		R0,00
22	7	49275	2018/12/31	R7 935,00	2018/12/31	CHECKERS HERMANUS	2019/02/15	1	2019/02/15		R0,00

There are 6 invoices

This is the query result;

Client_Name	Customer_Name	SumOfPmt_Date	Invoice_Amount	SumOfPmt_Amount	Discount
SA Forklift	SHOPRITE CHECKERS PTY LTD	43511	R5 068,00	R5 068,00	R0,00
SA Forklift	SHOPRITE CHECKERS PTY LTD	43511	R5 428,00	R5 428,00	R0,00
SA Forklift	SHOPRITE CHECKERS PTY LTD	87017	R7 935,00	R15 870,00	R0,00
SA Forklift	SHOPRITE CHECKERS PTY LTD	87016	R12 822,50	R25 645,00	R0,00

As you can see the grouping is on invoice date and payment. I have basically lost two invoices, one for R7935.00 and one for R12822.50. The payments reflect correctly but the amounts are not. I have tried changing the dates thinking they were grouped together because of that but the result is the same.

This is the SQL

SELECT tblClient.Client_Name, tblCustomer.Customer_Name, Sum(tblPmts.Pmt_Date) AS SumOfPmt_Date, tblInvoice.Invoice_Amount, Sum(tblPmts.Pmt_Amount) AS SumOfPmt_Amount, tblInvoice.Discount
FROM (tblClient INNER JOIN (tblCustomer INNER JOIN tblInvoice ON tblCustomer.CustomerID_Pk = tblInvoice.CustomerID_Fk) ON tblClient.ClientID_Pk = tblInvoice.ClientID_Fk) LEFT JOIN tblPmts ON tblInvoice.InvoiceID_Pk = tblPmts.InvoiceID_Fk
GROUP BY tblClient.Client_Name, tblCustomer.Customer_Name, tblInvoice.Invoice_Amount, tblInvoice.Discount
HAVING (((tblClient.Client_Name)=[Forms]![frmBalance].[cboClientName]));

Can you see what is going wrong?

[PeterS]

so I have figured out something....

If I change the invoice amounts by R0.01 then all the invoices appear in the query as I want them to EXCEPTING the changed amount of 0.01 as you can see in the query result below;

Client_Name	Customer_Name	SumOfPmt_Date	Invoice_Amount	SumOfPmt_Amount	Discount
SA Forklift	SHOPRITE CHECKERS PTY LTD	43511	R5 068,00	R5 068,00	R0,00
SA Forklift	SHOPRITE CHECKERS PTY LTD	43511	R5 428,00	R5 428,00	R0,00
SA Forklift	SHOPRITE CHECKERS PTY LTD	43511	R7 935,00	R7 935,00	R0,00
SA Forklift	SHOPRITE CHECKERS PTY LTD	43506	R7 935,01	R7 935,00	R0,00
SA Forklift	SHOPRITE CHECKERS PTY LTD	43508	R12 822,50	R12 822,50	R0,00
SA Forklift	SHOPRITE CHECKERS PTY LTD	43508	R12 822,51	R12 822,50	R0,00

where have I messed up?

[CJ_London]

As you can see the grouping is on invoice date and payment

according to your query

GROUP BY tblClient.Client_Name, tblCustomer.Customer_Name, tblInvoice.Invoice_Amount, tblInvoice.Discount

you are grouping on name, amount and discount. And you are summing the date

Sum(tblPmts.Pmt_Date) AS SumOfPmt_Date

suggest try this

SELECT tblClient.Client_Name, tblCustomer.Customer_Name, tblPmts.Pmt_Date, tblInvoice.Invoice_Amount, Sum(tblPmts.Pmt_Amount) AS SumOfPmt_Amount, tblInvoice.Discount
 FROM (tblClient INNER JOIN (tblCustomer INNER JOIN tblInvoice ON tblCustomer.CustomerID_Pk = tblInvoice.CustomerID_Fk) ON tblClient.ClientID_Pk = tblInvoice.ClientID_Fk) LEFT JOIN tblPmts ON tblInvoice.InvoiceID_Pk = tblPmts.InvoiceID_Fk
 GROUP BY tblClient.Client_Name, tblCustomer.Customer_Name, tblPmts.Pmt_Date, tblInvoice.Invoice_Amount, tblInvoice.Discount
 HAVING (((tblClient.Client_Name)=[Forms]![frmBalance].[cboClientName]));

[PeterS]

Thanks Ajax it works well but as soon as I have two payments for one invoice then the whole thig goes out of wack again .....like this

Client_Name	Customer_Name	Pmt_Date	Invoice_Amount	SumOfPmt_Amount	Discount
SA Forklift	VDNL LINEHAUL AND LOGISTICS PTY LTD	2019/01/31	R69,70	R69,70	R0,00
SA Forklift	VDNL LINEHAUL AND LOGISTICS PTY LTD	2019/01/31	R98,33	R98,33	R0,00
SA Forklift	VDNL LINEHAUL AND LOGISTICS PTY LTD	2019/01/31	R805,00	R805,00	R0,00
SA Forklift	VDNL LINEHAUL AND LOGISTICS PTY LTD	2019/01/31	R862,50	R862,50	R0,00
SA Forklift	VDNL LINEHAUL AND LOGISTICS PTY LTD	2019/01/31	R7 360,00	R2 164,47	R1 195,53
SA Forklift	VDNL LINEHAUL AND LOGISTICS PTY LTD	2019/02/26	R7 360,00	R4 000,00	R1 195,53

As you can see there were 2 paments (R2164.47 and R4000.00) which cause the invoice to be listed twice

Any suggestions?

[CJ_London]

in that case remove the payment date from the query - perhaps use invoice date instead if you need a date field

[PeterS]

I dont need the date field so I have removed it, however the query still does not display those two invoices but only the payments

[CJ_London]

so include the invoice number

[PeterS]

Thank you Ajax