How to make a table with age distribution per service per month

[DanielleN]

Hi all,

I am struggling with access and can't seem to find a proper solution to my problem.
I have a patient database. In here you can find, among others, the date of birth of the patient, the date that the patient was treated, the service provided (you can choose physio, rehab or ortho).

So what I would like is to create a cross table showing the service provided for age groups per month. It should look something like this
On top in the columns the months: jan feb mar etc.

Physio 0-4
5-19
20 and up
Rehab 0-4
5-19
20 and up

I was able to make the cross tabs by month and service but i have no clue how to do the age distribution. I did manage to calculate the age though.
Can someone please help me out on how to make the age distributions and to get a table as shown in my example?



Kind regards, Danielle

[CJ_London]

you'll need to use a nested iif or choose function

iif(age <=4,"0-4",iif(age <=19,"5-19","20 and up"))

or

choose(age <=4,"0-4",age <=19,"5-19",true,"20 and up"))

[ItsMe]

@Ajax
I was unaware of the Choose function and it piqued my interest. When I looked at MSDN, I was unable to understand the syntax you provided. I am understanding the function to behave more like an enumeration and less like a ternary.
https://msdn.microsoft.com/en-us/lib.../gg264716.aspx

[CJ_London]

Ah yes - you are right - I meant the switch function. As you say choose is based on an incremental value

[ItsMe]

Thanks for explaining, Ajax.

[DanielleN]

Something really strange is happening in my access.. I tried both your suggestions and they are not working. I now have this:
Agedistribution: Switch([Age]<=4,"0-4",[Age]<=19,"5-19",[Age]>19,"20 and up")

But the computer says the syntax of the expression is not valid, highlighting the "0-4".
I use access 2016.

If i open the same document on my work computer (access 2010) it works perfectly.

Any idea what is going on?

[CJ_London]

Any idea what is going on?

Nope - syntax looks fine to me. I'm assuming that if you are using a crosstab or groupby query that Age is a field and not a calculated value. If it is a calculated value then you need to substitute [Age] with the calculation, although the error you would have had would not have been a syntax error.

[orange]

Danielle,
As others have said nested if and switch can be used to create a Histogram.
Here is another function that may be of interest to you.

[DanielleN]

Thank you all for helping me. Ended up doing all me age distributions using the switch function in Access 2010 and later opening it in access 2016 and (strangely enough) this works.