Incorrect Totals

[Marianne]

I have a report total that is incorrect when more then one payment is
received in a week. Num 200053 had two payments so the "amt to date" is
totaled twice, as is the "amt expected". The "amt this week" is correct.

the "amt to date" and "amt expected" come from the Num table(one) which has
a one to many relationship with payment table(many)

is there any way to fix this so that the "amt to date" total should be
16,3329.84 and the expected amount should be 48,989.56?

Amt amt amt
Num To date this week expected
200012 7,996.52 1,999.13 $23,989.56
200053 8,333.32 1.00 $25,000.00
200053 8,333.32 8,333.32 $25,000.00
24,663.16 10,333.45 73,989.56

 

[Ken Snell \(MVP\)]

Post the SQL statement of the report's RecordSource query. Let's see what
the data are that you're using.

 

[Marianne]

Here it is. Thanks for your help!

SELECT tbl_CS.Entity, tbl_CS.ME_Dept, tbl_CS.ME_Div, tbl_CS.Ref,
tbl_CS.Person_Dept, tbl_CS.Position, tbl_CS.Description, tbl_CS.YTD_Received,
tbl_CS.YTD_Invoiced, tbl_CS.Est_CS_Value, tbl_Invoice.Current_Month_Payment,
tbl_Invoice.Pymt_Date, tbl_Invoice.Pymt_Amt, tbl_Invoice.Ref_Num
FROM tbl_CS INNER JOIN tbl_Invoice ON tbl_CS.Ref = tbl_Invoice.Ref_Num
WHERE (((tbl_CS.ME_Dept) Like [forms]![frm_Admins_Collected_rpt]![lbx_Dept]
& "*") AND ((tbl_Invoice.Pymt_Date) Between
[forms]![frm_Admins_Collected_rpt]![txt_BegDate] And
[forms]![frm_Admins_Collected_rpt]![txt_EndDate]));

 

[Ken Snell \(MVP\)]

Your query is providing you with the proper data, so the issue likely is the
structure of your report.

What you need in your report is a Group for the "Num" value (set this up in
Sorting & Grouping menu through the View item on the menu bar). Show the
"Num" value and the "Amt To Date" values in the GroupHeader for the "Num"
value; show the individual invoice details in the Detail section within that
Group; then show the sum of the invoice details' amounts (and calculate the
remaining balance) in the GroupFooter for the "Num" value.

--

Ken Snell
<MS ACCESS MVP>

Here it is. Thanks for your help!

SELECT tbl_CS.Entity, tbl_CS.ME_Dept, tbl_CS.ME_Div, tbl_CS.Ref,
tbl_CS.Person_Dept, tbl_CS.Position, tbl_CS.Description,
tbl_CS.YTD_Received,
tbl_CS.YTD_Invoiced, tbl_CS.Est_CS_Value,
tbl_Invoice.Current_Month_Payment,
tbl_Invoice.Pymt_Date, tbl_Invoice.Pymt_Amt, tbl_Invoice.Ref_Num
FROM tbl_CS INNER JOIN tbl_Invoice ON tbl_CS.Ref = tbl_Invoice.Ref_Num
WHERE (((tbl_CS.ME_Dept) Like
[forms]![frm_Admins_Collected_rpt]![lbx_Dept]
& "*") AND ((tbl_Invoice.Pymt_Date) Between
[forms]![frm_Admins_Collected_rpt]![txt_BegDate] And
[forms]![frm_Admins_Collected_rpt]![txt_EndDate]));

Post the SQL statement of the report's RecordSource query. Let's see what
the data are that you're using.