Count records Contian specific string values

[Pete]

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.

[Allen Browne]

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********@com cast.netwrote in message
news:11******** *************@e 65g2000hsc.goog legroups.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.

[Pete]

On Apr 11, 9:26 pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

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" <petebea...@com cast.netwrote in message

news:11******** *************@e 65g2000hsc.goog legroups.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.- Hide quoted text -

- Show quoted text -

Thanks,
I think I will create a report and use the dcount function. The data
is used very infrequently and comes from a survey. It looks like the
dcount is probably a better way to go.