Ez Way to modify pricing (Access 2002, SR 1)

Topic

New Reply

The database I am working on needs to store a set of information (Who ordered a souvenir, how many and total cost). The problem I’m having is that every year, the souvenir changes and the price change as well. To compound the matters, we offer only one souvenir a year.

I’ve set up a table that stores participant ID, Qty. of souvenir and total cost. However, where do I store the price so that I can set the Total Cost to automatically calculate total cost based on price * qty and then store it in the Total Cost field and be “accessible” to the end user to change the pricing each year? (I certainly don’t want to save it in VBA/Default field, etc.)

TIA.

Reply | Quote

Replies

It seems to me that you should have a master Souvenir table, with fields something like SouvenirID, Description, and Price. This is where you lookup the price.

Reply | Quote

That was my first thought but I was thinking, if the souvenir kept on changing each year, I wouldn’t need to store previous souvenirs, that would mean a table for single souvenir? Also, if we only offer one souvenir, how could we tell database to look at the most recent souvenir being offered?

Reply | Quote

I personally don’t think you should change the souvenir data each year. I think you should have a data record for a souvenir for each year. That is, the “2001 Souvenir”, the “2002 Souvenir”, etc. You could also add another field in your Souvenir table specifying the SouvenirYear.

Reply | Quote