counting query

  • Thread starter Thread starter hotrod1952 via AccessMonster.com
  • Start date Start date
H

hotrod1952 via AccessMonster.com

newbie! I have an existing database(access97). The database has the following
:
Table WO , Field WOID, I want to query all records beginning in PM and count
them. I can seem to filter in the criteria row using like"PM*". Which returns
all records with PM as the first two letters. How can I count them? Sample
data from the field (formatted as text) are: PM00345,PM00972,34569,23142.
 
in query design view, on the menu bar click View | Totals. in the query
grid, change the default "Group By" to "Count".

hth
 
please post your SQL statement. to do that, click View | SQL from the menu
bar, highlight *all* the text in the SQL pane, and paste it into a message.

hth
 
Using count in the totals field and like"pm*' in the criteria. I also checked
the table and verified that WOID is the correct field and is formatted as
text.

SELECT Count(WO.WOID) AS CountOfWOID
FROM WO
HAVING (((Count(WO.WOID)) Like ("pm*")));

please post your SQL statement. to do that, click View | SQL from the menu
bar, highlight *all* the text in the SQL pane, and paste it into a message.

hth
I tried that also. When I do that I return no records.
Click to expand...
[quoted text clipped - 9 lines]
Click to expand...
 
I think I have it now!
I modified sql code to this:
SELECT Count(WO.WOID) AS CountOfWOID
FROM WO
HAVING (((WO.WOID)) Like ("pm*"));
Using count in the totals field and like"pm*' in the criteria. I also checked
the table and verified that WOID is the correct field and is formatted as
text.

SELECT Count(WO.WOID) AS CountOfWOID
FROM WO
HAVING (((Count(WO.WOID)) Like ("pm*")));
please post your SQL statement. to do that, click View | SQL from the menu
bar, highlight *all* the text in the SQL pane, and paste it into a message.
Click to expand...
[quoted text clipped - 6 lines]
Click to expand...
 
Yes, sometimes it is much easier to see what is really happening when you
look at the SQL. Good catch.
 
newbie! I have an existing database(access97). The database has the following
:
Table WO , Field WOID, I want to query all records beginning in PM and count
them. I can seem to filter in the criteria row using like"PM*". Which returns
all records with PM as the first two letters. How can I count them? Sample
data from the field (formatted as text) are: PM00345,PM00972,34569,23142.
Click to expand...

Change your Query into a Totals query by clicking the Greek Sigma icon
on the toolbar (looks like a sideways M). You'll get a new row labeled
Totals on the query grid. Select the Primary Key of your table (if it
has one - if it doesn't, it should, but pick some field that is never
NULL) and change the default "Group By" to "Count". Under the field
containing PM, change the "Group By" to "Where".

Open the query; you'll get one row with one field, the count of
PM<anything> records.

For flexibility, you can use a criterion of

LIKE [Enter prefix:] & "*"

Entering PM will find all the PM records, entering AM will find all
the AM records, entering BM will... well, let's not go THERE!

John W. Vinson[MVP]
 
I think I have it now!
I modified sql code to this:
SELECT Count(WO.WOID) AS CountOfWOID
FROM WO
HAVING (((WO.WOID)) Like ("pm*"));
Click to expand...

Actually it's better if you change the HAVING to WHERE. WHERE is
applied before you do the counting; HAVING does the count first and
then discards the mismatches.

John W. Vinson[MVP]
 
Thanks for the help!
Hotrod1952

John said:
newbie! I have an existing database(access97). The database has the following
:
Table WO , Field WOID, I want to query all records beginning in PM and count
them. I can seem to filter in the criteria row using like"PM*". Which returns
all records with PM as the first two letters. How can I count them? Sample
data from the field (formatted as text) are: PM00345,PM00972,34569,23142.
Click to expand...

Change your Query into a Totals query by clicking the Greek Sigma icon
on the toolbar (looks like a sideways M). You'll get a new row labeled
Totals on the query grid. Select the Primary Key of your table (if it
has one - if it doesn't, it should, but pick some field that is never
NULL) and change the default "Group By" to "Count". Under the field
containing PM, change the "Group By" to "Where".

Open the query; you'll get one row with one field, the count of
PM<anything> records.

For flexibility, you can use a criterion of

LIKE [Enter prefix:] & "*"

Entering PM will find all the PM records, entering AM will find all
the AM records, entering BM will... well, let's not go THERE!

John W. Vinson[MVP]
Click to expand...
 
Back
Top