Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    3

    Unanswered: Help finding single occurrences of value

    Hi
    I am helping a small nonprofit straighten out their data and I am stumped on how to best do something.

    I have tables

    Individual table with individual.id and family.reference
    Financial table with individual.ID and and family refernce

    Family refernce is an indexed field with duplicates

    If the family refernce code in the individual table has only one occurrence with its value I need to assign the individual table.id to the individual.id in the financial table.

    Example

    Individual table

    Id. Family refernce
    1. Fam1
    2. Fam1
    3. Fam2
    4. Fam3
    5. Fam4
    6. Fam4

    For Id 3 and 4 I want to set the financial table rows with a family refernce of fam2 to have an individual Id of 4 and those with a family reference of family reference of fam3 to have an individual Id of 4. I do not want any action for fam1 or fam2 because they have more than one occurrence.

    Not sure if this can be done in SQL. Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not sure to understand what you want to achieve. For retrieving the rows having a unique family reference you can use:
    Code:
    SELECT Individual.Id, Individual.Family_reference
    FROM Individual
    WHERE Individual.Family_reference In (SELECT [Family_reference] 
                                          FROM   [Individual] 
                                          GROUP BY [Family_reference] 
                                          HAVING Count(*)=1 );
    But I don't know for sure what you want to do from there: Create new rows in the other table ("Financial") or update existing rows?
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    3

    Thanks,

    I will try that. I then want to update the financial_table.individual_id with the individual_table.id for the selected rows. Thank you.

  4. #4
    Join Date
    Jan 2012
    Posts
    3

    It worked

    Thank you very much

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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