Querying a specific text in a sentence

[scorpion99]

Hi all,

I have the following excel file (see attached). In the Main sheet , I have three categories that I would like to count each time they exists as value (See Summary sheet).


I imported the Main sheet as table in my access, now I would like to do a query that allows me to get the results immediately.Can someone help?

Mysheet.zip

[June7]

This is not a normalized data structure. What you want won't be simple and could involve 5 queries to achieve the final result. But I will show them as one nested SQL:

SELECT Data, Count(*) AS CountCat FROM (SELECT Main.ID, Main.Date, "Cars" AS Data FROM Main WHERE [Category] Like "*" & "Cars" & "*"
UNION SELECT Main.ID, Main.Date, "Food" AS Data FROM Main WHERE [Category] Like "*" & "Food" & "*"
UNION SELECT Main.ID, Main.Date, "Equipments" AS Data FROM Main WHERE [Category] Like "*" & "Equipments" & "*") GROUP BY Data;

Each date/category should be separate records.

Why were some rows hidden?