MS Access Help!

[game_over]

I'm wondering if any MS Access experts can help me with this problem. I haven't used Access since my college days and completely forgot how to do certain things.

 

My database is used for logging children that attend certain classes, it records their details: date of birth, name, medical info etc..

 

I need to be able to generate a report based on their age, so that all children running the report will generate a list of all children under the age of 8 and another report that generates a list of children over the age of 8. How complicated is it to do this based on their DOB? I guess I will need to work out their age from they DOB and then filter from there?

 

I've tried generating a report but can't seem to get desired results, would i work from a query first? how would the criteria look?

It would be even better if i could have over and under 8s on the same report, but just have them separated so they are easily distinguished.

 

Is this possible? thanks

[+InsaneNutter MVC]

I'm no Access expert sadly, however i've been using it a bit recently so can half help you with this. I can workout the age each person will be this year, however i'm unsure how to take in to account if the person has had their birthday or not.

 

Example Data:
 


Query:
 
Create a query and add the Child's Name, Dob and any other data you want in the result.

Then in an empty filed enter the expression Age: Year(Now( ))-Year([DoB])
 
This subtracts the person's birth year from the current year to give the persons age, however as I realised when doing this it doesn't take in to account if the person has had their birthday or not yet.
 

 

For "Criteria" you can simply filter the results, so <8 will return any person who is younger than 8

=8 would be anyone who is 8

Then >8 older than 8

You can then create reports based off the results of the queries:

 

 

I hope that gets you started in the right direction anyway. Ill try have a look tomorrow and see if I can work out the persons exact age.

[lj300]

Use the DateAdd Function. It will be more precise.

 

WHERE DateAdd ("yyyy", 8, DOB) <= NOW

WHERE DateAdd ("yyyy", 8, DOB) > NOW

 

 

You'll get the date the child turns 8

If it is less than or equal to NOW() it means he/she has already turned 8

If it is greater that NOW() it means he/she hasn't turned 8

 

 

[game_over]

Works perfect, thanks guys

[game_over]

I have another question:

 

Is it possible to set some criteria when a report is run? As above i need under 8s and over 8s, but these are also split into 2 separate groups .. so in total that is 4 queries that can be run

 

morning: over 8s and under 8s

afternoon: over 8s and under 8s

 

Id like it so that when a report is run it asks if them if they want the morning and afternoon group and then asks them if they want over8s or under 8s and then shows results accordingly?