Multiple look up criteria

[Dee]

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

 

[Roger]

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]

It tells me "Undefined function 'DISTINCT' in expression....

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;

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