Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Dumb query question (2000sr1a)

    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)<>[list]));

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dumb query question (2000sr1a)

    Have you tried the Find Duplicates Wizard?
    1. <LI>Start to create a new query
      <LI>Select Find Duplicates Wizard
      <LI>Select ListFullNameList as source
      <LI>Select 'Hamlet Code', 'Household' and 'Index in House' as fields with duplicate values
      <LI>Select any other fields you want to be displayed
      <LI>Finish the Wizard and look at the result
    HTH, Hans

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Dumb query question (2000sr1a)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •