DCount 1

[samotek]

DCount in a query
How can i sum up all the fields in the query? My query consists of the table products.The first field is Productid, the second is ProductName. The next fields are the quantities available in the different cities.So the are : reg0.reg1,reg2,reg3,reg4, etc.The last field is reg10.How can i sum up all the quntities? So the filed reg which is blank, must consist reg0+reg2+reg3 etc.I think i must use the Dcount function but how to combine it with the fields?

 

[PHV]

filed reg: Nz(reg0,0)+Nz(reg1,0)+...+Nz(reg9,0)+Nz(reg10,0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

samotek,
Have you considered normalizing your table structure so that summing the quantities would be:
SELECT ProductID, Sum(Quantity) as TotalQty
FROM tblProdCity
GROUP BY ProductID;

Generally, summing fields rather than summing records is a sign of a poorly designed table structure.

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]