How do I count a specific word in Access

S

Scott927

I made a table with a drop down window that has 3 conditions (Not Occupied,
Active, Grow.) This information is used to tell me the status of radios in a
system. The system has several sites and I would like to know how to count
the number of active radios as a site. I.E. RA1 Active, RA2 Active, RA3
Grow.
 
D

Dennis

Create a query based on your table with 3 columns. Make the first column the
site field, the 2nd column the status field and the 3rd column the status
field as well.
Click on the totals button on the toolbar so that 'group by' appears on all
three columns. Change the third columns 'group by' to 'count' and then run
the query.
I think this will give you what you are looking for.
 
S

Scott927

Dennis

When I created the query and 3 fields the third field always came back
with a 1,, if the sencond field was populated. Is there a way to only count
the "Active" radios?
Thanks
Scott
 
J

John Vinson

I made a table with a drop down window that has 3 conditions (Not Occupied,
Active, Grow.) This information is used to tell me the status of radios in a
system. The system has several sites and I would like to know how to count
the number of active radios as a site. I.E. RA1 Active, RA2 Active, RA3
Grow.
Click to expand...

First, GET RID OF YOUR LOOKUP FIELD. Combo boxes in tables are
slightly helpful in some regards, but - as in this case - a real pain
otherwise. Your table does not CONTAIN "Active" - it contains a
concealed numeric ID from the Lookup table (which itself contains
"Active").

So change the field properties on the "active" field to Textbox so you
can determine whether Active is actually stored as 1, or 3, or
-3312458 or some other number.

You can create a Query joining your table to the lookup table; change
it to a Totals query by clicking the Greek Sigma icon - looks like a
sideways M (or W, depending on which way you tilt). Select any field
(the primary key is a good choice), the Site field, and this activity
field. Change the Totals row operator on the primary key to Count, and
leave the default Group By on the Site and activity fields.

This will now give you a count of records in each category on each
site.

John W. Vinson[MVP]
 
Top