Prev: Standard Ribbon Tabs in Access 2007 Runtime
Next: Email Message Text Going in "TO" Field
From: Sandy on 4 Jul 2008 13:37
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

From: Tom van Stiphout on 4 Jul 2008 13:58
On Fri, 4 Jul 2008 18:37:18 +0100, "Sandy"
<sandy_stephen(a)DELETEhotmail.com> wrote:

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.


>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
From: Tom Wickerath AOS168b AT comcast DOT on 4 Jul 2008 14:04
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
__________________________________________

"Sandy" wrote:

> 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
From: Ken Sheridan on 4 Jul 2008 14:07
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

"Sandy" wrote:

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

From: Tom Wickerath AOS168b AT comcast DOT on 4 Jul 2008 14:19
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" wrote:

> On Fri, 4 Jul 2008 18:37:18 +0100, "Sandy"
> <sandy_stephen(a)DELETEhotmail.com> wrote:
>
> 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.
 |  Next  |  Last
Pages: 1 2
Prev: Standard Ribbon Tabs in Access 2007 Runtime
Next: Email Message Text Going in "TO" Field