434,762 Members | 1,838 Online
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

# Query to calculate the age

 P: 77 Please help. I need to find out the age (by years, months and days) of all patients in the admittance table that were admitted to a program in 2004, 2005 & 2006. We are basically trying to find out how many of them were in the range ( 17 yrs & 1 month - 18 yrs & 1 month ) on 12/31 of each year mentioned above. The admittance table has fields, CaseID, SeqNbr, Birthdate. Please help me with a query or algorithm that can be used in a query to find the age. Thanks a lot in advance Jan 3 '08 #1
4 Replies

 Expert Mod 10K+ P: 12,366 What have you tried so far? I venture that you would use DateDiff to calculate the difference between their birthdate and some entry date. And then just keep the ones where their entry date is in your chosen years and the difference is within your tolerance. Jan 3 '08 #2

 P: 77 They changed the request overnight !! Below is what I am trying to do now, I have the list of patients who were in care during the years 2004, 2005 and 2006. I want to find the patients who were 18 years and 1 month old in Jan 2004, Feb 2004.....Dec 2004 (basically each month of the year). Similarly I need to do the same for 2005 and 2006. Please treat this as urgent....thanks for the help. Jan 4 '08 #3

 Expert Mod 10K+ P: 12,366 They changed the request overnight !! Below is what I am trying to do now, I have the list of patients who were in care during the years 2004, 2005 and 2006. I want to find the patients who were 18 years and 1 month old in Jan 2004, Feb 2004.....Dec 2004 (basically each month of the year). Similarly I need to do the same for 2005 and 2006. Please treat this as urgent....thanks for the help. Do you mean they were that age at entry? It's still gonna basically be what I laid out in my prior post. Use DateDiff() against their birthdate and entry to get their age. And then make sure thats within your tolerance. There's too many unknowns right now for me to know what you need. When you say 18 years and 1 month are you talking about 6605 days old? Or is there some sort of range. Do you need a list for each month? Or are you just talking about age at entry? age during time served? age at exit? Jan 4 '08 #4

 Expert 100+ P: 254 They changed the request overnight !! Below is what I am trying to do now, I have the list of patients who were in care during the years 2004, 2005 and 2006. I want to find the patients who were 18 years and 1 month old in Jan 2004, Feb 2004.....Dec 2004 (basically each month of the year). Similarly I need to do the same for 2005 and 2006. Please treat this as urgent....thanks for the help. Make a Table for the Dates (Month end dates) in question (1/31/2004 thru 12/31/2006). This will make it easy for you to maintain vice dynamically creating an array and looping thru it. Just 1 column needed. Name the column MoEnd (month and year are reserverd words - can be used but good practice not to). Name the table - tblDates. You can name it all whatever you want but you will need to change what I post here. (Best to leave out spaces when naming as well use underscores instead). If you are looking for patients between 17 yrs and 1 month and 18 yrs and 1 month (205 months to 217 months): Select Format(MoEnd,"mm_yyyy") as Month_Yr, YourAdmitTable.CaseID,YourAdmitTable,SeqNbr, 'Y' As [InRange] From YourAdmitTable, MoEnd Where DateDiff("m",Birthdate,MoEnd) Between 205 and 217 Order By MoEnd; If looking for 217 month olds change where line to: Where DateDiff("m",Birthdate,MoEnd) = 217 If looking for all less than 217 month olds change where line to: Where DateDiff("m",Birthdate,MoEnd) < 217 This will get you going since we are not really sure on EXACTLY what you are looking for. Have a great weekend! Jan 4 '08 #5