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?
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:
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).
Thanks guys. That helps, but let me explain a little more clearly what I'm trying to do.
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?
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:
EventID (autonumber) 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 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.
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.
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.
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.