If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting multiple field values in a single query/report
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?? |
#2
|
|||
|
|||
Counting multiple field values in a single query/report
use an unbound form. Create two text fields to hold the start and end dates,
and then pass them in the code that opens the report... strDateFilter = "[DateField] BETWEEN #" & Me.txtStartDate & " # AND #" & Me. txtEndDate & "#" DoCmd.OpenReport "MyReport",,,strDateFilter (Can't remember which argument is the filter, so check it in VBA... -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#3
|
|||
|
|||
Counting multiple field values in a single query/report
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. -- Build a little, test a little. "KatK" wrote: 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?? |
Thread Tools | |
Display Modes | |
|
|