Solved: Query Logic

[wz72n01]

If I have the following 3 records:

Location1: Kansas
Part Number1: A1234
Seller Part Number1: B1234
Customer Price1: $10
Seller Price1: $8
Volume1: 20

Location2: Georgia
Part Number2: A2345
Seller Part Number1: B1234
Customer Price2: $12
Seller Price1: $8
Volume2: 15

Location3: Colorado
Part Number1: A3456
Seller Part Number1: B1234
Customer Price3: $14
Seller Price2: $6
Volume3: 10

How do I correctly calculate
#1 The difference between prices from different locations and multiply by the correct volume (e.g. Georgia and Colorado will be $14 - $12 x 10)

#2 The difference between the lowest Seller price and each location(e.g. Georgia would be $12 - $6 x 15)

My queries just take the min and max values and multiply by average volume -- I am not sure how to get the CORRECT volume for each location :think:

[OBP]

Shouldn't they all be the same part number for a fair comparison?
Anyway have a look at this "difference query" database it shows how to add and Subtract from previous records. But to get what you want may take 2 queries.

[wz72n01]

You are right. But I need to add a dashboard that will pull up the different customer part number associated with same seller part number. Let me take a look at the query. Thanks

[wz72n01]

The name of my table is Customer-Cust 2
My Fields are ID#, Supplier Part Number, Customer_Part_Number, Current_Price_Customer_Price

where the Supplier_Part_Number is the same, I want to take the difference from the maximum Current_Price_Customer_Price. I cannot get the NextValue expression to work

[OBP]

The t1 in the query is not a "table" as such, it is a named expression in the SQL statement that is used to temporarily store values.

[wz72n01]

Using the expression that you provided, how can I used the fields in my table to get the same output? how should I leverage the temporary table expression?

ID#, Supplier Part Number, Customer_Part_Number, Current_Price_Customer_Price

[wz72n01]

I figured it out. Thanks

[wz72n01]

SELECT t1.ID, t1.Supplier_Part_Number, t1.Current_Price_per_Customer_Price_List, t1.Current_Price_per_Supplier_MOU_Price_List_USD,
(SELECT min(t2.Current_Price_per_Supplier_MOU_Price_List_USD) from Table2 as t2 WHERE t2.Supplier_Part_Number = t1.Supplier_Part_Number) AS LowestSupplierPrice, t1.Current_Price_per_Customer_Price_List- LowestSupplierPrice AS SavOpp, t1.Quantity, t1.Quantity*SavOpp AS PotentialSavingsOpp
FROM Table2 AS t1
ORDER BY t1.Supplier_Part_Number, t1.Current_Price_per_Supplier_MOU_Price_List_USD, t1.Current_Price_per_Customer_Price_List;

If I wanted SavOpp to default to zero if there is an ERROR or it is negative, how would I code it in? an IFF statement? Sometimes Current_Price_per_Supplier_MOU_Price_List_USD has "No Price" in the cell making the calculation an error

[OBP]

Yes you would use an IIF(Not Isnull([SavOpp]), do your stuff here, 0)

or you could just exclude those records by putting
Not is Null
in the Criteria row of the SavOpp field.