Summing in a query

[DrillMonkey]

Hello,

I need to sum 20 fields on a data form. The fields names are "Parcels1 through Parcels20. I'd like for it to show the caculations in the "TotalParcels" control on the form.

SELECT tblWarehouse.RecordID, Sum(tblWarehouse.Parcels1) AS SumOfParcels1, Sum(tblWarehouse.Parcels2) AS SumOfParcels2, Sum(tblWarehouse.Parcels3) AS SumOfParcels3, Sum(tblWarehouse.Parcels4) AS SumOfParcels4, Sum(tblWarehouse.Parcels5) AS SumOfParcels5, Sum(tblWarehouse.Parcels6) AS SumOfParcels6, Sum(tblWarehouse.Parcels7) AS SumOfParcels7, Sum(tblWarehouse.Parcels8) AS SumOfParcels8, Sum(tblWarehouse.Parcels9) AS SumOfParcels9, Sum(tblWarehouse.Parcels10) AS SumOfParcels10, Sum(tblWarehouse.Parcels11) AS SumOfParcels11, Sum(tblWarehouse.Parcels12) AS SumOfParcels12, Sum(tblWarehouse.Parcels13) AS SumOfParcels13, Sum(tblWarehouse.Parcels14) AS SumOfParcels14, Sum(tblWarehouse.Parcels15) AS SumOfParcels15, Sum(tblWarehouse.Parcels16) AS SumOfParcels16, Sum(tblWarehouse.Parcels17) AS SumOfParcels17, Sum(tblWarehouse.Parcels18) AS SumOfParcels18, Sum(tblWarehouse.Parcels19) AS SumOfParcels19, Sum(tblWarehouse.Parcels20) AS SumOfParcels20
FROM tblWarehouse
GROUP BY tblWarehouse.RecordID;

I'm trying to sum only the current record thanks.

 

[DrillMonkey]

I was trying this in the "TotalParcels" control source
with limited success. I do get some caculations but none correct so far..mostly errors

=DSum("parcels1","parcels2","qryWarehouse")

 

[dhookom]

Summing would be much easier if your table was normalized. I assume you want to add the values across the fields in a single record rather than summing across records.

Try:
=SumOfParcels1+SumOfParcels2+SumOfParcels3+SumOfParcels4...

If any of the values might be null, you will need to use individual expressions like:
=Nz(SumOfParcels1,0)+Nz(SumOfParcels2,0)+...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DrillMonkey]

Thanks Duane