Recording date of change in table


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
 
Ad

Advertisements

T

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.
 
K

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
 
T

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
__________________________________________
 
T

Tom van Stiphout

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

-Tom.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top