Report of Age Range

davidjearly

Registered User.
Local time
Today, 10:27
Joined
Apr 30, 2009
Messages
40
Hi,

I have a table containing patient data, including a column which has dates of birth for all patients, and another column containing the ages of all patients.

I would like to produce a report containing a chart of all patients split into ages ranges as follows: <15, 15-19, 20-24, 25-29, 30-34, 35-39, >40.

Is this possible?

Many thanks!
 
First a word of advice, do not carry age in a table it gets out of date and can easily be calculated from DOB as when rquired. The forum contains many examples.

To count the number of patients falling into each band you use a Sum -If approach
age<15:Sum(iif(age<15,1,0)) etc
you can just code the IIf part and then switch to a totals query and select Sum for each field, this allows you to check the data easily.

Brian
 
Hi -

You need to lookup the Partition() function.

Heeding Brian's advice about not storing calculated items (e.g. Age), here's an example you can modify to fit your scenario:

Code:
SELECT  DISTINCT  Partition(DateDiff("yyyy",[DOB],Date())+(DateSerial(Year(Date()),Month([DOB]),Day([DOB]))>Date()),16,25,5) AS Range
  , Count(tblClients1.SSN) AS CountOfSSN
FROM
   tblClients1
GROUP BY
Partition(DateDiff("yyyy",[DOB],Date())+(DateSerial(Year(Date()),Month([DOB]),Day([DOB]))>Date()),16,25,5);

I had to specify a 16-25 range, since my table doesn't contain ages >=40. As written, it returns:

PHP:
 RangeCountOfSS
  :15	55
16:20	738
21:25	1220
26:  	140

HTH - Bob
 
Last edited:
Thank you both for the advice, but I am unsure how to implement it. As obvious as it is, I didn't realise that the ages column in the table would quickly become out of date.

So, to break this down the first thing I should do is remove the 'Age' column from my table, 'Patients', and leave the 'DOB' column, yes?

Assumming that is correct, how would I go about including the code above?

I have one form and one report that I would like to include the age data in. The form is a basic 'records' form created using the data in my table. This presents the patient data in a way that included the 'Age' column from my table. Assumming I should remove that as suggested, how could I display the clients age on that form without linking it to the table?

Lastly, how would I go about using the code above to create a report showing the age range of patients under the following ranges: <15, 15-19, 20-24, 25-29, 30-34, 35-39, >40?

Many thanks for your input.
 
Ok, slight update here. I have designed a query that returns the ages for all the birthdates in my table.

I have created a textbox on my form, linking to the DOB field and used an expression that calculates the age for each record. This is working ok now.

So, the first part of my query I have managed to solve by browsing this forum and the web.

However, the second part (designing a chart showing the age spread in the ranges I mentioned), I am still stuck with!

Any advice for this part would be most appreciated!
 
To implement the example provided, you need two things:

1) A date of birth ([DOB])

2) An unique identifier for each paricipant. I used SSN (Social Security Number).

No separate query is required to compute age, This:

(DateDiff("yyyy",[DOB],Date())+(DateSerial(Year(Date()),Month([DOB]),Day([DOB]))>Date())

...does it for you.

This: (DateSerial(Year(Date()),Month([DOB]),Day([DOB]))>Date()) is a boolean statement that equates to -1 if True, 0 if False.

It's now just a matter of substituting your table and field names to the example provided.

If this is still 'clear as mud', please provide your table components and I'll attempt to return a solution.

Best wishes - Bob
 
Last edited:
raskew - Many thanks for the explanation, however, in my last post I already said that I had figured this part out. I am now stuck on how to create a chart showing the age distribution in the ranges I listed above.

Can you help with this?

Thank you.

Edit:

To be clear, here is the code you posted above, adapted with my table name/identifier.

Code:
SELECT  DISTINCT  Partition(DateDiff("yyyy",[DOB],Date())+(DateSerial(Year(Date()),Month([DOB]),Day([DOB]))>Date()),16,25,5) AS Range
  , Count(Patients.CHI) AS CountOfCHI
FROM
   Patients
Group BY
Partition(DateDiff("yyyy",[DOB],Date())+(DateSerial(Year(Date()),Month([DOB]),Day([DOB]))>Date()),16,25,5);

Not too sure what to do next.

Thanks for your patience.
 
Last edited:
Hi David -

Is that query SQL working for you? To test, copy/paste this to the SQL of a new query. It should return something as I provided in my example. To show up to 40 years, you'll need to change the two references to 16, 25 to 16, 40.

Sorry to say, I'm not a chart person. Hopefully someone else can step in on this issue.

Best wishes - Bob
 
raskew - I'm getting the following error: 'Data type mismatch in query expression'.

Any tips?

Cheers again,

David
 

Users who are viewing this thread

Back
Top Bottom