Counting multiple field values in a single query/report

  • Thread starter Thread starter KatK
  • Start date Start date
K

KatK

I am creating a database for a community program that includes a number of
fields with drop down choices, such a race, school, abuse history, family
composition. We want to total/count each of these variables for a
particular date range. I can create separate summary queries to get totals
for each, such a total number of african americans, total white, total asian,
total other, but then the date range has to be re-entered over and over.
Have tried creating one master query/report for just the date range, then
pulling in the others as subreports, but it ends up showing each record
individually instead of the summary. I am a bit of a novice with queries
beyond the basic. Is there a way to do one query and/or report with it all??
 
Use an unbound form. Create two text fields to hold the start and end dates,
and then pass them in the criteria like this --
SELECT [School], [Race], Count([Race]) AS CountOfRace
FROM YourTable
WHERE DateField Between CVDate([Forms]![YourFormName]![StartDate]) AND
CVDate([Forms]![YourFormName]![EndDate])
GROUP BY [School], [Race];

Open the form and enter dates in form and leave open when you run the form.
 
Back
Top