If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > PC based Database Applications > Microsoft Access > Create a Query that looks up price based on date purchased, material code, po#, etc.

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Dec 2012
Posts: 1
Create a Query that looks up price based on date purchased, material code, po#, etc.

I would like to have a Query in my database that returns what the price should be for materials we purchase. The price is dependent on multiple factors.

  1. Prices adjust on various days for various materials at various locations. I need to match the material purchased to the price in effect after the date in tblPrice_Updates
  2. The prices are linked to an OP number (a Purchase Order, our shorthand nomenclature is backwards!) and a Plant Number I use this OP number in tblMaterial_Purchased in a combo select box so the person entering data can pick the correct OP based on the other values (Vendor Name and Plant Number)
  3. In the tblMaterial_Purchased they enter the Kit_Number (Material Code) to determine what we bought. This field is also in tblPrice_Updates.

The idea is that after putting in the date, Kit_Number, Plant_Number (Probably irrelevant for the Query since the OP_Number is tied to specific plants), and the OP_Number in tblMaterial_Purchased, the new query would return a price for each line based on the date bought.

Attached is a sable database that had been sanitized.
Attached Files
File Type: zip Price Lookup Database.zip (392.8 KB, 7 views)
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On