How to query data by month

[ggoldberg]

I have a table that contains:
Client ID
Gender
Race
Zip code
Birth date
Visit date
I would like to produce a table that contains one record for each Client in each Zip code for each month that a visit occurs. How can I structure a query to do this? I have the same requirement for reports by Race, Gender and Age

Thanks,

Gerry Goldberg

 

[lespaul]

SELECT DISTINCT CLIENTID, 'ZIPCODE' AS TYPE, ZIPCODE AS DATA, MONTH([VISIT DATE])
FROM TABLENAME
UNION
SELECT DISTINCT CLIENTID, 'RACE', RACE, MONTH([VISIT DATE])
FROM TABLENAME
UNION
SELECT DISTINCT CLIENTID, 'GENDER', GENDER, MONTH([VISIT DATE])
FROM TABLENAME
UNION
SELECT DISTINCT CLIENTID, 'AGE', DATEDIFF(TODAY, [BIRTH DATE]), MONTH([VISIT DATE])
FROM TABLENAME

This single query should return all the information you need for the report. You will be able to break the report up into sections with this structure. If you want separate reports just remove the unions and each select statement will work independantly.

leslie

 

[dhookom]

I would think the value of this query/report would be in aggregating the records into groups (Group By) rather than return the details.
Leslie,
The UNION query approach is nice. I would add an " As VisitMth" after the first Month([Visit Date]).

Also, your age calculation won't work correctly. Consider using the expression or function found at

http://www.mvps.org/access/datetime/date0001.htm.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

Yeah, well I pulled the AGE field calculation from ... you know.... I figured it got the basic idea across! Thanks for the link!

les

 

[wvmbark]

Here's what I do and it works great...

In a new field add MonthDate: =DateSerial(Year([Visit Date]), Month([Visit Date]), 1) and then group by that date.

This takes every Visit Date and converts it to the first day of it respective month. You can then group by that date. Easy as pie!

Mike