Table structure help

[lloyd33]

For those that have helped me in the pass THANKS, this forum has taught be a lot. I have 3 tables, now one contains the software price. The software price will change from time to time. How i have the table structures currently means that the sites that have received this software have the new prices assigned to them. Instead of maintaining the old price.

My first solution was to create a archieve table basically allowing me to keep a record of the old price then i would assign the site cost price etc to the archive table. Or is there another way i can modify the current structure to still save the old price.

Dealer table contains dealer name, they are charged for the software.

 

[The_Doc_Man]

In your price table, have two or three extra fields. Your choice as to details.

1. Date price came into existence.
2. Date price was superceded. Blank for current prices.
3. Yes/no flag : If YES, price is current. (Option, could just use #2="')

When you update a price, just store a new price and modify the entry for the old one. When you query for prices, only include prices with blank for supercede date (or YES for "price is current" flag.) Like I said, your call.

Now, when doing historical analysis, you can cross-match a transaction to the price in force on that date. Hint: Might need UNION query, look it up in the Help files, to show (a) transaction date BETWEEN ExistDate AND SupercedeDate UNION (b) transaction after Max(ExistDate) for which SupercedeDate = null or blank or whatever.

 

[neileg]

I almost agree with Doc Man, but not quite. Where you are creating records that include the price, you should store the price ruling on the relevant date in the record. I know this breaks the general rule that you should not store what you can calculate, but if you are going to have to calculate the price every time you want a transaction history, this overhead will become huge. The vailidity of your stored data will also depend on you never deleting any records from the price history table.

Because of this, I would include in your price table only one extra field, the date from which the price applies. Then you can look up your price using MAX() on the date field and this will return the latest price.

 

[lloyd33]

thank

Thanks guys for both your help on this subject. My head is no longer boiling.

 

[lloyd33]

max date

I almost agree with Doc Man, but not quite. Where you are creating records that include the price, you should store the price ruling on the relevant date in the record. I know this breaks the general rule that you should not store what you can calculate, but if you are going to have to calculate the price every time you want a transaction history, this overhead will become huge. The vailidity of your stored data will also depend on you never deleting any records from the price history table.

Because of this, I would include in your price table only one extra field, the date from which the price applies. Then you can look up your price using MAX() on the date field and this will return the latest price.

I have tried this in the qbe but i always get all the records when i select max on the date field. What am i doing wrong?

 

[lloyd33]

grateful

thanks for you're time and help.