help with HAVING clause in a query 1

[davyre]

Hi,

I have a query to show all orders and its units that is still "Pending". Here is the query:

SELECT TblCustOrderUnit.OrderID, TblCustomer.CustomerName, TblCustOrder.PONumber, TblCustOrder.JobName, TblCustOrder.CustomerUDD, TblUnits.UnitName, Count(TblCustOrderUnit.UnitID) AS Qty, TblCustOrderUnit.Status
FROM TblUnits INNER JOIN ((TblCustomer INNER JOIN TblCustOrder ON TblCustomer.[CustomerID] = TblCustOrder.[CustomerID]) INNER JOIN TblCustOrderUnit ON TblCustOrder.[CustOrderID] = TblCustOrderUnit.[OrderID]) ON TblUnits.[UnitID] = TblCustOrderUnit.[UnitID]
GROUP BY TblCustOrderUnit.OrderID, TblCustomer.CustomerName, TblCustOrder.PONumber, TblCustOrder.JobName, TblCustOrder.CustomerUDD, TblUnits.UnitName, TblCustOrderUnit.Status, TblCustomer.CustomerName, TblCustOrder.DeliveryStatus, TblCustOrderUnit.PartOrdered
HAVING (((TblCustOrderUnit.Status)="Pending") AND ((TblCustOrder.DeliveryStatus)="Pending") AND ((TblCustOrderUnit.PartOrdered)=False))
ORDER BY TblCustOrder.CustomerUDD, TblCustomer.CustomerName;

The problem that I am having is I want to add more condition on the query, to not displaying/resulting some certain records based on its OrderID and UnitID. If I modify the HAVING clause to:

HAVING ([COLOR=#F57900]((TblCustOrderUnit.OrderID)<>494) AND ((TblCustOrderUnit.UnitID)<>5)[/color] AND (TblCustOrderUnit.Status)='Pending') AND ((TblCustOrder.DeliveryStatus)='Pending') AND ((TblCustOrderUnit.PartOrdered)=False)

it will show all records except OrderID 494 and UnitID 5. What I want is to not display one record which has orderID 494 and unitID 5 (orderID 494 has unitID 5 and 11). I hope you understand my query. Anyone can help? THanks!

 

[dhookom]

Ideally the filtering should be in a WHERE clause rather than the HAVING clause
So, what is wrong with the results from.

SELECT TblCustOrderUnit.OrderID, TblCustomer.CustomerName, TblCustOrder.PONumber, 
TblCustOrder.JobName, TblCustOrder.CustomerUDD, TblUnits.UnitName, 
Count(TblCustOrderUnit.UnitID) AS Qty, TblCustOrderUnit.Status
FROM TblUnits INNER JOIN ((TblCustomer
 INNER JOIN TblCustOrder ON TblCustomer.[CustomerID] = TblCustOrder.[CustomerID])
 INNER JOIN TblCustOrderUnit ON TblCustOrder.[CustOrderID] = TblCustOrderUnit.[OrderID])
 ON TblUnits.[UnitID] = TblCustOrderUnit.[UnitID]
WHERE TblCustOrderUnit.Status="Pending" AND TblCustOrder.DeliveryStatus="Pending"
 AND TblCustOrderUnit.PartOrdered=False
 AND TblCustOrderUnit.OrderID<>494 AND TblCustOrderUnit.UnitID<>5
GROUP BY TblCustOrderUnit.OrderID, TblCustomer.CustomerName, TblCustOrder.PONumber, 
 TblCustOrder.JobName, TblCustOrder.CustomerUDD, TblUnits.UnitName, TblCustOrderUnit.Status, 
 TblCustomer.CustomerName, TblCustOrder.DeliveryStatus, TblCustOrderUnit.PartOrdered
ORDER BY TblCustOrder.CustomerUDD, TblCustomer.CustomerName;

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

Hi Duane,

I was just using Query Wizard to get my SQL there, so I do not intend to use HAVING instead of WHERE from the beginning.
Anyway, the problem about my SQL there (and yours too), is that if you say OrderID<>494 AND UnitID<>5, it will filter any order with OrderID 494 and any order with UnitID 5.
What I wanted is to filter any order that has OrderID 494 and also UnitID 5 (in one order/record).

So, instead of filtering any order with OrderID 494 and any order with UnitID 5, I just want to filter one order with OrderID 494 UnitID 5 (I am planning to use variables in the future instead using 494 and 5).

Hope I am clear enough..Thanks!

 

[PHV]

What about this WHERE clause ?
WHERE TblCustOrderUnit.Status='Pending' AND TblCustOrder.DeliveryStatus='Pending'
AND TblCustOrderUnit.PartOrdered=False
AND NOT (TblCustOrderUnit.OrderID=494 AND TblCustOrderUnit.UnitID=5)


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

it worked like a charm! Thanks PH!