I am working on simple database for selling tickets to an event. I would like it to lookup the price of the ticket based on Ticket level (lower, upper etc) and the date sold.
I have a tickets table that has Ticket ID, Ticket Date sold, Ticket level, Qty and How paid
I have a ticket price table that has Ticket level, Date and Price. I have entered every possible level, date and price combination as separate records.
I have setup a relationship with the ticket and price tables:
Tickets:Ticket Type = Price:Ticket Type
Tickets:Date Sold = Price: Date
I have the Price records sorted by descending Date
For some reason the price for the earliest (and cheapest) price keeps coming up, instead of the last weeks (& more expensive price). I set my test up with this week, last week and the week before that dates. I would also prefer to not enter every single day of the 3 week sales period, generally there are 3 prices changing weekly.
If anyone can give me an idea or direction to go in, that would be awesome.