Counting Offices

  • Thread starter Thread starter Jim Johnson
  • Start date Start date
J

Jim Johnson

Hi,

I am trying to have my query count the number of times an
office code is inputted. The column labe is Dist ID. I am
trying to get the output to give a list of the office
code and then a cout as to how many times that office
code has been recorded. I would like it to list al the
office codes, even if the count is 0. ie

AB 5
AT 3
AZ 2
B 6

I have read other postings for similar problems but am
not sure of some of the info given. Not sure if it needs
to be dumbed down for me.

Thanks
 
Jim said:
Hi,

I am trying to have my query count the number of times an
office code is inputted. The column labe is Dist ID. I am
trying to get the output to give a list of the office
code and then a cout as to how many times that office
code has been recorded. I would like it to list al the
office codes, even if the count is 0. ie
Click to expand...

That's a nice knot in the tail, showing 0. The count is easy:

SELECT distID, count(*) AS counted from yourtable group by distID

but to show the 0s as well, you need to have a source with all distIDs
(obviously not the same table!) and left join that with this query.

Dumb down? You create a new query, view its SQL and paste the SELECT
from above (with the correct names). Save it as, say, qCountOffice.

Create another query; draw the table with all offices from the Tables
tab, and our query qCountOffice from the Queries tab.

In the upper panel of the query, drag the distID field from the table
rectangle to the query rectangle. Double-click the resulting line (you
have joined the two) and select either 2 or 3, the one that shows all
from the table.

Now, in the lower half, display the distID from the table as the first
column, and this expression: "nz(qcountoffice.counted)" as the second column
 
Back
Top