The field could contain more than one thing, e.g. it might be:
AAA is CCC, so please Stop.
So, you cannot get this kind of answer from any grouping. You will need to
use a subquery to return the count.
1. Create a query without any table in the upper pane of query design.
2. Type this into the Field row:
CountAAA: (SELECT Count(ID) FROM Table1 WHERE Field1 LIKE "*AAA*")
3. Replace Table1 with your table, name, ID with the name of your primary
key field, and Field1 with the name of the field that might contain the AAA.
4. Test.
5. Once you have it working, type a similar expression into the next column
in the Field row, to match the CCC text.
6. Repeat in the next column for the "Stop" text.
If subqueries are new, see:
http://allenbrowne.com/subquery-01.html
If this is an important query, there may be a better way to design this
table.
The query will be *very* slow to run.
It is error prone (e.g. where someone typed Drop instead of Stop.)
And results are poor (e.g. if you search for Top, you find it in Stop,
Topic, etc.)
So perhaps something like a related table of keywords would give more
accurate results, lightening fast, and make the querying much easier.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Pete" <pe********@comcast.netwrote in message
news:11*********************@e65g2000hsc.googlegro ups.com...
>I need to create a single query (Not a SQL query) against a single
table that counts the number of records in the table, where the single
field "tmp" contains specific string values
If the field contains "AAA" the count is X.
if the field contains "CCC" the count is Y.
if the field contains "Stop" then count is Z.
I have tried several ways and can not seem to get any where. I get
the same count for all string values. Can some one please send me a
sample query that I could look at and study.
