Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 12-10-2003, 02:10 PM   #1
TVReplay99
Registered User
 
Join Date: Jan 2002
Location: St Louis MO USA
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
TVReplay99
Udate new Price from Order

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

TVReplay99 is offline   Reply With Quote
Old 12-11-2003, 03:52 AM   #2
Len Boorman
Back in gainfull employme
 
Join Date: Mar 2000
Location: Coventry West Midlands UK
Posts: 1,930
Thanks: 1
Thanked 1 Time in 1 Post
Len Boorman will become famous soon enough
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
Len Boorman is offline   Reply With Quote
Old 12-11-2003, 09:50 AM   #3
TVReplay99
Registered User
 
Join Date: Jan 2002
Location: St Louis MO USA
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
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

TVReplay99 is offline   Reply With Quote
Old 12-12-2003, 01:05 AM   #4
Len Boorman
Back in gainfull employme
 
Join Date: Mar 2000
Location: Coventry West Midlands UK
Posts: 1,930
Thanks: 1
Thanked 1 Time in 1 Post
Len Boorman will become famous soon enough
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
Len Boorman is offline   Reply With Quote
Old 12-12-2003, 06:33 AM   #5
neileg
AWF VIP
 
neileg's Avatar
 
Join Date: Dec 2002
Location: Newcastle, England
Posts: 5,975
Thanks: 0
Thanked 8 Times in 8 Posts
neileg has a spectacular aura about neileg has a spectacular aura about
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?
__________________
You can't always get what you want, but if you try sometimes you may just get what you need - Rolling Stones

Cheers, Neil
neileg is offline   Reply With Quote
Old 12-12-2003, 09:31 AM   #6
TVReplay99
Registered User
 
Join Date: Jan 2002
Location: St Louis MO USA
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
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
TVReplay99 is offline   Reply With Quote
Old 12-12-2003, 11:47 AM   #7
Len Boorman
Back in gainfull employme
 
Join Date: Mar 2000
Location: Coventry West Midlands UK
Posts: 1,930
Thanks: 1
Thanked 1 Time in 1 Post
Len Boorman will become famous soon enough
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


Len Boorman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 11:03 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World