Recording date of change in table

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

I am trying to keep this simple!

I have two tables
'RepairProduct' - with the following fields
CatName - text
RepProduct - text
Buy Price - Currency
Sell Price - Currency
Obsolete - Yes/No

and
'Job Details' - with fields
Job ID
RepProduct
Buy Price
Sell Price
etc

I also have a form, 'NewJobs', which has a Sell Price textbox in which the
Sell Price is automatically inserted from the value stored in the
'RepairProduct' table corresponding to the selected RepProduct textbox.
Coding has been used to facilitate this.
When the record is saved the current Sell Price is recorded in the table
'Job Details' - retaining all the earlier Sell Prices - if you see what I
mean.

All of this works fine - however prices change and when the Sell Price is
updated (and indeed the Buy Price) in the 'RepProduct' table I am looking
for a way to record the price change and the date this change was made.

Any thoughts would be most welcome.

Thanks
Sandy
 
On Fri, 4 Jul 2008 18:37:18 +0100, "Sandy"

There seems to be no need. Just change the prices as needed, and the
next time you create a JobDetails record you will automatically use
the new price.

If for some reason you do want to see the history of prices, you can
get that from the JobDetail table. I am assuming the Job table has a
JobDate tying the prices to that date.

-Tom.
 
You'll need to decompose the RepairProduct table by creating a Prices with
columns RepProduct, PriceType, PriceDate and Price. PriceType will have
possible values 'Buy' or 'Sell'. he Buy price and Sell Price columns would
be deleted from RepairProduct.

To get the current sell price you'd look up the row in prices where
RepProduct = the repair product in question and PriceType = 'Sell' and
PriceDate = the latest (Max) PriceDate for the repair product in question
where PriceType = 'Sell'.

Ken Sheridan
Stafford, England
 
Tom,

Using your suggestion would allow one to determine the earliest job date for
each change in price, but you would not be able to tell, with certainty, when
a price change actually went into effect.

Suppose part X has it's price increased in January by 5%, but the next time
this part is used in a job doesn't occur until August 12. Now the boss want's
to know how long ago has the current price been in effect.....


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Correct. The OP wanted to keep it simple, and I did simplify it.
Perhaps beyond the breaking point.

-Tom.
 
Back
Top