Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2005
    Posts
    37

    Unanswered: Question about Record Data in Table

    I'm building a database in which I'm trying to include in a record several entries from another table. In other words, Table A will include record 1, record 2, record 3, etc. In the Table B, in addition to date and other data, I would like each record to include 1 or more entries from Table A. For example, record 1 in Table B would include at least 1 record from Table A, say record 3 and record 9. Record 2 in Table B could include record 1, record 12, record 15, in addition to the other data. Each record would include at least 1, and could include up to 17 total records from Table A.

    What would be the best and/or easiest way to do this?

    Thanks in advance.

    Bruce

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You either have a one to many or a many to many relationship between the tables. If a table A record can only be associated with one table B record, it's one to many and you could store the ID of the related table B record in table A. Otherwise, you have a many to many relationship, which would require a junction table. That table would have fields for the ID from A and the ID from B. In your example, it would contain 5 records:

    Code:
    A   B
    3   1
    9   1
    1   2
    12  2
    15  2
    Paul

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    To expand upon pbaldy's answer, what he's talking about is database normalization. There's a good article about it here, or on microsoft's site. Specifically, if it's a many to many, I think you need at least 4NF (I think).
    Me.Geek = True

  4. #4
    Join Date
    Sep 2005
    Posts
    37
    Thanks guys. That helps, but let me explain a little more clearly what I'm trying to do.

    Table A
    Person 1
    Person 2
    Person 3

    Table B
    Event 1 Date Person 1 Person 3
    Event 2 Date Person 2
    Event 3 Date Person 1 Person 2

    Each event would include at least 1 person, but could include many more. Instead of creating multiple person fields in the record, which most would probably be null, I would like the fields to include the number of persons associated with the particular event. All of the persons would be listed in Table A. How should I set up my table to accomplish this?

    Thanks.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Table B would only include event related info, nothing about people. You'd add the third table I mentioned to related events to people.
    Paul

  6. #6
    Join Date
    Sep 2005
    Posts
    37
    I'm having a hard time getting this through my thick skull. I'm not exactly sure how to get the junction table to work. Here is what my tables look like:

    tblEvents
    EventID (autonumber) PK
    EventName
    Date
    Time

    tblPersons
    Person PK

    Ideally, I would like to have a form which would allow me to enter the event data, along with ther persons that are involved. There are 15 listed in the tblPersons table. An event could have only 1 person, or could have all 15, but usually only 4 or 5. I'm thinking that when I enter all of the persons involved in the event, they are all linked to the EventID for that event. Ultimately, on the report, I'd like to see:

    EventName, Date, Time, Person, Person, Person, Person, and so on.

    Would this be a one to many since one event can have many persons, or a many to many because one event can have many persons, but each person will be linked to many events?

    If I need to create a junction table, please show me what I need to do to get headed in the right direction. Been messing with this all night, and I'm confusing myself more.

    Thanks.
    Bruce

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    If I'm thinking along the same lines as pbaldy, I think he's suggesting you have one table with just people info, like:

    Table A
    {PersonID} | {FirstName} | {LastName} ...
    Person1 | Aaron | Anderson
    Person2 | Bob | Billson
    Person3 | Charlie | Carlson
    ...


    Then another table with just Event info, like:

    Table B
    {EventID} | {Date} ...
    Event1 | 1/5/2009
    Event2 | 2/3/2009
    Event3 | 3/1/2009
    ...


    Then you create a third table to relate the two, so like:

    Table C
    {RelationshipID} | {PersonID} | {EventID}
    Rel1 | Event1 | Person1
    Rel2 | Event1 | Person3
    Rel3 | Event2 | Person2
    Rel4 | Event3 | Person1
    Rel5 | Event3 | Person2
    ...


    Then you use relationships (via JOIN operations) to get the "right" data.
    Me.Geek = True

  8. #8
    Join Date
    Sep 2005
    Posts
    37
    Thanks Nick. I actually did get that far...creating the third table like you suggest, however, I didn't have the relationshipID, just the personID and event ID. I'm not sure that I got them joined right, but I'll work with that and try to fix it. In order to populate each record, I have to put the eventID and the personID. I was hoping to somehow not have to enter the eventID for each record. Is there a way to create a form or query so that the table knows which recordID that I'm entering the personID into? Perhaps the joins will take care of this?

    When I tried to create a test report to see if I could get the data in the third table to show correctly, it displayed it in rows, such as in a subreport. I would like the data in the third table to be all in the same row as the data in the person and event table. Any suggestions would be appreciated. Thanks for your assistance and your patience.

    Bruce

  9. #9
    Join Date
    Sep 2005
    Posts
    37
    oops... meant eventID instead of recordID.

  10. #10
    Join Date
    May 2005
    Posts
    1,191
    How are you currently entering the data? Is it directly into the table? Or is it via a form? If it's just a database that you own and maintain, than I guess I wouldn't advise you not to enter it directly into the table, but just recognize as you have that you then have to enter in everything by hand. That's really where forms shine in my opinion; if time is taken to program the capability they let you validate and sanitize data entry and can automate and expedite data entry (you now see one example of this with having to enter in your data manually every time; not only does this slow you down, but there's also the possibility of you mistyping a character or something).

    As for your second part, can you post a little more with what you already have? We can go from there.
    Me.Geek = True

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    A form/subform with master child links set appropriately will allow you to add records without having to re-enter the event ID.

    To display the records as you describe, you can use this type of thing:

    Modules: Return a concatenated list of sub-record values
    Paul

  12. #12
    Join Date
    Sep 2005
    Posts
    37
    Thanks Nick & Paul. Paul, I'll try the Form/Subform as you suggest. I looked at the link that you suggested, but the code is a little beyond my capabilities. I'll play around with it to see what I come up with.

    Nick, I agree with you on forms vs table entry. I have only entered just a few records, and that has been via table because I haven't been able to get the forms to enter the persons into the event table. Paul's suggestion will probably fix that. What I was trying to say regarding the report is that when I create the report with the junction table, it will list the persons in separate rows than the event data. What I'm trying to accomplish in the report is to have all of the persons associated with the event on the same row. I created a query and had the person field multiple times, hoping to be able to set criteria to accomplish what I'm trying to get. I was able to use the first and last criteria under grouping, but that would only give me the first and last, but not those persons in between. I'm determined to get this working somehow.

    Thank you guys for your help so far.

  13. #13
    Join Date
    Sep 2005
    Posts
    37
    Nick/Paul,

    Thanks for your help. I was finally able to get everything working like I need it to. The concatenated list was the really tricky part. Looked at your link Paul and I wasn't able use it. Looked at lots of other posts about concatenated lists, and the one that helped me was at this link ACC: How to Concatenate a List of Items from a Many-Side Table . You guys really helped out a lot.

    Bruce

Posting Permissions

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