x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 527

# Creating a query calculation depending upon date value

I need to modify the query calculation "UnshippedAmt: ([QuantityOrdered]-[QuantityShipped])*[PricePerUnit]" to return two different totals. If there is no value in the "ExpectedShipDate" field then the result should be added to Total A (pending shipments). If there is a date value in "ExpectedShipDate" then it needs to be excluded from Total A and instead added to Total B (future-dated shipments). Both of these totals need to be shown on the report. How can the query or report be modified to achieve this?
0
greatcomputing
4 Solutions

Commented:
Try the IIF function:

iif(condition1, value if true, value if false)

you can also use nesting:

iif(condition1, value if condition1 is  true, iif(condition2, value if condition 2 is true, iif(condition3, value if condition 3 is true, value if conditions 1 and 2 and 3 are all false)))

I didn't entirely understand your question, but the below will add quantityordered + quantityshipped if the expected ship date is empty.  otherwise it will subtract quantityshipped from quantityordered

IIF(isnull[ExpectedShipDate], ([QuantityOrdered] + [QuantityShipped])*[PricePerUnit], ([QuantityOrdered] - [QuantityShipped])*[PricePerUnit])

Hope this helps

0

Commented:
SELECT
Sum(iif(ExpectedShipDate, [QuantityOrdered]-[QuantityShipped])*[PricePerUnit],0) AS TotalB,
Sum(iif(Not ExpectedShipDate, [QuantityOrdered]-[QuantityShipped])*[PricePerUnit],0) AS TotalB
FROM myTable;

0

Commented:
Asume field M in Detail Secion
Create pending, future Fields to hold totals depending on date Null or NotNull
pending = IIf(IsNull([D]);[M];0)      - Running sum Over All
future = IIf(IsNull([D]);0;[M]) - Running sum Over All
0

Author Commented:
Added the following to the SELECT statement:

Sum(iif(ExpectedShipDate, ([QuantityOrdered]-[QuantityShipped])*[PricePerUnit],0)) AS FutureShipTotal
Sum(iif(Not ExpectedShipDate, ([QuantityOrdered]-[QuantityShipped])*[PricePerUnit],0)) AS ShipNowTotal

Now there is the error: "You tried to execute a query that does not include the specified expression 'Status' as part of an aggregate function." When the query is changed, it replaces 'Status' with the first table column referenced in the SELECT statement.
0

Commented:
Lets see the whole query please.
0

Author Commented:
The entire query is:

SELECT TempOrderList.Status, TempOrderList.Approved, OrdersQuery.*, OrderDetailsQuery.ItemNumber, OrderDetailsQuery.Product, OrderDetailsQuery.PricePerUnit, OrderDetailsQuery.QuantityOrdered, OrderDetailsQuery.QuantityShipped, OrderDetailsQuery.BilledSubtotal, OrderDetailsQuery.ShippedSubtotal, OrderDetailsQuery.[Finite Options], OrderDetailsQuery.[Freeform Options], OrderDetailsQuery.SKU, OrderDetailsQuery.Option01, OrderDetailsQuery.Option02, OrderDetailsQuery.Option03, OrderDetailsQuery.Option04, OrderDetailsQuery.Option05, OrderDetailsQuery.Option06, OrderDetailsQuery.Option07, OrderDetailsQuery.Option08, OrderDetailsQuery.Option09, OrderDetailsQuery.Option10, OrderDetailsQuery.Backordered, OrderDetailsQuery.Adjustment, TempOrderList.ManualOrder, QuantityShipped*PricePerUnit AS LineTotal, OrderDetailsQuery.ExpectedShipDate, ([QuantityOrdered]-[QuantityShipped])*[PricePerUnit] AS UnshippedAmt, Sum(iif(ExpectedShipDate, ([QuantityOrdered]-[QuantityShipped])*[PricePerUnit],0)) AS FutureShipTotal, Sum(iif(Not ExpectedShipDate, ([QuantityOrdered]-[QuantityShipped])*[PricePerUnit],0)) AS ShipNowTotal
FROM (TempOrderList INNER JOIN OrdersQuery ON TempOrderList.OrderNumber = OrdersQuery.OrderNumber) INNER JOIN OrderDetailsQuery ON OrdersQuery.OrderNumber = OrderDetailsQuery.OrderNumber
WHERE (((OrderDetailsQuery.Adjustment)=False) AND ((TempOrderList.ManualOrder)=False) AND ((OrdersQuery.OrderDate) Between [enter start date] And [enter end date])) OR (((OrderDetailsQuery.Product)<>"Adjustment for item(s) not shipped") AND ((OrderDetailsQuery.SKU)="Product") AND ((TempOrderList.ManualOrder)=False) AND ((OrdersQuery.OrderDate) Between [enter start date] And [enter end date]));
0

Commented:
Whenever you add an aggregate function to a query, everything not aggregated must be included in a GROUP BY clause following the WHERE clause as per the snippet.

``````SELECT TempOrderList.Status,
TempOrderList.Approved,
OrdersQuery.*,
OrderDetailsQuery.ItemNumber,
OrderDetailsQuery.Product,
OrderDetailsQuery.PricePerUnit,
OrderDetailsQuery.QuantityOrdered,
OrderDetailsQuery.QuantityShipped,
OrderDetailsQuery.BilledSubtotal,
OrderDetailsQuery.ShippedSubtotal,
OrderDetailsQuery.[Finite Options],
OrderDetailsQuery.[Freeform Options],
OrderDetailsQuery.SKU,
OrderDetailsQuery.Option01,
OrderDetailsQuery.Option02,
OrderDetailsQuery.Option03,
OrderDetailsQuery.Option04,
OrderDetailsQuery.Option05,
OrderDetailsQuery.Option06,
OrderDetailsQuery.Option07,
OrderDetailsQuery.Option08,
OrderDetailsQuery.Option09,
OrderDetailsQuery.Option10,
OrderDetailsQuery.Backordered,
TempOrderList.ManualOrder,
QuantityShipped*PricePerUnit AS LineTotal,
OrderDetailsQuery.ExpectedShipDate,
([QuantityOrdered]-[QuantityShipped])*[PricePerUnit] AS UnshippedAmt,
Sum(iif(ExpectedShipDate, ([QuantityOrdered]-[QuantityShipped])*[PricePerUnit],0)) AS FutureShipTotal,
Sum(iif(Not ExpectedShipDate, ([QuantityOrdered]-[QuantityShipped])*[PricePerUnit],0)) AS ShipNowTotal
FROM (TempOrderList INNER JOIN OrdersQuery ON TempOrderList.OrderNumber = OrdersQuery.OrderNumber) INNER JOIN OrderDetailsQuery ON OrdersQuery.OrderNumber = OrderDetailsQuery.OrderNumber
WHERE (((OrderDetailsQuery.Adjustment)=False) AND ((TempOrderList.ManualOrder)=False) AND ((OrdersQuery.OrderDate) Between [enter start date] And [enter end date])) OR (((OrderDetailsQuery.Product)<>"Adjustment for item(s) not shipped") AND ((OrderDetailsQuery.SKU)="Product") AND ((TempOrderList.ManualOrder)=False) AND ((OrdersQuery.OrderDate) Between [enter start date] And [enter end date]))
GROUP BY TempOrderList.Status,
TempOrderList.Approved,
OrdersQuery.*,
OrderDetailsQuery.ItemNumber,
OrderDetailsQuery.Product,
OrderDetailsQuery.PricePerUnit,
OrderDetailsQuery.QuantityOrdered,
OrderDetailsQuery.QuantityShipped,
OrderDetailsQuery.BilledSubtotal,
OrderDetailsQuery.ShippedSubtotal,
OrderDetailsQuery.[Finite Options],
OrderDetailsQuery.[Freeform Options],
OrderDetailsQuery.SKU,
OrderDetailsQuery.Option01,
OrderDetailsQuery.Option02,
OrderDetailsQuery.Option03,
OrderDetailsQuery.Option04,
OrderDetailsQuery.Option05,
OrderDetailsQuery.Option06,
OrderDetailsQuery.Option07,
OrderDetailsQuery.Option08,
OrderDetailsQuery.Option09,
OrderDetailsQuery.Option10,
OrderDetailsQuery.Backordered,
TempOrderList.ManualOrder,
QuantityShipped*PricePerUnit,
OrderDetailsQuery.ExpectedShipDate,
([QuantityOrdered]-[QuantityShipped])*[PricePerUnit];
``````
0

Author Commented:
I have modified the query as follows. It runs, but the values for FutureShipTotal and ShipNowTotal are always zero.

hnasr: How do I add that code to my query?
``````SELECT TempOrderList.Status, TempOrderList.Approved, OrderDetailsQuery.ItemNumber, OrderDetailsQuery.Product, OrderDetailsQuery.PricePerUnit, OrderDetailsQuery.QuantityOrdered, OrderDetailsQuery.QuantityShipped, OrderDetailsQuery.BilledSubtotal, OrderDetailsQuery.ShippedSubtotal, OrderDetailsQuery.[Finite Options], OrderDetailsQuery.[Freeform Options], OrderDetailsQuery.SKU, OrderDetailsQuery.Option01, OrderDetailsQuery.Option02, OrderDetailsQuery.Option03, OrderDetailsQuery.Option04, OrderDetailsQuery.Option05, OrderDetailsQuery.Option06, OrderDetailsQuery.Option07, OrderDetailsQuery.Option08, OrderDetailsQuery.Option09, OrderDetailsQuery.Option10, OrderDetailsQuery.Backordered, OrderDetailsQuery.Adjustment, TempOrderList.ManualOrder, QuantityShipped*PricePerUnit AS LineTotal, OrderDetailsQuery.ExpectedShipDate, ([QuantityOrdered]-[QuantityShipped])*[PricePerUnit] AS UnshippedAmt, Sum(IIf(OrderDetailsQuery.ExpectedShipDate,([OrderDetailsQuery.QuantityOrdered]-[OrderDetailsQuery.QuantityShipped])*OrderDetailsQuery.[PricePerUnit],0)) AS FutureShipTotal, Sum(IIf(Not OrderDetailsQuery.ExpectedShipDate,(OrderDetailsQuery.[QuantityOrdered]-OrderDetailsQuery.[QuantityShipped])*OrderDetailsQuery.[PricePerUnit],0)) AS ShipNowTotal, OrdersQuery.OrderNumber, OrdersQuery.OrderDate, OrdersQuery.Name, OrdersQuery.BalanceDue, OrdersQuery.FinalProductTotal, OrdersQuery.SourceOrderNumber, OrdersQuery.SourceOrderID
FROM (TempOrderList INNER JOIN OrdersQuery ON TempOrderList.OrderNumber = OrdersQuery.OrderNumber) INNER JOIN OrderDetailsQuery ON OrdersQuery.OrderNumber = OrderDetailsQuery.OrderNumber
WHERE (((OrderDetailsQuery.Adjustment)=False) AND ((TempOrderList.ManualOrder)=False) AND ((OrdersQuery.OrderDate) Between [enter start date] And [enter end date])) OR (((TempOrderList.ManualOrder)=False) AND ((OrdersQuery.OrderDate) Between [enter start date] And [enter end date]) AND ((OrderDetailsQuery.Product)<>"Adjustment for item(s) not shipped") AND ((OrderDetailsQuery.SKU)="Product"))
GROUP BY TempOrderList.Status, TempOrderList.Approved, OrderDetailsQuery.ItemNumber, OrderDetailsQuery.Product, OrderDetailsQuery.PricePerUnit, OrderDetailsQuery.QuantityOrdered, OrderDetailsQuery.QuantityShipped, OrderDetailsQuery.BilledSubtotal, OrderDetailsQuery.ShippedSubtotal, OrderDetailsQuery.[Finite Options], OrderDetailsQuery.[Freeform Options], OrderDetailsQuery.SKU, OrderDetailsQuery.Option01, OrderDetailsQuery.Option02, OrderDetailsQuery.Option03, OrderDetailsQuery.Option04, OrderDetailsQuery.Option05, OrderDetailsQuery.Option06, OrderDetailsQuery.Option07, OrderDetailsQuery.Option08, OrderDetailsQuery.Option09, OrderDetailsQuery.Option10, OrderDetailsQuery.Backordered, OrderDetailsQuery.Adjustment, TempOrderList.ManualOrder, QuantityShipped*PricePerUnit, OrderDetailsQuery.ExpectedShipDate, ([QuantityOrdered]-[QuantityShipped])*[PricePerUnit], OrdersQuery.OrderNumber, OrdersQuery.OrderDate, OrdersQuery.Name, OrdersQuery.BalanceDue, OrdersQuery.FinalProductTotal, OrdersQuery.SourceOrderNumber, OrdersQuery.SourceOrderID;
``````
0

Commented:
If you want my help, present the query so it's readable - line by line - as I posted above.
0

Author Commented:
A successful query is created using a combination of the above advice:

Sum(IIf(IsNull([ExpectedShipDate]),0,([OrderDetailsQuery.QuantityOrdered]-[OrderDetailsQuery.QuantityShipped])*OrderDetailsQuery.[PricePerUnit])) AS FutureShipTotal
Sum(IIf(IsNull([ExpectedShipDate]),(OrderDetailsQuery.[QuantityOrdered]-OrderDetailsQuery.[QuantityShipped])*OrderDetailsQuery.[PricePerUnit],0)) AS ShipNowTotal
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.