If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Many to Many?
I have a database that tracks trade shows. I've set up 2 tables, one is
tblVenues, one is tblEventYear. Right now I have the two tables linked by the field EventYear. EventYear is the primary key in tblEventYear and the foreign key in tblVenues. I have a one to many relationship with tblEventYear being on the one side. One event year with many venues. The problem I'm having is that one venue can have a show at the same place for several years. So, many venues can have events on many different years. Should this be a many to many relationship? If so, should I have a connecting table, and what would it be? I would like to be able to keep track of the venue information over several years, and be able to see what information has changed each year. But I dont want to have to re-input all the venues information for each new year. Thanks for the help. Tom -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200602/1 |
#2
|
|||
|
|||
Many to Many?
Thomas
Each year can have many trade shows, each trade show can occur in multiple years (each year is a separate instance). I'm not sure you need a "year" table, since that seems like an attribute of an instance of a trade show. You may have already incorporated this via your EventYear table, but I couldn't tell from your description. Each trade show (e.g., Seattle Boat Show; COMDEX; NRA Annual Conference; ....) can be recorded once, in your TradeShow table. Each instance (e.g., the 2005 Seattle Boat Show) can be recorded in a ?Event table, which would have fields like (guessing here, you're the expert): tblEvent EventID TradeShowID EventYear Venue MaxCapacity ... Good luck! Jeff Boyce Microsoft Office/Access MVP Microsoft IT Academy Program Mentor "ThomasK via AccessMonster.com" u16264@uwe wrote in message news:5bf8136ad82a0@uwe... I have a database that tracks trade shows. I've set up 2 tables, one is tblVenues, one is tblEventYear. Right now I have the two tables linked by the field EventYear. EventYear is the primary key in tblEventYear and the foreign key in tblVenues. I have a one to many relationship with tblEventYear being on the one side. One event year with many venues. The problem I'm having is that one venue can have a show at the same place for several years. So, many venues can have events on many different years. Should this be a many to many relationship? If so, should I have a connecting table, and what would it be? I would like to be able to keep track of the venue information over several years, and be able to see what information has changed each year. But I dont want to have to re-input all the venues information for each new year. Thanks for the help. Tom -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200602/1 |
#3
|
|||
|
|||
Many to Many?
Hi Tom,
Yes you need a connecting table. It should have foreign keys to both tables plus its own primary key. Also the combination of the two foreign key fields should be a unique index to keep duplicates out. I usually populate such a table with a form that has two combo boxes. The Row Source for these combo boxes is information from the tables. For example EventYear show up in one combo box. Hopefully you are using something like an autonumber as the PK in the tblVenues as venues tend to change names. In this case you would make the autonumber the bound field; however, the column width as 0. Make the column count 2 and also include in the venue name. With a column widths something like 0;2" you won't see the PK, but instead the venue name. -- Jerry Whittle Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "ThomasK via AccessMonster.com" wrote: I have a database that tracks trade shows. I've set up 2 tables, one is tblVenues, one is tblEventYear. Right now I have the two tables linked by the field EventYear. EventYear is the primary key in tblEventYear and the foreign key in tblVenues. I have a one to many relationship with tblEventYear being on the one side. One event year with many venues. The problem I'm having is that one venue can have a show at the same place for several years. So, many venues can have events on many different years. Should this be a many to many relationship? If so, should I have a connecting table, and what would it be? I would like to be able to keep track of the venue information over several years, and be able to see what information has changed each year. But I dont want to have to re-input all the venues information for each new year. Thanks for the help. Tom -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200602/1 |
#4
|
|||
|
|||
Many to Many?
Thanks for the reply,
These are my tables: tblVenues VenueID primary key EventYear foreign key ShowName ect.. tblEventYear EventYear primary key I have put in all the info into the tblvenues for this year. Some of this info will be the same over several years. I would just like to carry over the event info that is the same every year and have a picture of what the venue info was in past years. I would like to not have to reinput the event info each year. What is the best way to handle this? Thanks Jerry Whittle wrote: Hi Tom, Yes you need a connecting table. It should have foreign keys to both tables plus its own primary key. Also the combination of the two foreign key fields should be a unique index to keep duplicates out. I usually populate such a table with a form that has two combo boxes. The Row Source for these combo boxes is information from the tables. For example EventYear show up in one combo box. Hopefully you are using something like an autonumber as the PK in the tblVenues as venues tend to change names. In this case you would make the autonumber the bound field; however, the column width as 0. Make the column count 2 and also include in the venue name. With a column widths something like 0;2" you won't see the PK, but instead the venue name. I have a database that tracks trade shows. I've set up 2 tables, one is tblVenues, one is tblEventYear. Right now I have the two tables linked by the [quoted text clipped - 11 lines] Tom -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200602/1 |
#5
|
|||
|
|||
Many to Many?
I'm not completely sure what you're trying to model, but ISTM that you
probably need two tables, but set up slightly differently: tblVenues VenueID VenueName (and other information specific to the venue -- capacity, etc) tblEvent EventID VenueID EventName EventYear (and other information specific to the event -- registration fee, etc) and then set the relationships in queries, depending on what information you want. If you want to know in which venues a particular event has been held in past years, then you want a query with a one-to-many relationship between [tblEvent].[VenueID] and [tblVenues].[VenueID] (show matching entries in tblVenue where EventName = "Eurovision Song Contest"); if you want to know what's happened in a particular venue over the years, you want a separate query with a one-to-many relationship between [tblVenues].[VenueID] and [tblEvent].[VenueID] (show matching entries in tblEvent where Venue = "Paris"). Steve ThomasK via AccessMonster.com wrote: I have a database that tracks trade shows. I've set up 2 tables, one is tblVenues, one is tblEventYear. Right now I have the two tables linked by the field EventYear. EventYear is the primary key in tblEventYear and the foreign key in tblVenues. I have a one to many relationship with tblEventYear being on the one side. One event year with many venues. The problem I'm having is that one venue can have a show at the same place for several years. So, many venues can have events on many different years. Should this be a many to many relationship? If so, should I have a connecting table, and what would it be? I would like to be able to keep track of the venue information over several years, and be able to see what information has changed each year. But I dont want to have to re-input all the venues information for each new year. Thanks for the help. Tom |
#6
|
|||
|
|||
Many to Many?
The venue and show name are covered in the venues table. The problem is that
I need to enter the information for next years shows. So the EventYear field will be different in the venues table but much of the other information will be the same. I dont want to put the same information in the table for each venue and just change the year field. I'm sorry, this is hard for me to explain. Tom Stephen Glynn wrote: I'm not completely sure what you're trying to model, but ISTM that you probably need two tables, but set up slightly differently: tblVenues VenueID VenueName (and other information specific to the venue -- capacity, etc) tblEvent EventID VenueID EventName EventYear (and other information specific to the event -- registration fee, etc) and then set the relationships in queries, depending on what information you want. If you want to know in which venues a particular event has been held in past years, then you want a query with a one-to-many relationship between [tblEvent].[VenueID] and [tblVenues].[VenueID] (show matching entries in tblVenue where EventName = "Eurovision Song Contest"); if you want to know what's happened in a particular venue over the years, you want a separate query with a one-to-many relationship between [tblVenues].[VenueID] and [tblEvent].[VenueID] (show matching entries in tblEvent where Venue = "Paris"). Steve I have a database that tracks trade shows. I've set up 2 tables, one is tblVenues, one is tblEventYear. Right now I have the two tables linked by the [quoted text clipped - 11 lines] Tom -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200602/1 |
#7
|
|||
|
|||
Many to Many?
Thomas,
I think you need to back up and take a relook at your data design. What are you really trying to track -- Venues or Events? In most cases that I have seen people want to track Events, which take place in some venue (a place) and the venue then would be an attribute of a given instance of an event. I would suggest that you consider looking at Jeff Boyce's approach. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Access Junkie List: http://home.bendbroadband.com/conrad...essjunkie.html "ThomasK via AccessMonster.com" u16264@uwe wrote in message news:5bf8bc56a0b58@uwe... Thanks for the reply, These are my tables: tblVenues VenueID primary key EventYear foreign key ShowName ect.. tblEventYear EventYear primary key I have put in all the info into the tblvenues for this year. Some of this info will be the same over several years. I would just like to carry over the event info that is the same every year and have a picture of what the venue info was in past years. I would like to not have to reinput the event info each year. What is the best way to handle this? Thanks Jerry Whittle wrote: Hi Tom, Yes you need a connecting table. It should have foreign keys to both tables plus its own primary key. Also the combination of the two foreign key fields should be a unique index to keep duplicates out. I usually populate such a table with a form that has two combo boxes. The Row Source for these combo boxes is information from the tables. For example EventYear show up in one combo box. Hopefully you are using something like an autonumber as the PK in the tblVenues as venues tend to change names. In this case you would make the autonumber the bound field; however, the column width as 0. Make the column count 2 and also include in the venue name. With a column widths something like 0;2" you won't see the PK, but instead the venue name. I have a database that tracks trade shows. I've set up 2 tables, one is tblVenues, one is tblEventYear. Right now I have the two tables linked by the [quoted text clipped - 11 lines] Tom -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200602/1 |
#8
|
|||
|
|||
Many to Many?
You certainly don't want to be putting the same information in the table
repeatedly. If you're doing that, there's something wrong with the structure of the database. Are you saying that you've got several annual events taking place in various venues, that an individual event always takes place at the same venue each year, and that each individual venue will host several different events annually? That is, one event will take place in one venue many times over the years and that one venue will accommodate many different events each year? In that case, I think you need three tables, one for events, one for venues, and one for years. Keep the details that don't change for the venue in the venues table (e.g. address, capacity), the ones for the event in the events table (name of event, organisation that organises it...) and the details that change each year in the years table. Then you'll generally need a One-to-Many join between Venues and Events (many events take place in one venue) and One-to-Many join between Events and Years (one Event takes place in many years). Steve ThomasK via AccessMonster.com wrote: The venue and show name are covered in the venues table. The problem is that I need to enter the information for next years shows. So the EventYear field will be different in the venues table but much of the other information will be the same. I dont want to put the same information in the table for each venue and just change the year field. I'm sorry, this is hard for me to explain. Tom Stephen Glynn wrote: I'm not completely sure what you're trying to model, but ISTM that you probably need two tables, but set up slightly differently: tblVenues VenueID VenueName (and other information specific to the venue -- capacity, etc) tblEvent EventID VenueID EventName EventYear (and other information specific to the event -- registration fee, etc) and then set the relationships in queries, depending on what information you want. If you want to know in which venues a particular event has been held in past years, then you want a query with a one-to-many relationship between [tblEvent].[VenueID] and [tblVenues].[VenueID] (show matching entries in tblVenue where EventName = "Eurovision Song Contest"); if you want to know what's happened in a particular venue over the years, you want a separate query with a one-to-many relationship between [tblVenues].[VenueID] and [tblEvent].[VenueID] (show matching entries in tblEvent where Venue = "Paris"). Steve I have a database that tracks trade shows. I've set up 2 tables, one is tblVenues, one is tblEventYear. Right now I have the two tables linked by the [quoted text clipped - 11 lines] Tom |
#9
|
|||
|
|||
Many to Many?
One event takes place at the same time in several venues each year. This
event takes place every year. So, there are many venues every year, and each year there will be several venues to keep track of. Sometimes information about the venues (ie number of tents setup) will change from year to year. Some information about the venue will allways be the same (ie address). I need to track all the information about an event that happened on a particular year. I don't want to just go back into a record and change the year and other info that changed since last year. If I did that then I could not go back and see what happened in a particular year. I also dont want to repete information in my venue table while only changing the year and a few other small details that had changed since last year. I think that this is most likely a very simple problem with me understanding relationships. Is there a way to do this? Thanks for the help Tom Stephen Glynn wrote: You certainly don't want to be putting the same information in the table repeatedly. If you're doing that, there's something wrong with the structure of the database. Are you saying that you've got several annual events taking place in various venues, that an individual event always takes place at the same venue each year, and that each individual venue will host several different events annually? That is, one event will take place in one venue many times over the years and that one venue will accommodate many different events each year? In that case, I think you need three tables, one for events, one for venues, and one for years. Keep the details that don't change for the venue in the venues table (e.g. address, capacity), the ones for the event in the events table (name of event, organisation that organises it...) and the details that change each year in the years table. Then you'll generally need a One-to-Many join between Venues and Events (many events take place in one venue) and One-to-Many join between Events and Years (one Event takes place in many years). Steve The venue and show name are covered in the venues table. The problem is that I need to enter the information for next years shows. So the EventYear field [quoted text clipped - 37 lines] [quoted text clipped - 11 lines] Tom -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200602/1 |
#10
|
|||
|
|||
Many to Many?
Thomas
I get the impression you'd like this to be very easy. If it were, you'd already have it solved! I'll try paraphrasing: An event (instance) can happen multiple times in any given year. (either simultaneously or consecutively) An event can happen in more than one year. An event (instance) happens in a venue. An event can happen in more than one venue in any given year. A venue can "host" more than one event (instance) in any given year. So it seems to me that you have Events, Venues and EventInstances. This is a revision from my earlier response. Facts about Events that don't change (e.g., the event title) go in the tblEvent. Facts about Venues that don't change (e.g., the address) go in the tblVenue. Facts about an instance of an event at a venue (e.g., # of tents) go in the trelEventInstance. You would also find date, time, maxcapacity, etc. in the trelEventInstance. HOWEVER! ... if you are having multiple sessions/speakers/etc. at the event instance, you need another table that holds something like: trelSession SessionID (PK) EventInstanceID (a FK from the trelEventInstance table) SessionTitle SessionRoom SessionCapacity SessionSpeaker ... (and it gets more complex if your session can have multiple speakers!). Maybe it isn't simple to do because the real world you are modeling isn't simple... Regards Jeff Boyce Office/Access MVP "ThomasK via AccessMonster.com" u16264@uwe wrote in message news:5bfc1e7c0e484@uwe... One event takes place at the same time in several venues each year. This event takes place every year. So, there are many venues every year, and each year there will be several venues to keep track of. Sometimes information about the venues (ie number of tents setup) will change from year to year. Some information about the venue will allways be the same (ie address). I need to track all the information about an event that happened on a particular year. I don't want to just go back into a record and change the year and other info that changed since last year. If I did that then I could not go back and see what happened in a particular year. I also dont want to repete information in my venue table while only changing the year and a few other small details that had changed since last year. I think that this is most likely a very simple problem with me understanding relationships. Is there a way to do this? Thanks for the help Tom Stephen Glynn wrote: You certainly don't want to be putting the same information in the table repeatedly. If you're doing that, there's something wrong with the structure of the database. Are you saying that you've got several annual events taking place in various venues, that an individual event always takes place at the same venue each year, and that each individual venue will host several different events annually? That is, one event will take place in one venue many times over the years and that one venue will accommodate many different events each year? In that case, I think you need three tables, one for events, one for venues, and one for years. Keep the details that don't change for the venue in the venues table (e.g. address, capacity), the ones for the event in the events table (name of event, organisation that organises it...) and the details that change each year in the years table. Then you'll generally need a One-to-Many join between Venues and Events (many events take place in one venue) and One-to-Many join between Events and Years (one Event takes place in many years). Steve The venue and show name are covered in the venues table. The problem is that I need to enter the information for next years shows. So the EventYear field [quoted text clipped - 37 lines] [quoted text clipped - 11 lines] Tom -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200602/1 |
|
Thread Tools | |
Display Modes | |
|
|