Age Groups

[SeanB]

Hi. I'm new to access but almost have my Database doing what I need apart from one thing.
I cannot set up so it shows Age groups as in how many in the db are aged
16-18
19-30
31-40

I have a query so it works out the age from the Date of Birth.
I have read every thread I can find, watched every video on youtube tried most of what people have suggested but no luck.
I'm hoping someone can go through this with me.
Any help would be very appreciated.
The names of the fields are "Date of Birth" and "Age" for any code people might suggest.

Please ask me for any information. Thank You.

 

[John Big Booty]

Have a look at Query1 in the attached DB.

 

[SeanB]

Thanks for your reply. I think I'm almost there.
Managed to successfully replicate your query but i have a few questions though.

If the Dob column is in the same query clientid it list the count as 1 for each date of birth do you know why that is?
I've attached pics to better illustrate what I'm saying. It's not an issue just curious.

Also what I need now is a column that will take to the answers I now have and will list them as 16-30, 31-40 etc for a quick search or in a new field. How would I do that?

thank you for your response. It has been extremely helpfull so far.

 

[John Big Booty]

If the Dob column is in the same query clientid it list the count as 1 for each date of birth do you know why that is?
I've attached pics to better illustrate what I'm saying. It's not an issue just curious.

This is because of the nature of the query, it is a Totals or Group By query, and it can only group data that is identical, as the DOB are mostly all different they can't be grouped, you would need to apply one of the other options like Count.

Also what I need now is a column that will take to the answers I now have and will list them as 16-30, 31-40 etc for a quick search or in a new field. How would I do that?

Check Query 2 in the amended version of the attached DB.

 

[SeanB]

Thanks for your reply.
the age range you provided was exactly what I was after so thank you.
I'm trying to implement it into my db and have it working but i am having problems having it work out the total for each age range.
On yours you have it linked to the client ID on Table1. i am having problems linking mine to my client id so instead of telling me the total for each range it places the age range next to each group. Please see the attachment.
How do I tell it to count the client ID. I have a feeling it's very simple but I am a noob at this.

 

[SeanB]

OK. I figured out how to get it to count the ID.
I'm showing the first draft of this db tomorrow so I'll ask them how they want this data integrated.

I want to thank you for sorting this out for me. There's not much more else I can do now until they tell me what changes they want. Hopefully it will be mostly cosmetic and tidying it up from now on.

 

[SeanB]

Ok one more problem.
I've created a combo box that displays how many in the age range so how do I get it to list the client records?
For instance there are 2 over 40's who are jan and john. When I click on the over 40's box it then list the files for both jan and John?

 

[John Big Booty]

Have a look at the attached DB.

 

[SeanB]

Hi.
Could you explain how you to make it show the related records in that last form please. I've tried everything I know, which isn't much but I'm learning, but no luck.
I'll keep plugging away at it in the meantime.

I've managed to recreate the Age groups and number in groups section although on the drop down for age groups it displays the age range and number of people in that range. Not a problem just a bit untidy.

 

[John Big Booty]

Have a look at it's On Load event, you will also need to examine the On Click event of the button on the calling form.

Briefly, I am using the OpenArgs portion of the DoCmd.OPenForm to pass the value held in the combo, I am then using that information to filter the data in the second form.

 

[SeanB]

Thanks.
I'll try that tomorrow.

 

[SeanB]

I'm just not getting it.
I'm having real problems now.
i decided to start again with this to keep everything tidy and no I can't even get it to count the ages. What I get is in the enclosed picture.

I have replicated the first db.mdb you gave me apart from the fact the values aren't from a table but from a query. I don't know what I am doing wrong.
Can you help please?

 

[gemma-the-husky]

out of interest, if you were happy to have EQUAL sized groups, you can just use the intrinsic PARTITION function.

 

[John Big Booty]

Can you post a copy of your DB?

 

[SeanB]

Here it is.
There is no personal data on there. It's too big for the forums upload system.
http://www.2shared.com/document/bIy_IPIy/Socialaccdb.html

 

[John Big Booty]

Sorry after several attempts I have been unable to download anything but stuff I don't want or need. If you want me to look at your DB, do a compact and repair on it and put it in a zip file then upload it. I will make no further attempts to download anything stored off site.

And please save it as a pre '07 version of Access.

 

[SeanB]

OK. I have done it's saved as 2003.

 

[John Big Booty]

For some reason I am unable to fathom the DB is not recognising the Date() function

 

[SeanB]

I feel better now knowing someone who actually knows what he's doing is also stumped
It's probably something I've done.

 

[John Big Booty]

Does the Date() function not work in other DB's that you have written? Or is this limited to this particular DB?