SNAPSHOT OF RECORDS

[Fluffy]

I have a small database (less than 200 clients) which includes their some
basic info such as their start date & ending date. I have created reports
for demographics (race, sex, etc.). When I run these reports it shows me the
info for the current date, such as 45 females & 75 males. Throughout the
year, we get new clients & lose some. How can I get it to run these reports
for a particular date such as July 4th?

Using Win XP & Access 2003

 

[Douglas J. Steele]

SELECT Gender, Count(*)
FROM MyTable
WHERE StartDate <= #07/04/2007#
AND EndDate >= #07/04/2007#
GROUP BY Gender

 

[Fluffy]

Thanks Doug. Now what do I do with what you typed? Create a new rpt or
revise one? I'm still learning Access.

 

[Douglas J. Steele]

That's the SQL of a query. When you build queries through the graphical
query builder, Access is actually generating SQL for you. (You can see the
SQL associated with any query by opening the query in Design view, then
switching to the SQL view on the View menu)

To generate that SQL, create a new query and select your table. Drag in the
grid the Gender field, the Start and End date fields, and one other field
that you know is guaranteed not to contain Null values (the primary key is a
good choice). Change the query into a Totals query (there's a button with a
Sigma on it on the button bar, or check on the View menu). That'll add a new
row to the grid, entitled Total:, with a default value of Group By under
every field in the grid. Change that Group By to Count under the "other"
field, and to Where under the two date fields (You should also uncheck the
two date fields). Put <= #07/04/2007# as the criteria under the StartDate
field, and >= #07/04/2007# under the EndDate field. Run the query.

You can then use that query as the basis of a report if you want.

To have it prompt you for a date, put something like [Enter The Date]
instead of #07/04/2007# under the two fields. Note that dates must be input
in mm/dd/yyyy format.