Recording date of change in table

[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

 

[Tom van Stiphout]

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.

 

[Tom Wickerath]

Hi Sandy,

Check out this article, from Access MVP Allen Browne:

Creating an Audit Log
http://allenbrowne.com/AppAudit.html

This will allow you to keep an entire history of all price changes.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Ken Sheridan]

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 Wickerath]

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
__________________________________________

 

[Tom van Stiphout]

Correct. The OP wanted to keep it simple, and I did simplify it.
Perhaps beyond the breaking point.

-Tom.

 

[Sandy]

Thanks very much all of you - plenty food for thought!
Sandy