Many to Many relationships

[Jane]

Hello

Following some advice from Mr John Vinson who said I should not have the
same data in different tables I have redesigned my tables and relationships
in a Many to Many link.

But I am lost on where to go from here. I have tried to use the wizard to
create some queries and forms but nothing seems to give me the UI I need.

I have

tableBusses
BusID
BusDeparturePoint
BusDepartureTime
etc

tableStudents
StudentID
Student1stName
StudentSurname
etc

I have created a linking table like this
BusBookingID
StudentID
BusID

The problem is that students have one BusBookingID for each outing from
school but this may include a number of busses
Eg.
Bus1 will take them to the outing
Bus2 will bring them back to school
Bus3 will take them home (if after school hours)
etc

and, of course many students with different BusBookingID's may board each bus.

After looking on this forum I see this is a many to many relationship (I
think)

I am lost with this

I would like to be able to load the bus details in the system on a form.
On another form see a list of students and choose the bus(es) they will
board and this will automatically give them a BusBookingID

You can think of a BusBookingID as some form of ticket number that would
include a number of routes.

Can anyone offer some advice as to where to go from here.

Thank you

Jane Hollin

 

[Bob Quintal]

See comments inline.

Hello

Following some advice from Mr John Vinson who said I should not
have the same data in different tables I have redesigned my tables
and relationships in a Many to Many link.

But I am lost on where to go from here. I have tried to use the
wizard to create some queries and forms but nothing seems to give
me the UI I need.

I have

tableBusses
BusID
BusDeparturePoint
BusDepartureTime
etc

tableStudents
StudentID
Student1stName
StudentSurname
etc

I have created a linking table like this
BusBookingID
StudentID
BusID

The problem is that students have one BusBookingID for each outing
from school but this may include a number of busses
Eg.
Bus1 will take them to the outing
Bus2 will bring them back to school
Bus3 will take them home (if after school hours)
etc

and, of course many students with different BusBookingID's may
board each bus.

After looking on this forum I see this is a many to many
relationship (I think)

actually, it's many to many to many to many.
You will need, in addition to the students table, imo a table for
each outing, that defines that entity.

Then you need a one to many relationship into a bus-outing table.
with Outing number and bus number 1,2,3... which may repeat for each
outing number.

then you need a a one to many relationship from the bus-outing table
to an outing-students table, The outing-students table is also
related in a many to one relationship whth the students table.

 

[Jane]

Hi Bob

I think I will need to go back to my access book and see what I can do with
this.

Thank you for your advice.

Jane

 

[Fred]

Jane,

Here's a few thoughts that might help sort it out.

First you have to figure out/decide exactly what entities you want/need to
database.

You should clarify to yourself that your first table is not really a Bus
table, it is a BusRuns table. You probably don't even need a bus table,
buses will probably be just a field in your BusRuns table, as you have
already done.

It appears that an "Outing" is somewhat synomous with a Booking", or at
least that there is a one-to-one relationship between (recorded) outing and
bookings. If so, "Outing" would be merely an attribute/piece of information
about a Booking.

Next you are going to have to answer / clarify to yourself (and/or us)
whether or not a particular BusRun can be used for more than one booking.
The answer to this question will fundamentally affect your table structure.

Then you should repost with your answers. Reading the book is also good
and needed, but this would be a good one to also let us help you on.