Age Calculation Count in Query (1 Viewer)

[batteredveg]

Hey guys

Hope you can help me - I'm trying to create a query which summarises a table containing people's case references and dates of birth.

I want to summarise, in a query, how many adults and children are contained within each case (using some use of Group By and Count in Query Builder I imagine), but I can't seem to work it out.

My table looks something like this:

Record ID   Case Ref   Name                   Date of Birth   ...
---------   --------   ---------------------  -------------
10001       ABC1001    John Smith             01-Jan-84       ...
10002       ABC1001    Janie Smith            04-Feb-81       ...
10003       ABC1001    Mark Smith             15-Apr-15       ...
10004       ABC1001    Jackie Smith           15-Apr-15       ...
10005       XJN9999    Mary Brown             01-Feb-76       ...
10006       XJN9999    Peter Brown            25-Dec-15       ...

And I would like my query to output like this:

Case Ref   Adult Count   Child Count
--------   -----------   -----------
ABC1001    2             2
XJN9999    1             1
...        ...           ...

Any advice on a query which would do the job would be greatly appreciated!

Regards

Paul

 

[arnelgp]

add a calculated field using count:

adult count:iif(datediff("yyyy", [date of birth], date) > 17, 1, null)
child count:iif(datediff("yyyy",[date of birth], date) <=17, 1, null)

 

[batteredveg]

add a calculated field using count:

adult count:iif(datediff("yyyy", [date of birth], date) > 17, 1, null)
child count:iif(datediff("yyyy",[date of birth], date) <=17, 1, null)

Thanks arnelgp - such a simple solution and I couldn't see it. Much appreciated!