473,320 Members | 1,535 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query to calculate the age

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 1898
Rabbit
12,516 Expert Mod 8TB
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
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
Rabbit
12,516 Expert Mod 8TB
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
jaxjagfan
254 Expert 100+
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

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

Similar topics

4
by: Bacci | last post by:
I have two tables. The first is "Locations" which has 52,000 zip codes w/cooresponding latitudes and longitudes. The second "Suppliers" has 2,000 company names and addresses. The user enters a...
5
by: Stephen Miller | last post by:
Hi, I am trying to add a staggered running total and average to a query returning quarterly CPI data. I need to add 4 quarterly data points together to calculate a moving 12-month sum (YrCPI),...
6
by: paii | last post by:
I have a table that stores job milestone dates. The 2 milestones I am interested in are "Ship Date" TypeID 1 and "Revised Ship Date" TypeID 18. All jobs have TypeID 1 only some jobs have TypeID 18....
1
by: Simon Bailey | last post by:
I currently have a query calculating the gap in days between two dates. The fields being "DateLookedAt" and "DateResolved" plus the calculated field "TimeTaken". I am looking to add another...
3
by: Jamie Pittman via AccessMonster.com | last post by:
I am having trouble bellow wit this query. I have the total regular hours and the overtime. The problem is that if it is 8 hours and under, I need it to show as regular hours. Any thoughts? ...
5
by: Wired Hosting News | last post by:
I tried to be breif and give a scenario so as not to be overlooked because it was soooo long. Let me give you real world. I am a manufacturer of goods and produce 11 items that are distributed...
5
by: jonm4102 | last post by:
I'm trying to calculate the median of some numerical data. The data can only be found in a query (henceforth query 1) field I previously made, and I would prefer to calculate the median in a new...
21
by: jennwilson | last post by:
Ok - So, I am back. I would like to count the number of times a specific record appears in a field from one table in my query and then use that value in the same query to calculate an average....
1
by: commodityintelligence | last post by:
Greetings, I am merging a series of different tables into one query to export decision-making information. I have some architecture issues I need to ask for help on. I have no programming...
1
by: alzaabi20xx | last post by:
hello everyone i hope you could help me in this query. i build a query which you give it a month and a year and after it will calculate the overtime of that month (choosen month & year). my...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.