I want to add specific tracks to a specific customer

[Guest]

I own a DJ Business, and currently have one table called customers, one
called bookins and one called track list.

I have managed to link the customers and bookings tables together, and now
when i search for a customer number it will show there event bookings.

I want to be able to add a way of also adding the records from the table
"Track List" to a form and report as well.

So in the end i would have:

Enter Customer Numbers --> Shows Bookings --> Select Booking --> Show's
Track List

Also i would obviously need a way to allocate tracks to bookings.


Thanking you all in advance.

 

[Tom Ellison]

Dear Nopex:

It sounds like you have encountered the need for a different type of
relationship.

A "track" could be played at any one of a number of shows. And a show would
have many "tracks", right?

This is a many-to-many relationship, quite unlike the one-to-many
relationships with which you may be more familiar. It requires some
distinct handling.

You must create a table to handle the intersections between all tracks and
all shows. This is sometimes called a Junction Table.

You should already have some unique key for shows and for tracks. The
junction table should contain columns for each of these. In addition, it
could have other information, such as the sequence in which you expect to
play the tracks, an evaluation of whether the crowd enjoyed it or not, or
any other information that is unique to the playing of that track at that
show.

This is a different type of relationship. Once you get a feel for it, your
problems should diminish.

I assume a Show is a pre-arranged play-list that may be re-used at several
bookings. You seem to also be interested in associating such a play-list
with a booking, perhaps without creating a re-usable show for that. You
might even want the ability to copy a show's play-list to a booking's
play-list, and then perhaps modify it. The same sort of structure applies
here as well.

Tom Ellison

 

[tina]

well, one booking may have many tracks selected (obviously), and one track
may be chosen for multiple bookings, so you have a many-to-many relationship
between bookings and tracks. suggest the following tables, as

tblCustomers
CustomerID (primary key)
FirstName
LastName
(other fields that specifically describe a customer)

tblTracks
TrackID (pk)
TrackName
(other fields that specifically describe a track)

tblBookings
BookingID (pk)
CustomerID (foreign key from tblCustomers)
PlayDate
(other fields that specifically describe a booking)

tblBookingTracks
BookingID (foreign key from tblBookings)
TrackID (foreign key from tblTracks)
<use the two fields as a combination primary key, or you can add another
field, probably Autonumber data type, to serve as the primary key>
(other fields that describe a specific track for a specific booking, such as
how frequently to play the track, maybe?)

you can use a form/subform setup to show the bookings for a specific
customer: tblBookings bound to the main form, tblBookingTracks bound to the
subform, with a combo box in the subform (with RowSource based on tblTracks)
to choose each specific track for the booking. that would be a standard
setup, but there are many ways to set up the form(s) to suit your data entry
/ display needs.

hth

 

[tina]

oops, sorry Tom, i didn't see your response post before i answered.

 

[Tom Ellison]

Dear Tina:

I take it then you basically agree. I don't get that often enough. So,
I'll just enjoy. It is considerate to not step on others who have answered
already. Your apology is certainly accepted!

Tom Ellison