Query to count

[ukmdg]

I have a table with records with one field containing a comma separated string.

for example within each field this may have something like

Orange,Apple,Pear,Clementine,Melon

I need a query to count the total orange or apple etc within the table.

Is there an easy way of doing this?

Thanks in advance

UKMDG

 

[Jon K]

Since the table is not normalized, you can use -Sum() on an InStr() expression for each of the orange, apple etc in a query like the following. And as a table has a maximum of 255 fields, you can type no more than 255 such expressions in the query.

SELECT -Sum(InStr([FieldName],"Orange")>0) AS Orange,
-Sum(InStr([FieldName],"Apple")>0) AS Apple,
-Sum(InStr([FieldName],"Pear")>0) AS Pear,
..............................
FROM [TableName];
.