Table structure - not duplicating records

[audrey]

Here's another one I'm stumped on. What would be the best way to do this... if it can be done:

The tables in question are the following:

1. Exhibitors (stores exhibitor data, i.e. name, age, address, etc)
2. Livestock (stores livestock data for each exhibitor, i.e. breed, weight, gender)
3. Shows (stores data about each show, i.e. title, date, location)

I would like to have the Shows table to also store which exhibitors and livestock participated at this show. For example:

County Show: Jane Doe (exhibitor) - blackie, whitie (livestock)
John Smith - spot, blue, cutie-pie
James Black - smokey, sassy, fluffy

State Show: Jane Doe - blackie
James Black - spot, blue, cutie-pie, snuffles
Christy Meek - baby, apples

The problem is that each show will be associated with multiple exhibitors (and their different livestock). AND each exhibitor (and their livestock) will be associated with different and often times multiple shows.

So what would be the best way to have the Shows table pull the exhibitor and livestock data without having to duplicate exhibitor records for each show?

 

[RuralGuy]

I believe you want a 4th table that ties livestock entered in shows. You can use a query to get the owner of the livestock.

 

[audrey]

Let me make sure I have this straight in my mind before I dive off into it

The 4th table, say tbl_tie_show, would have 2 fields: Show ID and Livestock ID. When a user enters an exhibitor and their livestock, on the entry form, they would select which show to associate this animal to (I could possibly create a combo and set the default value to the current show being used). Then when the user adds the exhibitor/livestock, I could create a query that would also add that livestock and Show ID to the tbl_tie_show.

*edited here for new thought ** Here's a problem... If the user wanted to select an exhibitor from a previous show (say you do a find record to find that exhibitor), then the user updates that exhibitor/livestock record to the current show, then wouldn't that overwrite the association with the previous show? I'm getting myself confused, lol.

Then to recall a show, I select from tbl_tie_show livestock where Show ID = the current show being recalled. Then of course, my established relationship between Exhibitor ID and Livestock ID would pull the Exhibitor associated with each animal.

Would this work? Is this what you were talking about?

Thanks for the response ~ Audrey

 

[RuralGuy]

Understanding that I haven't actually tried this, your description is *exactly* what I had in mind.

 

[audrey]

Thanks for getting me started in the right direction. I'm going to play around with this for a bit and if I get hung-up... i'll definitely be back!

Thanks again

 

[RuralGuy]

Glad I could assist. There are tons of people here who will be glad to assist if you run into some trouble. If I'm right, all of the entry can be done with the query of the entry form. All of the tables can be joined and you should still have an updateable query that you just select the Foreign keys to fill in all of the data. You'll find out when you get started. Have fun.

 

[KKilfoil]

*edited here for new thought ** Here's a problem... If the user wanted to select an exhibitor from a previous show (say you do a find record to find that exhibitor), then the user updates that exhibitor/livestock record to the current show, then wouldn't that overwrite the association with the previous show? I'm getting myself confused, lol.

First of all, you shouldn't add text into your posts once another has posted to the thread, as your revisions are usually overlooked. It's better to post again with followup questions such as this.

To answer your concern, you would simply add a new record in your link table for each instance where an exhibitor attends a show. So rather than overwriting the data in the old record, you would have a new record, with the SAME exhibitor, but a different show.

 

[RuralGuy]

KKilfoil is correct in that I missed that edit. The answer given is also correct. Your "tbl_tie_show" table will have a separate record for *every* entry in *every* event or show.