Create a 'basket' function for buying tickets

[shaunacol]

I am a bit rusty at Access and am trying to think of the best way to create a way to order clients tickets, any feedback on my thoughts is appreciated:


Here are the tables I have ATTRACTIONS (lists all attractions you can buy tickets for) ORDER_DETAILS (Order_detailID, ticket type, number of tickets, price) ORDER (orderID, total cost, deposit required etc) and finally CLIENT (clientID, name etc)

So, firstly I am thinking the attraction needs to be chosen. I have a search form where this can be done. Once chosen I can bring up a form with that attraction and the order details in a subform, allowing my to enter new order details for that attraction. Now here is my first problem. I also have a PRICE table in my database listing all ticket type (adults etc) for each attraction and the actual current price. When the person chooses the ticket type on my form (i.e. adult) I would like to have the price field in the Order_Detail table automatically populated. The calculation would be "look at PRICE table and find a match where AttractionID and ticket type are equal to the one on the subform THEN multiple it by the 'number of tickets field' in the form. I hope that makes sense.

Am I making this too complicated? Because I realise it means that if there are multiple attractions on an order then they would have to go back and find another attraction meaning I somehow have to store the OrderDetail so it is the same. Once they have added all tickets for all attractions I somehow have to allocated the next OrderID in table order and populate the total cost and deposit required.




Am i going down the right lines here? Thanks in anticipation!

[pbaldy]

I'm not clear on what the question is, but is this appropriate (this would be a situation for the second method):

BaldyWeb - Autofill

[Micron]

This sort of thing is done all the time. Those tables / your tables would be much like:
a PO header (main order info/customer info). These might also be tied to supplier/customer tables.
PO line item (Parts Catalogue id/attraction id x qty) - one record for each part/attraction on that PO/order
and a Parts Calalogue (bolts, nuts/concert tickets, circus tickets) with item id's (linked to PO line) and unit pricing.

[shaunacol]

Thanks, I guess I was asking 2 things, first is am I doing this the right way or is there an easier one and the second is how I can populate the price field. I think you have def answered my second one with your link, I will try a select statement (im not great at them but can surley figure it out). Thank you!

[pbaldy]

No problem! I certainly think the correct design is an order header table and an order detail table (along with customers and attractions, attractions essentially being a products table).

[shaunacol]

I don't think I should be linking my pricing table with my order line table though as a price is for an attractions so the relationship should be there shouldn't it? And then the order line table also links to attraction. I guess I could try and pull thee price through but I would get multiple entries due to the relationship I think. maybe I have done the relationships wrong?

[pbaldy]

Don't know your actual design, but I wouldn't link for price, as price would change over time. I'd store price in the details table, as you showed above.