Multi Dlookup in a query with grouping

[ZigZagZak]

Can someone help me with this queries SQL? I have been beating my head on
the desk for hours and can't figure out how to get the CPI: value to work
correctly. Right now it comes back with the first value on the table. I
need it to match the "Bar type" & the "Bar size" with each of the group &
give the CPI. Hopefully that makes sense....I am mentially tired right now.

Thanks
Zach

SELECT
NZ(Sum([LENGTH]*0.2833*(([BarSizeDec]/2)*([BarSizeDec]/2))*3.14159),0) AS
[TOTAL WEIGHT], RECVNEWBAR.[BAR TYPE],
DLookUp("[Costperin]","[BARcost]","[BARCOST]![BARSIZE] ='[BAR SIZE]'" And
"[BARCOST]![BARTYPE] = '[BAR TYPE]'") AS CPI, Sum(RECVNEWBAR.LENGTH) AS
SumOfLENGTH
FROM RECVNEWBAR
WHERE (((RECVNEWBAR.RECVDATE)=#5/1/2009#))
GROUP BY RECVNEWBAR.[BAR TYPE]
HAVING (((RECVNEWBAR.[BAR TYPE])<>"303SS" And (RECVNEWBAR.[BAR
TYPE])<>"304SS" And (RECVNEWBAR.[BAR TYPE])<>"CAST IRON" And (RECVNEWBAR.[BAR
TYPE])<>"YELLOW BAR"));

 

[John Spencer]

It seems to me that you could simplify the entire query to something like

SELECT NZ(Sum([LENGTH]*0.2833*[BarSizeDec]/4*3.14159),0) AS [TOTAL WEIGHT]
, RECVNEWBAR.[BAR TYPE]
, CostPerIn AS CPI
, Sum(RECVNEWBAR.LENGTH) AS SumOfLENGTH
FROM RECVNEWBAR LEFT JOIN BarCost
ON RECVNEWBAR.BarSize = BarCost.BarSize
AND RECVNEWBAR.BarType = BarCost.BarType
WHERE RECVNEWBAR.RECVDATE=#5/1/2009#
AND RECVNEWBAR.[BAR TYPE])NOT IN ("303SS","304SS","Cast Iron", "Yellow Bar")
GROUP BY RECVNEWBAR.[BAR TYPE], CostPerIn


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[ZigZagZak]

Ok I repied once already, but it didn't go through.
Just wanted to say thanks! You have amazed me and made my day!

Zach

It seems to me that you could simplify the entire query to something like

SELECT NZ(Sum([LENGTH]*0.2833*[BarSizeDec]/4*3.14159),0) AS [TOTAL WEIGHT]
, RECVNEWBAR.[BAR TYPE]
, CostPerIn AS CPI
, Sum(RECVNEWBAR.LENGTH) AS SumOfLENGTH
FROM RECVNEWBAR LEFT JOIN BarCost
ON RECVNEWBAR.BarSize = BarCost.BarSize
AND RECVNEWBAR.BarType = BarCost.BarType
WHERE RECVNEWBAR.RECVDATE=#5/1/2009#
AND RECVNEWBAR.[BAR TYPE])NOT IN ("303SS","304SS","Cast Iron", "Yellow Bar")
GROUP BY RECVNEWBAR.[BAR TYPE], CostPerIn


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Can someone help me with this queries SQL? I have been beating my head on
the desk for hours and can't figure out how to get the CPI: value to work
correctly. Right now it comes back with the first value on the table. I
need it to match the "Bar type" & the "Bar size" with each of the group &
give the CPI. Hopefully that makes sense....I am mentially tired right now.

Thanks
Zach

SELECT
NZ(Sum([LENGTH]*0.2833*(([BarSizeDec]/2)*([BarSizeDec]/2))*3.14159),0) AS
[TOTAL WEIGHT], RECVNEWBAR.[BAR TYPE],
DLookUp("[Costperin]","[BARcost]","[BARCOST]![BARSIZE] ='[BAR SIZE]'" And
"[BARCOST]![BARTYPE] = '[BAR TYPE]'") AS CPI, Sum(RECVNEWBAR.LENGTH) AS
SumOfLENGTH
FROM RECVNEWBAR
WHERE (((RECVNEWBAR.RECVDATE)=#5/1/2009#))
GROUP BY RECVNEWBAR.[BAR TYPE]
HAVING (((RECVNEWBAR.[BAR TYPE])<>"303SS" And (RECVNEWBAR.[BAR
TYPE])<>"304SS" And (RECVNEWBAR.[BAR TYPE])<>"CAST IRON" And (RECVNEWBAR.[BAR
TYPE])<>"YELLOW BAR"));