Query Help

[illy2k]

If you have a query that has order id as one field and totalprice as another field, can the query assign the totalprice a 0 value if for an order id there was no totalprice entered. See if you don't enter a totalprice, yet you want to pull another total for let's say maintanence in the same query can you set totalprice = to 0, even though the total price for that order id# never existed?

Query:
Order Id# TotalPrice MaintTotalPrice



if there is no TotalPrice, the query also wont show MaintTotalPrice even if this has a value. Is there anyway to set TotalPrice = 0 within the query, if the query sees that there is no value for it?

 

[Jon K]

Switch to query SQL View and paste the SQL statement of your query here.

 

[alpine82]

Here

SELECT DISTINCTROW Orders.OrderID, Format$([Orders].[Order Date],'Long Date') AS [Order Date By Day], Orders.UnitNum, Sum([Fluids #2 Qry].[Sum Of FluidsTotal]) AS [Sum Of Sum Of FluidsTotal], Sum([InHouse #2 Qry].[Sum Of InHouseTotal]) AS [Sum Of Sum Of InHouseTotal]
FROM [InHouse #2 Qry] INNER JOIN ([Fluids #2 Qry] INNER JOIN Orders ON [Fluids #2 Qry].OrderID = Orders.OrderID) ON [InHouse #2 Qry].OrderID = Orders.OrderID
GROUP BY Orders.OrderID, Format$([Orders].[Order Date],'Long Date'), Orders.UnitNum;

 

[Jon K]

Try adding two Nz() functions in the SQL statement as follows:-

SELECT DISTINCTROW Orders.OrderID, Format$([Orders].[Order Date],'Long Date') AS [Order Date By Day], Orders.UnitNum,
Sum(Nz([Fluids #2 Qry].[Sum Of FluidsTotal])) AS [Sum Of Sum Of FluidsTotal],
Sum(Nz([InHouse #2 Qry].[Sum Of InHouseTotal])) AS [Sum Of Sum Of InHouseTotal]
FROM [InHouse #2 Qry] INNER JOIN ([Fluids #2 Qry] INNER JOIN Orders ON [Fluids #2 Qry].OrderID = Orders.OrderID) ON [InHouse #2 Qry].OrderID = Orders.OrderID
GROUP BY Orders.OrderID, Format$([Orders].[Order Date],'Long Date'), Orders.UnitNum;

What the Nz() functions do is change any null values to zeroes before summing.


You said: "if there is no TotalPrice, the query also wont show MaintTotalPrice even if this has a value."

So most likely you will need to use the Nz() functions on the fields that comprise the [Sum Of FluidsTotal] and the [Sum Of InHouseTotal] in the queries [Fluids #2 Qry] and [InHouse #2 Qry], rather than using the Nz() functions in the above query.

For example, if [Sum Of InHouseTotal] is equal to FieldA + FieldB and FieldA may contain some null values, you can use:-
Sum(Nz([TableName].[FieldA]) + [TableName].[FieldB]) when calculating [Sum Of InHouseTotal]