[yashman]

Hi,

I am finding difficulty in one of the script in MsAccess. I have

- Venue Table like XYZ Auditorium
- Floor Table - Balcony
- Floor Seat table like A, B
- Seat Number table with values like 01, 02
- Category table like CAT A with category price.
- Category Seat Relation table which links the Category table and Floor Seat Table (above)

- Event table which has a Foreign Key of Venue table
- Temp_Cart table which stores the Category ID, NumOfTickets etc.
- Temp_Cart_Seats with FK_Temp_CartID and the Seat Number Table ID. This table is used when the customer purchases the ticket for event. The table stores Seat Number IDs which the customer has booked for the event.

Now the issue is that I have 2 Events hosted in the same venue at different dates with same seating arrangement but definitely Category per event will vary. Let's assume

Customer A booked 5 seats for Event A for CAT A each ticket then the values in the tables.
CAT A price of $100 each applies to seats from A01 to A10.

# Temp_Cart
- PK_TempCartID = 1
- CategoryID = 1
- NoOfTickets = 5

# Temp_Cart_Seats
- PK_TempCartSeatsID = 1
- FK_TempCartID = 1
- FK_SeatNumberID = 1

- PK_TempCartSeatsID = 2
- FK_TempCartID = 1
- FK_SeatNumberID = 2

- PK_TempCartSeatsID = 3
- FK_TempCartID = 1
- FK_SeatNumberID = 3

- PK_TempCartSeatsID = 4
- FK_TempCartID = 1
- FK_SeatNumberID = 4

- PK_TempCartSeatsID = 5
- FK_TempCartID = 1
- FK_SeatNumberID = 5

For the first event, since first customer has booked 5 tickets, then the balance ticket count shown to next customer will be 5 which is fine. But for the Next Event which may happen in future date in the same venue shows only 5 instead of showing total of 10 as for this show none of the tickets have been booked in system.

Can anyone help please on this as it is urgent.