Total incorrect

[Mike Hughes]

When this query is run the total amount for the first line should be $60 but it is not showing in the total field. Why?


SELECT
[DISBURSEMENTS 2].DT_BATCH,
[DISBURSEMENTS 2].CD_SOURCE_BATCH,
[DISBURSEMENTS 2].NO_BATCH,
[DISBURSEMENTS 2].SEQ_RECEIPT,
[DISBURSEMENTS 2].DISBURSMENTS,
[DISBURSEMENTS 2].[REC ADVANCE],
SUM ([DISBURSEMENTS 2].DISBURSMENTS+
[DISBURSEMENTS 2].[REC ADVANCE]) AS [TOTAL DISBURSED]INTO [DISBURSEMENTS 3]

FROM [DISBURSEMENTS 2]
GROUP BY
[DISBURSEMENTS 2].DT_BATCH, [DISBURSEMENTS 2].CD_SOURCE_BATCH, [DISBURSEMENTS 2].NO_BATCH, [DISBURSEMENTS 2].SEQ_RECEIPT, [DISBURSEMENTS 2].DISBURSMENTS, [DISBURSEMENTS 2].[REC ADVANCE];

DISBURSEMENTS 3
DT_BATCH CD_SOURCE_BATCH NO_BATCH SEQ_RECEIPT DISBURSMENTS REC ADVANCE TOTAL DISBURSED
4/11/2011 O 5002 1 $60.00
4/11/2011 O 5002 2 $77.35 $204.65 $282.00

 

[jzwp22]

It looks like you are summing fields in a record, summing across records and appending everything. I think you may need to split this up into separate queries

I query to get the sum of fields for each record:


query name: qryDetail
SELECT
[DISBURSEMENTS 2].DT_BATCH,
[DISBURSEMENTS 2].CD_SOURCE_BATCH,
[DISBURSEMENTS 2].NO_BATCH,
[DISBURSEMENTS 2].SEQ_RECEIPT,
[DISBURSEMENTS 2].DISBURSMENTS,
[DISBURSEMENTS 2].[REC ADVANCE],
([DISBURSEMENTS 2].DISBURSMENTS+
[DISBURSEMENTS 2].[REC ADVANCE]) AS [TOTAL DISBURSED]
FROM [DISBURSEMENTS 2]


Now a query to get the sum for groups of similar records:

SELECT qryDetail.DT_BATCH, qryDetail.CD_SOURCE_BATCH, qryDetail.NO_BATCH, qryDetail.SEQ_RECEIPT, SUM(qryDetail.[TOTAL DISBURSED])
FROM qryDetail
GROUP BY qryDetail.DT_BATCH, qryDetail.CD_SOURCE_BATCH, qryDetail.NO_BATCH, qryDetail.SEQ_RECEIPT

I'm not sure why you would want to append the data into another table. Having the duplicate data in another table is not a good database practice.

 

[Mike Hughes]

Thanks for the info. I'll give your idea a try.

 

[boblarson]

Also, just to note - if you do this:

[DISBURSEMENTS 2].DISBURSMENTS + [DISBURSEMENTS 2].[REC ADVANCE]

You may want to wrap it with the NZ function because if either of the fields are null then adding a null to a number will result in a null.

So this would be how to deal with that:

Nz([DISBURSEMENTS 2].DISBURSMENTS,0) + Nz([DISBURSEMENTS 2].[REC ADVANCE], 0)