Dumb query question (2000sr1a)

Topic

New Reply

I’m sure this is really dumb, but I’m not certain and you’re experts, so here goes.

In my master list of individuals, I have levels of identification in descending order, of hamlet, group, household number, and index in house. I want to make sure that household number is unique within hamlet, but noticed on occasion that household numbering is duplicated within a hamlet, particularly where there are different groups. I have assigned unique property ID’s in the whole list, but the household number is important to people in the field, who can’t deal with propertyUID’s, so I want to get it right.

How can I pull out a query which will give me those household numbers which are duplicated within a hamlet, but exclude different members of the same family from this (so obviously index in house will have to be there somewhere).

I tried this:SELECT ListNameFullList.ID, …..etc …. FROM ListNameFullList INNER JOIN ListNameFullList AS ListNameFullList_1 ON ListNameFullList.Household = ListNameFullList_1.Household
WHERE (((ListNameFullList.ID)));

but I’m worried that it’s returned far fewer records than I’d expected.

My brain is now jelly trying to find alternative ways round what is conceptually a very simple problem. All help appreciated.

Thanks

Reply | Quote

Replies

Have you tried the Find Duplicates Wizard?

1. Start to create a new query
2. Select Find Duplicates Wizard
3. Select ListFullNameList as source
4. Select ‘Hamlet Code’, ‘Household’ and ‘Index in House’ as fields with duplicate values
5. Select any other fields you want to be displayed
6. Finish the Wizard and look at the result

HTH, Hans

Reply | Quote

I still got the same results, but that was helpful too.

Thanks for the instructions on using the find duplicates wizard. I have to say I found the running instructions unhelpful in helping me decide what I would actually get, the online help totally useless, and trying to play around with the SQL statements (with Access’s usual lack of help on SQL) also frustrating.

I’m sure your suggestion did get to what I want (looking at the SQL statement as well), just there’s something wrong (or at least unexpected) in my data.

So yes, you did help! Thanks

Reply | Quote