Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    58

    Unanswered: one to many releationship problem

    Hello,

    I have come across a problem with my relationships and hoped somebody could advise me. I have set up the following:

    [Tables]

    Artist
    Label
    Albums
    Singles

    I have a one to many relationship from artist to :

    Singles
    Albums

    and from Label to:

    Singles
    albums

    The problem is that when I click on the + sign in the artist or label table it shows me data from the singles table only. I would like to see both singles and albums data.

    I have looked at the relationship table and all looks to be ok.

    Any ideas. I am new to access so I apologize if this is a stupid question.

    I used the nslook up wizard.

    kind regards,

    Neil

  2. #2
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    No question is a stupid question.

    What it is You have two tables related to one table. It can only show one or the other (in my experience). Is there any way you could relate the Singles and the Album table. Because then you could click the plus sign. The album table would then show under and then click the next plus sign and the singles table would be under that.

    Hope this helps if you need more explanation let me know and I will see what I can do,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    JS is basically on the right path. YOu have what we would call a "circular reference" going on...

    Try something like this:

    label -> artist -> albums -> singles


    Or to be more precise:

    label:
    label_id
    description

    artist:
    artist_id
    label_id
    name

    albums:
    album_id
    artist_id
    name

    singles:
    album_id
    name

    I am of course assuming that you will only have one label per artist...

  4. #4
    Join Date
    Nov 2003
    Posts
    267
    Teddy

    The only problem I see with you new relationships, is an artist can work for one label and then later in their career work for another label. You table structure won't allow that.


    Bascially what neoice problem is... is a limitation in access. the + (SubDataSheets) in the tables will only show you one of the relationships (usually the one you specified) but not more then one.

    Look up SubDataSheet in teh help for more information

    S-

  5. #5
    Join Date
    Oct 2003
    Posts
    58
    Thanks for your help.

    I will try your suggestions. If I still have poblems then I think I will just combine the singles and albums into one table called records.

    Kind regards,

    Neil

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by sbaxter
    Teddy

    The only problem I see with you new relationships, is an artist can work for one label and then later in their career work for another label. You table structure won't allow that.
    Yeah, that was what I meant by my footnote of "assuming you only have one label per artist".

    The workaround would be to create another table, we'll call it affiliations, and tie the affiliations to the album:

    artist -> affiliations
    label -> affliliations
    affiliations -> album

    etc

  7. #7
    Join Date
    Oct 2003
    Posts
    58
    Hello,

    Do you see any problems if i just have the following tables:

    Artist
    Label
    Records

    Inside the records table would be a field called Record_Type. This field would hold either Albums or Singles.

    I could then create a query on albums and another for singles.

    I would have a one to many from artist to records and label to records.

    One other thing, is it possible to display how many records you have in a text box on a form - maybe some VBA code would do it?

    Many thanks

    Neil

  8. #8
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Yes it is possible to display a record count. I dont know how to do it though... The tables look good. That was a good mix. You may have had duplicate data if you did it the other way. (Is it possible for it to be a single and a record?) If so then you may want to substitute what ever you have now for two check boxes.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  9. #9
    Join Date
    Oct 2003
    Posts
    58
    Check boxes are a great idea!

    Thanks

    Gosh, 11pm and time for bed. thanks for your help.

    Neil

  10. #10
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    hehe not a problem its only 5pm here time to get off of work... later
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

Posting Permissions

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