Solved

Purchase Order Price with the Microsoft Northwind Database

Posted on 2016-09-14
22
42 Views
Last Modified: 2016-09-16
Maybe I am missing something here, but when I create a new purchase order in the Northwind database, and select a product the unit price is generated from the products table and does not update back to the products table if it is different?

When a vendor has an increase, I’d like to track how much the increase was from the previous is for reporting purposes.

Are there suggestions do you have to track historical data?
You can get a copy of the database by going to Access > New and searching for “Product Inventory”
0
Comment
Question by:Sean Holloway
  • 7
  • 4
  • 4
  • +3
22 Comments
 
LVL 18
ID: 41798736
what is displayed is simply being looked up from the Products table.  If you want the ability to over-ride the price, then the AfterUpdate event of the product should fill the price and price needs to be a field in the underlying table that is a source for the form and the Control Source for the price -- so the form design needs to change.
0
 

Author Comment

by:Sean Holloway
ID: 41798746
Thanks Crystal, so on the Products table, navigate to the Table group and select AfterUpdate?
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 41798765
That would be the AfterUpdate event of the textbox where the new price is entered.  The code would check the price in the Products table, and update if needed (possibly with a confirmation, since you might not always want to update the price for everyone in future).
0
 
LVL 18
ID: 41798767
Hi Sean,

no the procedure that runs after the product is updated to fill in the price would be on the form that collects the information -- and the Purchase Order Details form already does that.  Use the form; don't enter information directly into the tables.
0
 

Author Comment

by:Sean Holloway
ID: 41798797
Thanks, I see that now. As for the options, there is nothing that really references update record.
0
 

Author Comment

by:Sean Holloway
ID: 41798808
Do I create a macro? If so, from what table?
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 41798884
VBA code is best, running from the AfterUpdate event of the text box with the price.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41798903
I assume you realize that if you update the value in the products table that any purchase orders with that product that exist will also be changed to the new value when run again.

It sounds like you need a table change that tracks when a price changes.

mlmcc
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41799004
I assume you realize that if you update the value in the products table that any purchase orders with that product that exist will also be changed to the new value when run again.
Not true if you design the tables correctly.  Think of the price associated with the product as the "suggested retail value".  When you add an item to the order, this SRV is copied from the product table to the order detail table where it may be overridden or modified by a discount percentage for the client.  this is now the actual sale price and stays on the order forever without change.  Changes to the SRV only apply to new orders.  Or, if your business rules specify, you might run an update query that finds any unshipped orders and modify the price on those.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41799128
What I said is valid.  If you update the price in the products table it will change for every order.  

Your method of allowing the price to be edited then saving in the orders or orders detail table is a good way to allow different pricing for products.

mlmcc
0
 
LVL 84
ID: 41799439
If you update the price in the products table it will change for every order.  
It would change for any NEW order, but in the context described above existing orders would remain unchanged, since all that happened was the SRV was collected when the product was selected onto that order, and was then placed into a separate field. This is SOP for Sales Order/Invoicing system.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 34

Expert Comment

by:PatHartman
ID: 41799685
Sean,
Northwind is actually too simplistic for a real world implementation.  In my previous post I only addressed mlmcc's disagreement with the recommendations experts had already made.  But there are two issues at play.  One is the standard price for a product, which is what I think you are asking to track, and the other is the price on each order.  SOP for this type of application is to copy the price from the product table to the order details at the time the order is placed (I think Northwind does NOT do this).  The price on the order details is the actual sale price and it could be different from the suggested price which originates on the product record.  Once the SRV is copied to the order, it is no longer the SRV and can be overridden based on your business rules.  For example, if a certain customer always gets a 10% discount, you would apply the discount at the time you copy the price from the product table to the order details table.  So a $10 price on the product results in a $9 price on the order.  For tracking SRV history, you need a new table.  To do that, you would add code in the product maintenance form to create the history record.  The absolute simplest way to to this is to run an append query in the AfterUpdate event of the form.  This query copies the current record and adds it to the history table.  Then if you want to analyze SRV over time, you would query the history table.  If you want to analyze actual sales prices, you would use the order details table.
0
 

Author Comment

by:Sean Holloway
ID: 41799689
Ok, thank you all for the input. Keeping in mind that I am using this database to track office supply inventory. When we need to restock a supply (we use the Purchase Order feature) and the vendor raises their price, I want to keep tabs on the difference between the old price and new price for accounting purposes.

What modifications should I make to achieve this?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41799695
To actually use Northwinds in a real business, you need to make many changes to it.  It is merely a shell and it doesn't follow standard business practice.  My previous post offered one solution to where the change should be made and what you need to do.
0
 

Author Comment

by:Sean Holloway
ID: 41799746
Sorry Pat, I replied before I saw your reply.

This is my first Access project in a long time. Considering Northwinds is a web database, I can't create an append query like I would for a Access Client Database.

I get the part about an AfterUpdate, but how do I tell the query to push that data to a new table after?

I created a query called UpdateHistory and added the tables Products and PurchaseOrderDetails. I added the fields ID, ProductName, ListPrice from the Products table and UnitCost from the PurchaseOrderDetails table.

I created a table called PriceUpdateHistory with the same fields.

Not sure what to do next.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 41799790
Perhaps you could post this task as a Gig.
0
 

Author Comment

by:Sean Holloway
ID: 41799803
I'd rather try and execute this myself.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41800372
Web databases are very limited in what they can do since they do not support VBA among other things.  What is the purpose of doing this as a web app?  You'll have much better support here if you use the client/server version.  I don't use the "Access" web app because it is too restrictive and simply doesn't do things that I do in virtually every application.  I put Access in quotes because this "feature" is Access in name only.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 41801687
I agree with Pat -- Access Web apps are virtually useless, lacking VBA, linked tables, and any kind of security.
0
 
LVL 18

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
ID: 41801730
the best use of a Web App is generally as a hybrid app:

1. do stuff on the web that needs to be shared
  there is security -- that is why SharePoint is needed
  while web apps don't have VBA, there is a lot you can do with macros if you are patient and persistent

2. do the heavy-lifting on the desktop and link to the web app tables

> "I can't create an append query like I would for a Access Client Database."

you can if you link to the web tables and use a desktop database that is linked to the web app tables and permission is set for read/write

> "how do I tell the query to push that data to a new table after?"

if you want to do this in a Data Macro, you can use ForEachRecord to loop through records in a specified table where optional criteria is met. Can use EditRecord, SetLocalVar, ….
0
 

Author Closing Comment

by:Sean Holloway
ID: 41801992
Thank you for your help.
0
 
LVL 18
ID: 41802180
you're welcome ~ happy to help
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now