Table help

[Polin]

I have two tables, a Performance table and a Ticket table. The Performance table stores information on performances such as the name, time, date, production cost etc. and the Ticket table stores information on the seats such as seat number, ticket cost and whether it has been booked.

There are 160 seats and they are all stored in the Ticket table. For each performance in the Performance table I need to store whether each 160 seats has been booked for that performance.

One solution would be to have a ticket table for each performance, and another would be to have 160 entries in the Ticket table every time I added a performance but I was wondering if there was an easier way to do this.


thanks in advance,

~Polin

 

[jzwp22]

Yes, there is an easier way: a third table that relates the performances and tickets or more correctly the seats. The third table will be the ticket table. See the following 3 tables:

tblPerformances
-pkPerformID primary key, autonumber
-PerformanceTime
-PerformanceDate
-ProductionCost

BTW, the words date and time are reserved words in Access so they should not be used as table or field names. Also, it is best not to have any spaces or special characters in your table or field names.

tblSeat (your old ticket table; this table will have 160 records--one for each seat; no booking information is stored in this table only the information about the seat itself)
-pkSeatID primary key, autonumber
-SeatNumber


tblTickets
-pkTicketID primary key, autonumber
-fkPerformID foreign key relating to tblPerformances
-fkSeatID foreign key relating to tblSeats
-dteBooked (a date field to say when the seat for the particular perfomance was booked)

You would only store the seats that have been booked for a particular performance in the above table. You can run a query against the 160 seats in tblSeats to see which have not been booked & thus are still available

 

[Polin]

Oh, ok this makes sense.

Thank you!

 

[jzwp22]

You're welcome.