testing for multiple values

[Will04]

Hello everyone,

I have a table with data on household members and their relationship to the head of the household.

Each record has a field ABHID which comprises of the AREA CODE [2], BUILDING NO[4] and HOUSEHOLD NO[2]

eg. 02014401 where AREACODE="02" , BUILDING NO ="0144" and HOUSEHOLD NO ="01"


Each record stores the relationship to Head which can be HEAD, SPOUSE, CHILD, PARTNER, RELATIVE or NON-RELATIVE

I would like to create a query to select only the household with nuclear families WITHIN AREA CODE i.e. those households with the relationship being either HEAD, SPOUSE, PARTNER and CHILD.

For example, if a household has 6 members - HEAD, SPOUSE and FOUR CHILDREN, the ABHID number should be counted once for that particular AREA CODE.

As long as a member of that household is a RELATIVE or NON-RELATIVE that household is not counted

Any suggestions on how I can do this??


Many thanks in advance

Will

 

[Will04]

BTW I'm using Access 2007

Thanks

 

[Will04]

Guys,

So far, no responses <sigh>..

Is it that difficult???



Will

 

[vbaInet]

It is that difficult <sigh> = False

Something like this:

SELECT Count(*) AS CountOfMembers, [ABHID] 
FROM TableName 
GROUP BY [ABHID]
HAVING Relationship='Head' OR Relationship='Spouse' OR  Relationship='Partner'  OR Relationship='Child';

But saying that, I think your database is not normalized.

 

[Will04]

Hi vbaInet,

Thank you for taking time to respond, much appreciated

SELECT Count(*) AS CountOfMembers, [ABHno] 
FROM HHDetails
GROUP BY [ABHno]
HAVING Rel2hd='Head' OR Rel2hd='Spouse' OR  Rel2hd='Partner'  OR Rel2hd='Child';

However, when I run the above code I get the following msg..

You tried to execute a query that does not include the specified expression ‘Rel2hd='Head' Or Rel2hd='Spouse' Or Rel2hd='Partner' Or Rel2hd='Child'’ as part of an aggregate function.

Looking at the code, I think it will not give the results that I'm looking for...i.e.

Each household can have one or more members (each member having the same household number i.e. ABHno).

In order to be counted as a Nuclear household, there MUST BE a 'Head', 'Spouse', 'Partner' and at least one 'Child', however that household MUST NOT have as members a 'Relative' or 'Non-relative'.

e.g. A NUCLEAR HOUSEHOLD

ABHno Rel2Hd
02014401 Head
02014401 Spouse
02014401 Child
02014401 Child

e.g. NOT A NUCLEAR HOUSEHOLD

ABHno Rel2Hd
02014408 Head
02014408 Spouse
02014408 Child
02014408 Relative

I believe that the code when executed, will select both these households as valid, instead of rejecting the 2nd example because of the presence of a 'Relative'

Many thanks again for your help..


Will

 

[vbaInet]

Well in that case you're looking at a more sophisticated query. Here are the steps:

1. Filter out the [ABHno]s that have 'Relative' OR 'Non-Relative' in them. To do this you need a subquery as follows:

Not In (Select Distinct ABHNO FROM TableName Where  Rel2hd='Relative' Or Rel2hd='Non-Relative')

That whole line will go in the criteria under ABHNO.
2. Save the query and let's say you call it qrySetup_Pt1
3. Create another query and use qrySetup_Pt1, include all the fields from there.
4. Your next and final step would be to use yet another subquery to filter the [ABHno]s that have Head, Spouse AND (at least one) child. So you need to put something like this in the criteria for ABHNO again:

(Select Q.ABHno From qrySetup_Pt1 As Q Where Q.ABHno = qrySetup_Pt1.AbHno And Q.Rel2Hd='Head') [COLOR=Red][B]AND[/B][/COLOR] (Select Q.ABHno From qrySetup_Pt1 As Q Where Q.ABHno = qrySetup_Pt1.AbHno And Q.Rel2Hd='Spouse') [COLOR=Red][B]AND[/B][/COLOR] (Select Q.ABHno From qrySetup_Pt1 As Q Where Q.ABHno = qrySetup_Pt1.AbHno And Q.Rel2Hd='Child')