Many to Many?

[ThomasK via AccessMonster.com]

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

 

[Jeff Boyce]

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

 

[Guest]

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.

 

[ThomasK via AccessMonster.com]

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

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

 

[Stephen Glynn]

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]

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

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

 

[Lynn Trapp]

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/conradsystems/accessjunkie.html

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

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

 

[Stephen Glynn]

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
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

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

 

[ThomasK via AccessMonster.com]

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

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

 

[Jeff Boyce]

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>

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

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

 

[ThomasK via AccessMonster.com]

Thank you,

Your right this is not simple. I've decided to ask everyone to only have one
event one time and never again. Just to keep my data organized. ;-)

Thanks for your help

Tom

 

[Jeff Boyce]

Dang!

Your customers will do that for you?!

Jeff

 

[Stephen Glynn]

It is simple, though, once you've got the tables properly set up. Then
you can forget all about what's supposed to go where and use queries
based on the tables, and forms based on the queries, to enter and
display exactly the data you need.

Steve