|
Home > Archive > MS Access database support > February 2006 > DCount
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| 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?
| |
| Rick Brandt 2006-02-18, 11:23 am |
| "solar" <sprimerov@gmail.com> wrote in message
news:1140275623.527217.152530@f14g2000cwb.googlegroups.com...
> 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?
To answer your question, no you would not use DCount(). You would simply enter
an expression in a new query column of...
Total: reg0 + reg1 + reg2 + reg3 + reg4
If any if those might be null then you would need...
Total: Nz(reg0) + Nz(reg1) + Nz(reg2) + Nz(reg3) + Nz(reg4)
However; I should point out that your table structure is incorrect. It's a
spreadsheet construct rather than a database construct. A proper database
design would yield a table like...
Productid ProductName Qty Reg
someID "someName" 1 0
someID "someName" 3 1
someID "someName" 7 2
someID "someName" 2 3
....and then you would be able to sum a *column* which is what aggregate queries
do well. Any time you find yourself needing to aggregate across a *row* then
there is a good chance that you have a design problem.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
| |
| Bob Quintal 2006-02-18, 11:23 am |
| "solar" <sprimerov@gmail.com> wrote in
news:1140275623.527217.152530@f14g2000cwb.googlegroups.com:
> 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?
>
The reason you are having trouble is that the table design is
wrong. You need to create a child table containing the value for
a producID, a CityID and the quantity.
Imagine the rework of your query when you add that 11th city
Your products table needs only to contain ProductID,
ProductName, ProductSupplier, ProductLeadTime... all the things
that are attributes of the product. Your Child table needs to
contain ProductID, CityID and QtyOnHand for that city. You
probably want to make a cities table as well with CityID,
CityName, CityState, such that you save a lot of typing by
picking the city from a combobox.
Then your summing up the quantities becomes a simple, easy to
build and easy to maintain summary query.
--
Bob Quintal
PA is y I've altered my email address.
|
|
|
|
|