Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    kannuberg is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2009
    Posts
    10

    Unhappy two relations to one table?

    Hi
    I have a problem. I have a booking-table that is related to two other tables.


    See attached picture.

    I want to make a booking-report with the people that have appointments. The problem is that the information comes from two different tables.

    Untill now I have always just used one relation but in this case I need two relations and I can't get it to work.

    What am I doing wrong? is it the type of relations or what?

    Thank you in advance for any help.

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Why are you using 2 different tables? you can load/relate the same table more than once by using alias's.

    David

  3. #3
    genesis is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    39
    i think you have two tables here: booking-calendar table and the persons table.

    i think it is better if you just omit the persons-b dropdown box because in your table booking-calendar table, you can add as many persons that you like. it will be easier for you to create report from there using the said table.

  4. #4
    kannuberg is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2009
    Posts
    10
    Quote Originally Posted by dcrake View Post
    Why are you using 2 different tables? you can load/relate the same table more than once by using alias's.

    David
    I use two different tables because A-persons and B-persons have the same number. A-persons are actually grandmothers through the mother side and B-persons are grandmothers through the father side.

  5. #5
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    You could still have them in the same table. All you need to do is to have an additional field that denotes the bloodline. Then use these as a two part no duplicates key. By using the autonumber as a primary key you will be able to make them unique.

    David

  6. #6
    genesis is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    39
    what do you mean have the same number? do you mean same ID or same number of persons?

  7. #7
    kannuberg is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2009
    Posts
    10
    Quote Originally Posted by genesis View Post
    i think you have two tables here: booking-calendar table and the persons table.

    i think it is better if you just omit the persons-b dropdown box because in your table booking-calendar table, you can add as many persons that you like. it will be easier for you to create report from there using the said table.
    yeah, but the problem is that the number that the calendar-dropdownboxes relate to, is the primary key of personsA and personsB and the numbers are identical. I don't think I can get that into one dropdownbox or what?

  8. #8
    genesis is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    39
    I think you should create your unique ID for relational purposes and an ID that is intended for the relationship being an individual. cant it be done that way?

  9. #9
    kannuberg is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2009
    Posts
    10
    sorry, both tables have ID-numbers which are also primary keys. The ID-number for one grandmother is the same as for the other grandmother

  10. #10
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    This is obviously a case of denormaisation, you need to get your tables correct in order to progress.

    David

  11. #11
    genesis is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    39
    I think that ID that you say is really existing in your workplace as have been designated already. So if that is the case, why not create an ID that is a primary unique individual id that is not designated by whoever. such purpose is for your relationship only to the other table.

  12. #12
    kannuberg is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2009
    Posts
    10
    Quote Originally Posted by dcrake View Post
    This is obviously a case of denormaisation, you need to get your tables correct in order to progress.

    David
    Thanks for taking time to respond.
    So you say that the ID-numbers, which are primary keys and also identical for both grandmothers, should not be primary key? There is autonumber on both tables, should I make them Primary key?

  13. #13
    kannuberg is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2009
    Posts
    10
    Quote Originally Posted by genesis View Post
    I think that ID that you say is really existing in your workplace as have been designated already. So if that is the case, why not create an ID that is a primary unique individual id that is not designated by whoever. such purpose is for your relationship only to the other table.
    Ok, I think I understand now. Maybe I have confused the idea of primary keys and relationships. I will try to mov.e primary key from the current ID to the autonumber and hope that nothing bad will happen

    Thank you for your help.

  14. #14
    genesis is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    39
    pertaining to your post number 12. yes. that should not be your primary key. autonumber on the other hand maybe your option to use as primary key but it would be wiser if you will not rely on autonumbers as primary key, you can always create new id by yourself.

  15. #15
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    What I suggest is the following:

    Edit table A and add a new field

    Bloodline Text 1

    Run an update query on this field and populate it with "M" Maternal

    Create an append query and append data from table 2 into table 1 and for the field Bloodline append "P" Paternal.

    You will now have both sets of grandparents in the same table each person being either a maternal or paternal parent.

    Each one will have their own autonumber which becomes the primary key.

    You can then create two queries

    QryMaternal & QryPaternal

    Set a condition in each one M & P

    Now in your relationships you relate QryMaternal to ParentA and QryPaternal to ParentB on a one to one relationship. As the parent can oly be either the maternal or paternal grandparent, they cannot be both.

    David

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums