Update New Price From Order

[TVReplay99]

I am creating a fairly simple order and receiving DB. Before I begin creatind the order forms, I need to get my mind around one area. How do I update the Cost & SellPrice Field in my Stock Table from the order form when there is a change?

Ex: Hammer current Cost is $7.00
Sell hammer @$7.70 with 10% mark up(mark up field is in stock Table)

Order for Hamer now Costs $8

How do I write that to the Stock table as new Cost & Sell price? Or If I save the Cost and the Markup % should I even save the Sell Price?

Any advice or redirection in my thinking would be helpful.

Thanks to all.

Brian

[Len Boorman]

If your sell price always results from the markup of a buy price then you should not store the sell price since it is derived data.

This then means that you only need to watch the buy price.

Just thinking now

If you generate buy orders from your application then you can put a purchase price on your order.

Then when invoice received you could enter invoice details against the order details and check for price changes. (Invoice Matching routine)

You could calculate the price difference and then add this difference to the buy price record. You would need to flag that price update routine had been run against the invoice otherwise each time you ran the rountine you would add any price diferences

Just a thought

Len

[TVReplay99]

Len,
I assume I would do that with a update query using current record StockNO as Criteria and then what? I don't have any experience with update queries much less using form date to do it. Any advice wuold be heplful.

Off to trial and alot of error.

Brian

[Len Boorman]

Brian
Basically the process I imagine is

Open form showing outstanding purchase orders
Add Invoice data

You then need a process to actually do price updates. This could be an action that you carry out say monthly or whatever period you want, even whenever an invoice is received.

Maybe on a suitable form you have button that say "Update Costs"
the on click event is a bunch of queries that in sequence

Clear all records from Temp table

Select all Item, invoice prices and cost prices when update flag is false. (remember I mentioned this flag earlier) and calculate the difference.

Use the results of this query to append the date to a temp table

Use this temp table linked to your main table to update costs to ( Main table cost) plus (Temp table cost difference)

Set update flag to true

Now that is a suggested basic sequence but there are a couple of points to watch out for.
If you have two invoices for the same item both with price changes you will perhaps have to query the temp table to establish the greatest before doing the update

Sound like the learning curve is a bit seep at the moment.
Remember they climbed Everest simply one step at a time

Len

[neileg]

If you are using the latest purchase invoice price as your 'cost', I would run a query that found the latest date for that item using Max on the date field. This will give you the relevant price.

However there is an accounting (not a programming) dilemma here. Suppose you get a bulk price on your hammers and order 100 at $5 a piece. You then buy an extra one hammer and this costs you $8. All of the hammers that cost $5 each are now being costed at $8. That's a difference of $300 on the stock. Are you happy with this?

[TVReplay99]

Neileg and Ken,
Thanks for the input. Neileg, interesting delima accounting wise. What I imagine is the Cost update to happen when I recieve the item. So I guess what I need is an event On Update of the Recieved field in my Order Details to check if the Cost in tblOrderDetails =Cost in tblStock.

I have tried this but to no avail using If...Then and DLookup.

I have not tried a Temp Table and not quite sure how to approach that.

Just a thought is their a way to have both Cost Fields (tblStock and tblOrderDetails) bound on the form with one hidden and if one is changed the other is also so that way the data is written directly to the tables?

Thanks for the thoughts, I very much appreciate both of your help.

Brian

[Len Boorman]

Just comment on Temp Table

This is an ordinary table. It is just used as a temporary repository for some data to be further analysed

Len B

Added comment.
Thanks to Neileg for his additions. I am not an accountant so he is very correct in pointing this out. Suggest to run any proposals past your own accountants.

ljb