Hi I’m trying to generate a query for inventory valuation and am trying to find the unit price for the parts, can anyone guide the table with stores the average cost of parts
Erp.PartCost is the table.
as @MikeGross said, it is the PartCost table. Note that this table can have multiple COST IDs in the same table. for single site companies, this is “normally” set to “1”, but it could be something different. The PLANT table holds the correct/active COSTID for your site. When I create queries, I typically do not assume COSTID=1, but typically link to the Plant table to find out the correct costid to use.
1 Like
@timshuwy is correct - we only ever have one cost ID, but yes you’ll need to link up those two tables to get the correct cost ID.
You said you wanted Avg costs, but since we’re here, I’ll add that if you want to be thorough about it, you will need to add Part and PartWhse tables, then calculate which columns in PartCost you want to use (Standard, Avg, etc.) based on the CostMethod assigned to the Part/Warehouse. 
The one thing you can count on around here is a complicated answer you were not expecting.
Thank you Mike and Tim, yes its Avg cost, thanks for the suggestion, this helps a lot
1 Like