count records with the same value in column b

[steallan]

Yes i'm having trouble with a Count problem, don't laugh at me.

I've got a query with 200000 plus records. Each record has an equipment ID number (its not the primary key) in column B. I just want to count up the number of records that have each equipment ID code, but im not sure how to do it.

Please help

 

[The_Doc_Man]

Write a summation query of the original query and select your "column B" as the field, then select COUNT in the appropriate row. You are allowed to write queries based on queries. Now, to make it "usable", select "column B" TWICE. One is "Group By" the other is "Count" and you can then get counts for each "column B" value and see what that value is.

Just remember, if you have a query, you can almost always use it like a table except when you want to update something that happens to be calculated. If all you want to do is drive a report with it or get counts, a query of a query is as good as just about any other method.

 

[steallan]

Thanks a lot i've done that and it worked

 

[TobyMace]

Nothing like reviving a 13 year old post!

I want to do something similar to above but count the number of time the value in column B appears next to/with the value in column A.
For example count the number of times "Apple" appears in column B every time "Granny Smith" is in column A and count the number of times "Apple" appears in column B when "Jazz" is in column A.

However I don't want to define what will be in column A each time. I want the value in column A to be read, read the values in column B alongside it in that particular record, then count each entry of column B.

(For extra brownie points, some entries in column be may look like "C123, C456" and some may look like "C123" or "C456" alone. Is there anyway to count the values that may have "C123" as entire field, or part of field? I realise allowing the cells to be formatted in this way to begin with wasn't a good idea but that was before my time and i'm not going and changing over 20000 records and creating new ones.)

Thanks in advance.

 

[Minty]

Ouch. Do you have any entries with more than one value e.g. C123, C234, C345, C456 ?

 

[TobyMace]

Do you mean in column B? Then yes.
If the second bit is not achievable then no worries. I may "archive" our current data, and start from fresh with only allowing the format of C???.

 

[Minty]

I definite think you should normalise your data, although not impossible working with a variable multivalued field like that is troublesome.

 

[Pat Hartman]

I'm assuming (and I've been wrong before) that the explanation is more confusing than it needs to be. See if this is what you want.

Select fieldA, fieldB, Count(*) as CountOfAB
From YourTable
Group By fieldA, fieldB;