Query sums based on checkbox criteria?????

[mrmims]

Hello,



I am working with an inventory database. I have three tables:
1) TBL_Components
2) TBL_Locations (All places the components are found. Some locations are marked as SpareLocation with a checkbox, all Components in these Locations are Spare. If SpareLocation is not checked then all Components in this Location are considered In Use)
3) TBL_ComponentConnections (Table that makes the connections between Component and Locations. In this table is the Quantity of each component in that Location)

I want a single query that will list each Component and two expressions for the quantities, one expression for quantity of each component in a SpareLocation and one expression for quantity of each component not in a SpareLocation. I can do this successfully as two separate queries, that is easy. But how do I write the expressions for all information to show in 1 query?

I tried 2 DSum expressions but they just returned the complete sum of components, instead of a sum of each individual component.

This is my SQL Query for Components In Use

SELECT TBL_Components.PartNumber, TBL_Components.Description, Sum(TBL_InventoryConnections.QuantityOnHand) AS SumOfQuantityOnHand, TBL_Location.SpareLocation
FROM TBL_Location INNER JOIN (TBL_Components INNER JOIN TBL_InventoryConnections ON TBL_Components.ComponentsID = TBL_InventoryConnections.ComponentsID) ON TBL_Location.LocationID = TBL_InventoryConnections.LocationID
GROUP BY TBL_Components.PartNumber, TBL_Components.Description, TBL_Location.SpareLocation
HAVING (((TBL_Location.SpareLocation)=False))
ORDER BY Sum(TBL_InventoryConnections.QuantityOnHand) DESC;

The SQL for the Spare Components is the same, but SpareLocation is True.

This is what I tried, but did not work. I get an error saying "You tried to execute a query that does not include the specified expression '[SpareLocation]=0 and [SpareLocation]=-1' as part of an aggregate function.

SELECT TBL_Components.PartNumber, TBL_Components.Description, Sum(TBL_InventoryConnections.QuantityOnHand) AS SumOfQuantityOnHand, Sum(TBL_InventoryConnections.QuantityOnHand) AS SumOfQuantityOnHand1
FROM TBL_Location INNER JOIN (TBL_Components INNER JOIN TBL_InventoryConnections ON TBL_Components.ComponentsID = TBL_InventoryConnections.ComponentsID) ON TBL_Location.LocationID = TBL_InventoryConnections.LocationID
GROUP BY TBL_Components.PartNumber, TBL_Components.Description
HAVING (([SpareLocation]=False) AND ([SpareLocation]=True))
ORDER BY Sum(TBL_InventoryConnections.QuantityOnHand) DESC , Sum(TBL_InventoryConnections.QuantityOnHand) DESC;

I have also attached a picture of the query design.

Thank you for your help, I am sure I am missing something simple.

[June7]

Try:

SELECT TBL_Components.PartNumber, TBL_Components.Description, Sum(IIf(SpareLocation=True, TBL_InventoryConnections.QuantityOnHand, Null)) AS SumOfQtySpare, Sum(IIf(SpareLocation=False, TBL_InventoryConnections.QuantityOnHand, Null)) AS SumOfQtyNotSpare, TBL_Location.SpareLocation
FROM TBL_Location INNER JOIN (TBL_Components INNER JOIN TBL_InventoryConnections ON TBL_Components.ComponentsID = TBL_InventoryConnections.ComponentsID) ON TBL_Location.LocationID = TBL_InventoryConnections.LocationID
GROUP BY TBL_Components.PartNumber, TBL_Components.Description, TBL_Location.SpareLocation
ORDER BY Sum(TBL_InventoryConnections.QuantityOnHand) DESC;

[mrmims]

SOLVED

You are my hero, and after looking at it makes complete sense!

Thank you so much.