Counting Different Values in Multi Select Fields

[ferrisj]

So I have inherited a dbf file with 3 fields.

ID, MS1, MS2

ID is a primary key, MS1 and MS2 are each field with different values in them separated by a comma.

Example

ID MS1 MS2
============ ================ =====================
1 a, b, c x, y, z
2 b, d, f y, z, u

Want I need to figure out is:

How many As exist in the field. How many B's. etc

 

[PHV]

A starting point:
SELECT Count(*) FROM yourTable
WHERE ',' & MS1 & ',' Like '*,A,*'

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

 

[ferrisj]

So there could be 20 different values in ms1

 

[dhookom]

Are you counting from just the MS1 field or could there be A's in either field to be counted?

Can a field contain more than a single 'a' and if so, should this be counted more than once?

Do you have a table that contains a list of the 20 unique search values? If not, create one and make a query something like:

SELECT tblUniqueVals.UniqueVal, Count(YourTable.*) as NumOf
FROM tblUniqueVals, YourTable
WHERE "," & MS1 & "," Like "," & [UniqueVal] & ","
GROUP BY UniqueVal;


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ferrisj]

Yes I am county just from MS1

A field will only can only contain one A per record

Possible
ms1
===
a,b,c
a,c,d

Not Possible
ms1
=====
a,a,b

Yes I have a table that has the 20 different values.

 

[PHV]

Duane, I'm afraid you missed the wildchars ...
SELECT U.UniqueVal, Count(*) as NumOf
FROM tblUniqueVals AS U, yourTable AS T
WHERE ',' & T.MS1 & ',' Like '*,' & U.UniqueVal & ',*'
GROUP BY U.UniqueVal

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

 

[ferrisj]

My query looks like this:

SELECT CommunityLookup.Values, Count(thru0208.*) as NumOf
FROM CommunityLookup, thru0208
WHERE "," & COMMUNITY_ & "," Like "," & [Values] & ","
GROUP BY Values;


I am getting an error:
Syntax error in query expression 'Count(thru0208.*)'

 

[PHV]

SELECT CommunityLookup.Values, Count(*) as NumOf
FROM CommunityLookup, thru0208
WHERE "," & thru0208.COMMUNITY_ & "," Like "*," & CommunityLookup.Values & ",*"
GROUP BY CommunityLookup.Values

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

 

[ferrisj]

with this new sql

SELECT CommunityLookup.Values AS Expr1, Count(*) AS NumOf
FROM CommunityLookup AS U, thru0208 AS T
WHERE (((',' & [T].[COMMUNITY_] & ',') Like '*,' & U.Values & ',*'))
GROUP BY U.Values;


I am getting an Enter Parameter Value for CommunityLookup.Values

 

[PHV]

If you want to play with aliases:
SELECT U.Values, Count(*) AS NumOf
FROM CommunityLookup AS U, thru0208 AS T
WHERE ',' & T.COMMUNITY_ & ',' Like '*,' & U.Values & ',*'
GROUP BY U.Values

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

 

[ferrisj]

This works kinda, here is the issue now.


now it gives me a result of

Group By Count
======== ======
A 9


But I know there is 21

because when i run:
SELECT thru0208.COMMUNITY_
FROM thru0208
WHERE (((thru0208.COMMUNITY_) Like "*A*"));

I get 21


the values

 

[dhookom]

Thanks for fixing my errors. You probably have spaces in the COMMUNITY_ field on either side of your values. You might want to try something like:

SELECT CommunityLookup.Values AS Expr1, Count(*) AS NumOf
FROM CommunityLookup AS U, thru0208 AS T
WHERE (((',' & Replace([T].[COMMUNITY_]," ","") & ',') Like '*,' & U.Values & ',*'))
GROUP BY U.Values;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Duane, sorry, but another error fixing ;-)
SELECT [!]U[/!].Values AS Expr1, Count(*) AS NumOf
...

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

 

[lespaul]

Duane,
Looks like you're having as bad a day as me! PHV has corrected me today too!

Les

 

[dhookom]

It's Friday and I hurriedly copied your SQL ;-)

I just have to keep everyone checking and not make things too easy for OPs (original posters). They really need to be able to trouble-shoot syntax and other errors.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

They really need to be able to trouble-shoot syntax and other errors.
I'm with you Duane.
I hate "cargo cult programers" ;-)