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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple look up criteria
I have three queries.
First one: "Discrepancies in unit price": SELECT Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE] FROM Data WHERE (((Data.Month)=#1/1/2006#)) GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE] ORDER BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION; This is so that we can find only one manufacturer's product that has varying unit prices. (We need to find the discrepancies in unit prices to see when we are being charged various unit prices for the same products). Then I have the "DupDiscr" qry SELECT [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit Price].MANUFACTURER, [Discrepancies in Unit Price].DESCRIPTION, [Discrepancies in Unit Price].[UNIT PRICE] FROM [Discrepancies in Unit Price] WHERE ((([Discrepancies in Unit Price].[MFG #]) In (SELECT [MFG #] FROM [Discrepancies in Unit Price] As Tmp GROUP BY [MFG #] HAVING Count(*)1 ))) ORDER BY [Discrepancies in Unit Price].[MFG #]; which finds the duplicate entries for the same manufacturer number. Third Qry - "SQUP" which sums the Quantity Shipped by Unit price: SELECT Data.[MFG #], Data.[UNIT PRICE], Sum(Data.[QTY SHIP]) AS [SumOfQTY SHIP], Sum(Data.AMOUNT) AS SumOfAMOUNT FROM Data GROUP BY Data.[MFG #], Data.[UNIT PRICE]; How can I do a look up function in the "DupDiscr" qry that will look up the value in the "SQUP" qry matching the mfg # AND the Unit Price and bring back the "qty shipped value if the mfg# and unit price match that row??? I hope that I have made sense. What I am trying to do here (AND IF THERE IS AN EASIER WAY .... PLEASE TELL ME!!!) is find only the records that have duplicate mfg # and the unit price is different and sum up the quantity that was shipped for those items.... Please advise ) Thank you |
#2
|
|||
|
|||
Multiple look up criteria
Just a quick guess, Have you tried something like this?
SELECT Data.MANUFACTURER, DISTINCT(Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE]), sum([UNIT PRICE]) AS TOTAL_COST FROM Data WHERE (((Data.Month)=#1/1/2006#)) GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE] ORDER BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION; Dee wrote: I have three queries. First one: "Discrepancies in unit price": SELECT Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE] FROM Data WHERE (((Data.Month)=#1/1/2006#)) GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE] ORDER BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION; This is so that we can find only one manufacturer's product that has varying unit prices. (We need to find the discrepancies in unit prices to see when we are being charged various unit prices for the same products). Then I have the "DupDiscr" qry SELECT [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit Price].MANUFACTURER, [Discrepancies in Unit Price].DESCRIPTION, [Discrepancies in Unit Price].[UNIT PRICE] FROM [Discrepancies in Unit Price] WHERE ((([Discrepancies in Unit Price].[MFG #]) In (SELECT [MFG #] FROM [Discrepancies in Unit Price] As Tmp GROUP BY [MFG #] HAVING Count(*)1 ))) ORDER BY [Discrepancies in Unit Price].[MFG #]; which finds the duplicate entries for the same manufacturer number. Third Qry - "SQUP" which sums the Quantity Shipped by Unit price: SELECT Data.[MFG #], Data.[UNIT PRICE], Sum(Data.[QTY SHIP]) AS [SumOfQTY SHIP], Sum(Data.AMOUNT) AS SumOfAMOUNT FROM Data GROUP BY Data.[MFG #], Data.[UNIT PRICE]; How can I do a look up function in the "DupDiscr" qry that will look up the value in the "SQUP" qry matching the mfg # AND the Unit Price and bring back the "qty shipped value if the mfg# and unit price match that row??? I hope that I have made sense. What I am trying to do here (AND IF THERE IS AN EASIER WAY .... PLEASE TELL ME!!!) is find only the records that have duplicate mfg # and the unit price is different and sum up the quantity that was shipped for those items.... Please advise ) Thank you |
#3
|
|||
|
|||
Multiple look up criteria
It tells me "Undefined function 'DISTINCT' in expression....
"Roger" wrote: Just a quick guess, Have you tried something like this? SELECT Data.MANUFACTURER, DISTINCT(Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE]), sum([UNIT PRICE]) AS TOTAL_COST FROM Data WHERE (((Data.Month)=#1/1/2006#)) GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE] ORDER BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION; Dee wrote: I have three queries. First one: "Discrepancies in unit price": SELECT Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE] FROM Data WHERE (((Data.Month)=#1/1/2006#)) GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE] ORDER BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION; This is so that we can find only one manufacturer's product that has varying unit prices. (We need to find the discrepancies in unit prices to see when we are being charged various unit prices for the same products). Then I have the "DupDiscr" qry SELECT [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit Price].MANUFACTURER, [Discrepancies in Unit Price].DESCRIPTION, [Discrepancies in Unit Price].[UNIT PRICE] FROM [Discrepancies in Unit Price] WHERE ((([Discrepancies in Unit Price].[MFG #]) In (SELECT [MFG #] FROM [Discrepancies in Unit Price] As Tmp GROUP BY [MFG #] HAVING Count(*)1 ))) ORDER BY [Discrepancies in Unit Price].[MFG #]; which finds the duplicate entries for the same manufacturer number. Third Qry - "SQUP" which sums the Quantity Shipped by Unit price: SELECT Data.[MFG #], Data.[UNIT PRICE], Sum(Data.[QTY SHIP]) AS [SumOfQTY SHIP], Sum(Data.AMOUNT) AS SumOfAMOUNT FROM Data GROUP BY Data.[MFG #], Data.[UNIT PRICE]; How can I do a look up function in the "DupDiscr" qry that will look up the value in the "SQUP" qry matching the mfg # AND the Unit Price and bring back the "qty shipped value if the mfg# and unit price match that row??? I hope that I have made sense. What I am trying to do here (AND IF THERE IS AN EASIER WAY .... PLEASE TELL ME!!!) is find only the records that have duplicate mfg # and the unit price is different and sum up the quantity that was shipped for those items.... Please advise ) Thank you |
Thread Tools | |
Display Modes | |
|
|