Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2002
    Posts
    13

    Unanswered: Relationship - 2 manys to 1 one ! How ??

    I have been going over this for hours.

    I would like for 2 seperate data sets (Sport1 and Sport2) to pull from the same Table (Sports).

    Eg Sport1 - Volleyball
    Sport2 - Basketball

    Sports:

    Basketball
    Volleyball
    Soccer
    Ect. . .

    I would like Sport1 and Sport2 to pull from Sports
    I have tried various methods to do this. .

    Making 2 fields Sport1 and Sport2, Then creating a seperate table containing The lists of sports.... but i cant use relationships to tie them together..... after connecting Sport1 > Sports, then attempting to connect Sport2 > Sports, It doesn't allow for 2 relationships. . . I select 'No' >> then it adds a fake table called Sports_1 but the 2 do not update properly.

    I am still in a Spreadsheet mindset..... please help me. I love you !

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    cameron, could you give me an example of what the first few rows of each of the "2 seperate data sets (Sport1 and Sport2)" would look like

    if you are just merging similar stuff from two different tables, then they should really share the same structure, i.e. similar data in the same columns

    if you want to keep track of where they came from, add another column like "originaldataset" and populate it with values "1" and "2"

    then if you wanted just rows from the common table which originally came from the Sport1 table, you'd write

    select * from Sport
    where originaldataset="1"

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Jul 2002
    Posts
    13
    Sport1 << i want values to be restricted to the Sports table [combo box]
    Sport2 << same ... restrict to combo box.


    I attached a picture of my relationships.
    right now i have Combo boxes pointing to Queries that take values from the Sports Table.... when i try to insert Sport1 and Sport2 onto the same form... i usually get a #Name error on one or the other field.

    Sometimes.... on the Combo box ... if i tell Sport1 to look at the first row [sportID], then Sport2 to the second row [sport] it seems to work

    This doesn't quite seem right though.... thanks.
    Attached Thumbnails Attached Thumbnails capture_07.gif  

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i'm going to have to bow out, i don't know combo boxes or anything else in msaccess other than tables and queries

    re: capture_07.gif, i can see where the problem is coming from, you've got a rather large table (Title) with 3 slots for sports, and you want to link these via 3 queries to the same table? no idea if you can do that

    this may be too late, but can you remove those three columns from Title? create a new table, TitleSport, with columns Sku and SportID, so if a particular Title has three sports, there will be three rows for it in that table... make both Sku and SportID the primary key (a two-column primary key) of TitleSport... have just one Sport table, and then link the TitleSport table to <i>both</i> the Title and Sport tables via the respective columns

    make sense?

    rudy
    http://rudy.ca/

  5. #5
    Join Date
    Jul 2002
    Posts
    13
    kinda makes sense, but. . .

    The only problem that i can see with what you said is i would like to keep the SKU field a one-to-one relationship to prevent duplicates of sku. Instead... on the report/query end of things i was planning on pulling the values from SportID1, SportID2, and SportID3 to generate information for each sport.

    by the way.... in the pictures.... the Sport_1 was generated by Access not me, as well as Producer_1, and Producer_2... do you know what it is doing ?? is it an index/

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    The only problem that i can see with what you said is i would like to keep the SKU field a one-to-one relationship to prevent duplicates of sku
    i understand your concern, but check this --

    in Title, Sku is the primary key, and therefore unique

    in TitleSport, the pair of columns (Sku, Sport) is the primary key, and therefore unique -- this prevents you from linking Sku X to Sport Y more than once

    there might be three rows for a given Sku in TitleSport, so the same Sku occurs three times in that table, but that's okay -- this is the best way of implementing the one-to-many relationship

    it is 1:m, you know, because the way you have it now, each sku can be related to 3 sports

    if you want to continue with 3 columns of sport links in your Title table, i'm afraid i cannot help with setting up the combo boxes

    rudy

  7. #7
    Join Date
    Jul 2002
    Posts
    13
    thanks rudy... i'll try to approach it that way.

  8. #8
    Join Date
    Jul 2002
    Posts
    13
    Is this looking better..... ? [see attached]

    where do i identify all of the sports.... in this table [TableID]
    Attached Thumbnails Attached Thumbnails 2.gif  

  9. #9
    Join Date
    Jul 2002
    Posts
    13
    Actually.... i think this is right. ??
    Attached Thumbnails Attached Thumbnails 3.gif  

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    beauty! the values in Sport1 are looking great

    remove the Sport column from the Sport1 table -- you could rename the table SkuSport, too, to remind you that it's an "intersection" table

    now create a table called Sports, with SportID as the primary key, and Sport as the character description of each sport

    then you'll have to go back into Sport1 (or SkuSport) and verify that the values are correct, to link the right skus to the appropriate sports

    i believe Sports is a lookup table in this scenario, but i'm not an Access expert, just table design...

    rudy

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    whoops, i replied based on 2.gif

    3.gif is perfect, and i hope you can see now how to implement many-to-many relationships

    (to see how difficult your structure would have been, try writing a query that lists all CAMP skus...)

    good luck

    rudy

  12. #12
    Join Date
    Jul 2002
    Posts
    13
    thanks... you really helped me out.... i had seen many to many but didn't really understand them totally....

    you have been a great help. much appreciated.

Posting Permissions

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