It's been a while

[PaulTEG]

I need to generate a summary report in access based on a start_date, DOB, and a class to get the age distribution

What I've got so far is

Select participants.class, participants.start_date-participants.dob As Age from participants;

The problem is it gives back I think the number of days, but I'd like this to display the number of years

Thanx in Anticipation
Paul

It's important in life to always strike a happy medium, so if you see someone with a crystal ball, and a smile on their face ...

 

[TonyJollans]

Hi Paul,

There is a limitation with this kind of manipulation in that Access has no real concept of a period as opposed to a date. The numerical values it holds (or calculates) are formatted as dates based on some time long ago and so 5 years and 1 day, say, is returned apparently as 5 years, 1 month and 2 days (the 2nd of January year 5) - this may not be the actual result but I hope it illustrates.

That said, to get the date difference in years you can use:

SELECT participants.class, DateDiff("yyyy",[participants].[dob],[participants].[start_date]) AS Age
FROM participants;

But this doesn't give the age if startdate is earlier in the year than dob. It can be improved upon by doing some calculation with months instead of years ..

SELECT participants.class, Int(DateDiff("m",[participants].[dob],[participants].[start_date])/12) AS Age
FROM participants;

.. but this is still wrong if startdate falls earlier in the dob month. To calculate more accurately requires looking specifically at the day, something like this should do it:

SELECT participants.class, Int(DateDiff("m",[participants].[dob],[participants].[start_date])/12)+(Day([participants].[dob])>Day([participants].[start_date])) AS Age
FROM participants;

Enjoy,
Tony

 

[PaulTEG]

Thanks for the reply Tony, I had cobbled a somewhat less elegant solution using format ([participants].[start_date]-[particpants].[dob],"YY&quot.

I generated a number of queries age_u14, age_14-16 ..., and tried to use them in a report, and it doesn't appear to work. The report was querying me for a variable

[tt]
SELECT Count(*) AS AD_22_24
FROM Participants
WHERE (((Format([start_date]-[dob],&quot;yy&quot)>=22 And (Format([start_Date]-[dob],&quot;yy&quot)<24));[/tt]

What I'm trying to do is create a distribution table
under 16 | 3
16-18 | 2
18-24 | 6

and so on, any ideas for a more generic query, or can you point me to a good online resource for report writing?

Cheers
Paul

It's important in life to always strike a happy medium, so if you see someone with a crystal ball, and a smile on their face ...

 

[TonyJollans]

Hi Paul,

I'm not sure what you mean when you say you tried to use a number of queries in your report. And what are you being prompted for? The posted query is sound, but where are you using it?

I'm not sure how best to do your report, but I will have a play and come back. Also not really sure where to point you to for best resources -

http://www.mvps.org

is probably a good starting point, but there are many others around. A search on the forums here might well turn up some.

Enjoy,
Tony

 

[PaulTEG]

Tony,
I'm using a query to get a specific result count(*), and I need to show a number of these results in a summary table, So i'm generating a query for each

Select count(*) where this=that
Select count(*) where this=theother

IYKWIM

It's important in life to always strike a happy medium, so if you see someone with a crystal ball, and a smile on their face ...