A
AlienzDDS
The following query is not sorting as required. I am adding up cases shipped
out within date parameters and would then like to sort by number of cases
descending. Any help is appreciated.
SELECT Items.Environment, Items.Item, Last(Items.Description) AS
LastOfDescription, Customers.CustomerID, Customers.Name,
nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1 And
[actualpickupdate]<DateAdd("d",1,Forms!FrmItemMovmtParameters!Date2),[qtycases])),0)
AS CasesOut, Customers.InactiveCust,
nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1 And
[actualpickupdate]<DateAdd("d",1,Forms!FrmitemmovmtParameters!Date2),[qtypallets])),0) AS PalletsOut
FROM (Customers LEFT JOIN Items ON Customers.CustomerID = Items.CustomerID)
LEFT JOIN (BillsOfLading RIGHT JOIN InventoryCurrent ON
BillsOfLading.BillOfLadingID = InventoryCurrent.BillOfLadingID) ON
Items.ItemID = InventoryCurrent.ItemID
GROUP BY Items.Environment, Items.Item, Customers.CustomerID,
Customers.Name, Customers.InactiveCust
ORDER BY nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1
And
[actualpickupdate]<DateAdd("d",1,Forms!FrmItemMovmtParameters!Date2),[qtycases])),0) DESC;
Thanks, Alison.
out within date parameters and would then like to sort by number of cases
descending. Any help is appreciated.
SELECT Items.Environment, Items.Item, Last(Items.Description) AS
LastOfDescription, Customers.CustomerID, Customers.Name,
nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1 And
[actualpickupdate]<DateAdd("d",1,Forms!FrmItemMovmtParameters!Date2),[qtycases])),0)
AS CasesOut, Customers.InactiveCust,
nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1 And
[actualpickupdate]<DateAdd("d",1,Forms!FrmitemmovmtParameters!Date2),[qtypallets])),0) AS PalletsOut
FROM (Customers LEFT JOIN Items ON Customers.CustomerID = Items.CustomerID)
LEFT JOIN (BillsOfLading RIGHT JOIN InventoryCurrent ON
BillsOfLading.BillOfLadingID = InventoryCurrent.BillOfLadingID) ON
Items.ItemID = InventoryCurrent.ItemID
GROUP BY Items.Environment, Items.Item, Customers.CustomerID,
Customers.Name, Customers.InactiveCust
ORDER BY nz(Sum(IIf([actualpickupdate]>forms!frmitemmovmtparameters!date1
And
[actualpickupdate]<DateAdd("d",1,Forms!FrmItemMovmtParameters!Date2),[qtycases])),0) DESC;
Thanks, Alison.