Different approach
create three queries
1. return all the flats with on adult, name it OneAdult
SELECT ADD.HouseNo, ADD.FlatNo
FROM People INNER JOIN [ADD] ON People.ADD_ID = ADD.ID
WHERE (((People.Age)>"16"))
GROUP BY ADD.HouseNo, ADD.FlatNo
HAVING (((Count(ADD.FlatNo))=1));
2. Return all the flats with kids, name it FlatsWithKids
SELECT ADD.HouseNo, ADD.FlatNo
FROM People INNER JOIN [ADD] ON People.ADD_ID = ADD.ID
WHERE (((People.Age)<"16"))
GROUP BY ADD.HouseNo, ADD.FlatNo;
3. Join the two queries
SELECT FlatsWithKids.HouseNo, FlatsWithKids.FlatNo
FROM OneAdult INNER JOIN FlatsWithKids ON (OneAdult.FlatNo =
FlatsWithKids.FlatNo) AND (OneAdult.HouseNo = FlatsWithKids.HouseNo);
--
In God We Trust - Everything Else We Test
Ofer said:
Try this
SELECT HouseNo, FlatNo
FROM People INNER JOIN [ADD] ON People.ADD_ID = ADD.ID
WHERE People.Age<16 AND ADD.HouseNo In (SELECT ADD.HouseNo
FROM People INNER JOIN [ADD] ON People.ADD_ID = ADD.ID
WHERE People.Age>16
GROUP BY ADD.HouseNo
HAVING Count(ADD.FlatNo)=1)
--
In God We Trust - Everything Else We Test
:
nope,
some records are filtered, am trying to work out which though.
I do know that some of the ones shown are showing addresses where there are
people over 16 with no children.
I figured count may need to be used? Not sure how it works tho' but the
reason I say this is because I need addresses where there are only One adult.
:
What do you get?
no records?
all the records?
--
In God We Trust - Everything Else We Test
:
yep, you were correct to assume the joining field, sorry about that,
however this doesnt seem to be working.
:
Try this:
SELECT HouseNo, FlatNo
FROM People INNER JOIN [ADD] ON People.ADD_ID = ADD.ID
WHERE People.Age>16 AND ADD.HouseNo In (SELECT ADD.HouseNo
FROM People INNER JOIN [ADD] ON People.ADD_ID = ADD.ID
WHERE People.Age<16)
You didn't specify which field join the tables, so I joined them by the ID.
--
In God We Trust - Everything Else We Test
:
Hi
Thanks in advance.
I have 2 tables in access that contain add info and person info.
Ea address can have many people
What i need to do is find out how many addresses contain lone parents
ie - if an address has ONE person over age of 16 AND at least one person
below age of 16.
my tablesa are:
ADD:
FlatNo, HouseNo, Add1, ID
PEOPLE:
PersonID
Forename
Surname
Age
ADD_ID
I have seen some very small queries in the past,
but am affraid I dont know where to begin on this one!
Please Help!
Thanks
