Need Assistance with Query Display

[cdogstu99]

I have a table which lists data as follows:
Company Name, Car 1, Car 2, Car 3, Car 4, Car 5, Car 6.

For each car field, the value can take either 1, 1A, 2, 3, 3A, 4, 5, or 6. Field 'Car 1' always has a value, but the others do not necessarily take a value.

I need to display a list by company with the total count of the fields in each car.

For example:
"1" "1A" "2" "3" "4" ....etc
Company 1 5 4 3 4 9


I'd also like to display this on a report if possible. Thanks!

 

[PHV]

Do a search on normalization union query and crosstab query.

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

 

[vitaminD12]

It is not quite clear to me what your goal is counting these cars and what other pieces of data you are collecting besides just these car fields but it seams like a design issue. These 6 car fields seam to be too many - try to normalize your data and may be build a couple of new tables.