Counting query help

  • Thread starter Thread starter Peter Aitken
  • Start date Start date
P

Peter Aitken

I have a multiuser Access app that keeps track of student dormitory
assignments. One screen displays the number of students currently assigned
to each floor of each dorm - about 60 floor/dowm combinations in all. Right
now I have a separate query for each one using the Count(*) method; code in
the form accesses all these queries to populate the form with data. However
it is quite slow and I think it is because there are so many queries being
run. Is there some way to run a single query that will give me counts for
each floor/dorm combination in the table?

THanks,
 
Hi,


A crosstab query, maybe?


TRANSFORM SUM(howMany) As v
SELECT dorm, SUM(v) As TotalByDorm
FROM myTable
GROUP BY dorm
PIVOT floor



I assume you have a table with fields:

dorm floor howMany 'fields
"A" 1 12
"A" 2 9 'data sample


If, instead, you have


StudentID, Dorm, Floor as fields, use

TRANSFORM COUNT(*) As v
SELECT ...




You can use Nz(SUM(howMany), 0) or Nz(COUNT(*), 0) to replace the NULL
with zero, if you find zero more convenient than Null, in the presentation
(even if NULL probably stands better if there is no such thing such as a
floor 5 in a given dormitory)




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top