Multi Dlookup in a query with grouping

  • Thread starter Thread starter ZigZagZak
  • Start date Start date
Z

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"));
 
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
 
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

John Spencer said:
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"));
Click to expand...
Click to expand...
 
Back
Top