Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    Indiana, USA
    Posts
    79

    Unanswered: Report where information fits multiple catagories

    I am working on a database for my genealogy contacts and want a report listing each person sorted by the Surname they are researching. Easy Enough.

    Problem is that some are researching more than one Surname that connects with my database and I need that contact to appear under both Surnames on the same report.

    On the Contacts Information page (Contacts Table) I have two Surname Drop Boxes named Surname and Surname2.

    Example:

    Magilla Gorilla is researching Hattery (Surname) and Moore (Surname2)
    Cowardly Lion is researching Peacock (Surname) and Hattery (Surname2)
    Fairy Princess is researching Green (Surname2) and Moore (Surname2)

    The report should read

    Hattery
    Magilla Gorilla ...
    Cowardly Lion ...

    Moore
    Magilla Gorilla ...
    Fairy Princess

    Peacock
    Cowardly Lion ...

    Green
    Fairy Princess ...

    Any ideas would be greatly appreciated. Also please remember that I know just enough to screw things up and need explainations for dummies.

    Perplexed

  2. #2
    Join Date
    Aug 2004
    Posts
    40
    So to clarify things a bit..

    Each researcher can only have 2 surnames? How do you cope with researchers doing research on 20 people?

    Howard

  3. #3
    Join Date
    Feb 2004
    Location
    Indiana, USA
    Posts
    79

    More Confusing All The Time

    So far, I am having trouble coping with two surnames. My hope is that if I can figure out two then I can apply the information to 20. There may be a more efficient way to track the list by Surnames but I don't know what it is. So far it is unusual to have more than two or three Surnames in Common unless they are a very close relative.

    Any Ideas?

  4. #4
    Join Date
    Aug 2004
    Location
    NYC
    Posts
    1
    you need a new table perhaps named tblContact_Researches
    Fields:
    ContactID
    SurnameID

    This is a many to many table (where a contact can research many surname and a surname can be researched by many contacts).

    Then to do the report based on a query involving all three tables (tblContact, tblSurname, and tblContact_Researches. Join the tables appropriately and then do a report grouping by surname.

    Hope this helps.



    Quote Originally Posted by Perplexed
    So far, I am having trouble coping with two surnames. My hope is that if I can figure out two then I can apply the information to 20. There may be a more efficient way to track the list by Surnames but I don't know what it is. So far it is unusual to have more than two or three Surnames in Common unless they are a very close relative.

    Any Ideas?

  5. #5
    Join Date
    Aug 2004
    Posts
    40
    Ok... a Union query is what you want.

    Create a union qry like below;

    Code:
    SELECT surname1 AS surname,researcher FROM table1
    UNION SELECT surname2 AS surname, researcher FROM table1;
    The result will be

    Surname: Researcher:
    Green Fairy Princess
    Hattery Cowardly Lion
    Hattery Magilla Gorilla
    Moore Fairy Princess
    Moore Magilla Gorilla
    Peacock Cowardly Lion

    Then group on the surname in your report. I've only leant about Union queries lately and boy are they a godsend.

    You'll just have to add more if you add more surnames

    Anyway HTH

    Howard

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the union query is fine.... for two surnames

    see post #4 if you want to expand at all

    having multiple surnames on one row in the table is not a good design because of this very problem, changing the structure (and the union query) to add another surname

    it's a good idea to start with the many-to-many relationship table as soon as you go from 1 to 2, not from 2 to 20
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2004
    Posts
    40
    here here.. R937 it would be much better to design the db properly, I was only addessing his immediate problem. Yes perplexed redsign it as per dwakefield has suggested but the union query will work in it's current state.

    Good luck

  8. #8
    Join Date
    Feb 2004
    Location
    Indiana, USA
    Posts
    79
    Thank you hpicken and dwakefield,

    I appreciate both the quick fix and the design information. I got to learn two things today. Have things working fine now.

    You're the best!
    Perplexed

Posting Permissions

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