By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 1,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Finding Age Group in Access

P: 7
Hell All!

I have a requirement to find age group of Customers. Here is the scenario. There is only one table - Customer. Here are the columns - CustNo, PrimaryBirthdate. I can pull the age of the customers as of today by using the following SQL query. The requirement is - to pull the same info on age group. For example, how many of these customers are in Age Group <20, Between 20-40, 40 to 50 and above 50 etc.

Query:-

select Datediff('yyyy',[primarybirthdate],now())as Age,CustNo from(select CustNo,primaryBirthdate FROM Customers);

Result:-

Age CustNo

50 12345
54 34234
69 56443
20 44556
16 89777

I have very limited knowledge of Access. Can run some simple sql queries. You must note that I am NOVICE when it comes to Access. Please help me.

Thank you all in anticipation of solution to my requirement.

Omnitha.
Aug 22 '08 #1
Share this Question
Share on Google+
8 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello, Omnitha.

You may find some clues reading Grouping Query Range thread.

Kind regards,
Fish
Aug 22 '08 #2

P: 7
Hello, Omnitha.

You may find some clues reading Grouping Query Range thread.

Kind regards,
Fish
Thanks for the lead, Fishval. Unfortunately, my novice level won't help me much to understand all. So, help me with a straight, simple query here...Appreciate your reply.

Regards,

Omnitha
Aug 22 '08 #3

ADezii
Expert 5K+
P: 8,669
Assuming your Table Name is tblCustomers, and you know how to calculate the Age of a Customer, the following SQL will list the Total Count of all Customers in 10 Year Intervals up to 100 years old:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW Partition([Age],0,150,10) AS [Age Ranges], Count([Age]) AS [Count In Range]
  2. FROM tblCustomers
  3. GROUP BY Partition([Age],0,150,10);
Sample OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Age Ranges    Count In Range
  2.  10: 19              1
  3.  30: 39              9
  4.  50: 59             21
  5.  60: 69             17
Aug 22 '08 #4

FishVal
Expert 2.5K+
P: 2,653
Thanks for the lead, Fishval. Unfortunately, my novice level won't help me much to understand all. So, help me with a straight, simple query here...Appreciate your reply.

Regards,

Omnitha
Ok.

The thread you've read contains two solutions as well as sample db.
The second approach - SQL solution with additional tables storing range bounds IMHO is more effective and flexible. I would recommend you to take it launch pad. Read it carefully, play around it and when find a proble you are not able to overcome ask for help. If you feel you completely don't understand it I'll try to explain it for you.

Kind regards,
Fish
Aug 22 '08 #5

Expert Mod 2.5K+
P: 2,545
Hi Omnitha. I understand that you may prefer to get an off-the-shelf solution from us to your problem without you yourself being involved in doing some work on the answer - but that is not how this site works. Particularly when there is a chance that this might be a homework problem or assignment of some kind - and it does look like it to me - we simply cannot do your work for you. Our posting guidelines contain notes on homework, as do our FAQs to make this clear.

Fish and ADezii have both pointed you in the right direction, and from here you need to do some work and come back to us if you have specific issues.

-Stewart
Aug 22 '08 #6

P: 7
Stewart,

I sure do agree with your suggestion. My request was neither home work nor an assignment. I was working on an Excel exported from Access and took took some interest in running Access Queries, instead of doing Excel macros.So, thought ask for little help here. Anyways, Forum is very useful and certainly ignited my interest in Access. Will keep working on the solutions suggested by Fishval and Adzeril.

Thanks!

Omnitha


Hi Omnitha. I understand that you may prefer to get an off-the-shelf solution from us to your problem without you yourself being involved in doing some work on the answer - but that is not how this site works. Particularly when there is a chance that this might be a homework problem or assignment of some kind - and it does look like it to me - we simply cannot do your work for you. Our posting guidelines contain notes on homework, as do our FAQs to make this clear.

Fish and ADezii have both pointed you in the right direction, and from here you need to do some work and come back to us if you have specific issues.

-Stewart
Aug 22 '08 #7

ADezii
Expert 5K+
P: 8,669
FishVal is 100% correct in stating that the Banding Table approach is more flexible. Our own Moderator, Stewart Ross Inverness, created a very nice Demo Database illustrating this approach when responding to a prior Thread on a similar Topic. Download the Attached Database and closely study the three Queries, I'm sure all your questions will be answered, but if not, we'll be here to further assist you, if needed. When you open the Database, the first Query will automatically be selected in the Database Window for your convenience.
Aug 23 '08 #8

P: 7
Thanks to all those tried to help me on my query. I was able to get this one working...here is the query which works!

CREATE VIEW AgeGrp1 AS select count(*) as Age20to40 from (select DISTINCT ContactID, primaryBirthdate FROM customers) where Datediff('yyyy',[primarybirthdate],now()) > '20' and Datediff('yyyy',[primarybirthdate],now()) < '40';
Aug 28 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.