counting entries

[MaleNurse325]

I have a report based on table OWM which has a fields Samp1, Samp2, samp3 and Samp4. these fields are a single letter 'A', 'B','D', 'F' or 'X'

I am trying to build a calculated field to count the number of each specific entry to give me the number of 'A' s for instance. I'm fairly confident it is a Dcount function but can only get it to work on a single entry.

any help would be greatly appreciated.

Cheers
steve:banghead:

 

[Ranman256]

No,not Dcount. Use a query. But since you did not normalize,you will need a few queries.
Q1 to Count col1
Q2 to count col2
Etc.
Then you can sum all the queries in a union query.

 

[plog]

table OWM which has a fields Samp1, Samp2, samp3 and Samp4

Good news, what you want to achieve is simple. Bad news, you haven't set your tables up properly to do so.

When you start numerating field names (Field1, Field2, Field3, etc.), its time for a new table. Data should be added vertically (with more rows) and not horizontally (with more fields). The table to hold the Samp data should look like this:

Samp
samp_ID, autonumber, primary key
ID_OWM, number, links to ID field in OWM
samp_Value, short text, this will hold the A, B, D, F, X values
samp_Num, number, this will hold the number currently in the field name (1, 2, 3, 4, etc.) assuming that the number is important.

So, instead of the 1 record you have now, you would have 4 records in the Samp table. Let's say you have this in OWM:

ID, Samp1, Samp2, Samp3, Samp4
13, A, A, B, X

That data should instead be stored like so in Samp:

samp_ID, ID_OWM, samp_Value, samp_Num
1, 13, A, 1
2, 13, A, 2
3, 13, B, 3
4, 13, X, 4

Then to get your totals, you do a simple aggregate query:

SELECT samp_Value, COUNT(samp_Value) As SampTotal FROM Samp GROUP BY samp_Value;

 

[Uncle Gizmo]

As Plog says - Your Design is wrong. You have a choice, continue with your design in which the set-up and design of the tables is easy, everything looks nice and understandable. However the design of the queries is not easy! Your choice is to continue with a bad design and sort out the problems as they occur, which will escalate in an exponential way, or spend a bit of time learning how to design it properly and discover that it is quite easy to create the queries...

I've started a simple example of what I guess it should look like... See attached:-