Question Counting values in separate fields (1 Viewer)

[gfsm703]

Hi All,

Self Taught newbie - ish.

I have several fields headed Cat 1 Cat 2 Cat 3 etc in each I may have
values like:

Cat 1 Cat 2 Cat 3
MOE FAS TRG
FAS EEL FFE
MNT

I want to be able to count each value and report the count by value i.e. no particular order actually just the value totals counted from the different fields.

MOE 1 FAS 2 TRG 1 EEL 1 FFE 1 MNT 1 and have these displayed in a simple text report and then a graph if possible

Anyone please help, as I am stuck with this one.

 

[MarkK]

Your table should never have three cats in it. You are now stuck having to check each of the cats, which is a major headache.
Rather, consider a structure like this ...

[B]tCat[/B]
CatID (Unique Primary Key)
CatNumber (1, 2 or 3)
Cat

Now your data should look like

ID  Num  Cat
1   1    MOE
2   1    FAS
3   1    MNT
4   2    FAS
5   2    EEL
6   3    TRG
7   3    FFG

See? Just one pile for all the things that are the same, then to count them is easy because they're all in one pile. Use DCount() or ...

SELECT Cat, Count(Cat) AS CatCount
FROM tCat
GROUP BY Cat;

HTH

 

[gfsm703]

Thank you very much for your reply, the problem I am having is better explained on the 2 attachment jpgs. If you could help further..it would be appreciated.

 

[MarkK]

Fields in a row of a table should represent the different dimensions of a single discrete object.
The error you have made is this...
Fields in a row of your table represent the same dimension of mutiple discrete objects.
And probably you made this error because you allowed the design of your user interface to dictate the structure of your data, and that is a common hazard and one to be avoided.