New to Access - Help with Queries

[KFF]

Hi there,

Relatively new to access, so my terms might not be correct. I hope my problem is query-related.

Project: I am developing a database for items from our ERP system so I can add additional details and update pricing in bulk. Because the data has to be formatted a certain way before being updated to our ERP, I need to keep it all in this separate database. In addition to this, the pricing and unit of measure of each item in our ERP may or may not be different than our pricing and unit of measure for our e-commerce. Before updating pricing, the unit of measure must match the ERP system's unit of measure for that item or it won't be accepted.

Example: Our vendor may give us a price sheet with boxes listed by price by 1000, indicated by the letter M. However, we stock and sell cartons by each, indicated by EA. I cannot do this individually because there are about 10,000 box sizes. Not all of them are in by M (1000) so I can't just do a mass calculation for that group of items.



Request: I would like to run some type of query that does the following:
If Item_Category = 075 then follow these steps:
UOM_Current (unit of measure of current pricing in our ERP) matches UOM_Update (the updated price form unit of measure), then update PRICE_Current to what PRICE_Update is.
If UOM_Current = EA (each) and UOM_Update = M, divide PRICE_Update by 1000 and update PRICE_Current with that result.

Result: If PRICE_Current for box A is listed as EA (each) for $0.14 and we have PRICE_Update for that same box as $160 per M (1000), then I would like the PRICE_Current to be updated to $0.16 in the query. OR, a new column added, whatever it takes.

Thank you for your time and patience with this - the more details you can give the better, or resources for problems like this!

[rpeare]

There are a lot of ways to do this, so this is one suggestion.

You have a list of products:

tblProducts
PR_ID  PR_Name ---->  Other product related fields
1      Product A
2      Product B

Now you have two different types of activities with each product, receiving and issuing, you can have a table to store both types:

tblIssueType
IT_ID  IT_Desc
1      Issue
2      Receipt

tblProductXref
PX_ID  PR_ID  IT_ID  Price  QtyCode  Qty
1      1      2      140    M        1000
2      1      1      .14    EA       1
3      1      1      1.68   BX       12
4      1      1      20.16  CS       144

So, when you update the price of a RECEIPT item you can then force an update of the price of an ISSUE item with the same product ID by getting a unit cost and multiplying it by the quantity in the measure (1 for EA, 12 for a box 144 for a case in this instance)