How to select all addresses that have residents aged both under AND over 18 (1 Viewer)

[dal1503]

I have a table, with each record being a separate person. The fields I have are:

• Name
• Age
• Address

I need to write a query that returns all records that have people aged both under 18 AND over 18 living in the same address.

Just to clarify, I need the records to have the same address, and to have someone under 18 living there as well as someone over 18 living there. If two records have the same address but only under 18s living there, they shouldn't be returned.

I know I can use AND criteria for the Age field, but not sure how to specify that the Address field has to be the same too.

Any help is much apppreciated

 

[Ranman256]

1st ,you should not have a field AGE. Age would keep changing, so it should be a [birthdate] field with the age calculated in a query....Age: DateDiff("yyyy",[birth],date)

make a query to collect the age cataory, qsAges:
select addr, under: IIF(age<18,1,0), Over: IIF(age>17,1,0) from table

qsAgeSum: sum the query, qsAges, to show ONLY those addresses with BOTH:
select addr, sum(under), sum(over) from qsAges where sum(under)>0 and sum(over)>0

 

[dal1503]

1st ,you should not have a field AGE. Age would keep changing, so it should be a [birthdate] field with the age calculated in a query....Age: DateDiff("yyyy",[birth],date)

make a query to collect the age cataory, qsAges:
select addr, under: IIF(age<18,1,0), Over: IIF(age>17,1,0) from table

qsAgeSum: sum the query, qsAges, to show ONLY those addresses with BOTH:
select addr, sum(under), sum(over) from qsAges where sum(under)>0 and sum(over)>0

This table was imported from an Excel spreadsheet, which itself was exported from an online database that calculates age live... if it was my own database I would use date of birth not age too, but thanks for the heads up

Secondly, thank you for your response. After a small amount of tweaking I got it to do what I wanted. Thanks!