count number

[Guest]

i have a table, each record lists a different car (so far i have listed 24
different cars) i have a field named quantity where i could have different
numbers of different cars if that makes sense.

e.g. 5 of a certain audi car, 2 or a certain bmw so on.

i want to make a query that will count all the cars in the table. so for the
example i made above, though there are 2 different cars, but the answer i
wanted is a total of 7.

Could any1 provide a step by step guide on how to achieve this in access 07
preferrably in simple terms.

Though i am using the 2007 version of access it is saved in a 2003 access
format, if that matters
Cheers.

 

[pietlinden]

i have a table, each record lists a different car (so far i have listed 24
different cars) i have a field named quantity where i could have different
numbers of different cars if that makes sense.

e.g. 5 of a certain audi car, 2 or a certain bmw so on.

i want to make a query that will count all the cars in the table. so for the
example i made above, though there are 2 different cars, but the answer i
wanted is a total of 7.

Could any1 provide a step by step guide on how to achieve this in access 07
preferrably in simple terms.

Though i am using the 2007 version of access it is saved in a 2003 access
format, if that matters
Cheers.

You mean a regular totals query where you group by model doesn't work?

 

[Michel Walsh]

If your data is 'vertical', like:

CarModel Quantity ' fields name
audi 5
bmw 2



Then, in a new query, bring the table. Next, click on the Summation button
on the toolbar, the one with a capital sigma, a M rotated by 90 degree, and
a new line, Total, appears in the grid. Drag the field quantity in the grid,
change the GROUP BY to SUM. That's all.




If your data is 'horizontal', like:


carModel1, qty1, carModel2, qty2, ... 'fields
audi 5 bmw 2

you are not using the database as it is assumed, by the developers. It is
said that your data is not 'normalized'. Try to get your data
'vertically', as in the first example.



Hoping it may help
Vanderghast, Access MVP