Improve company productivity with a Business Account.Sign Up

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

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
Asked:
greatcomputing
4 Solutions
 
ducky801Commented:
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
 
GRayLCommented:
SELECT
Sum(iif(ExpectedShipDate, [QuantityOrdered]-[QuantityShipped])*[PricePerUnit],0) AS TotalB,
Sum(iif(Not ExpectedShipDate, [QuantityOrdered]-[QuantityShipped])*[PricePerUnit],0) AS TotalB
FROM myTable;

0
 
hnasrCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
greatcomputingAuthor 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
 
GRayLCommented:
Lets see the whole query please.
0
 
greatcomputingAuthor 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
 
GRayLCommented:
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, 
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]))
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, 
OrderDetailsQuery.Adjustment, 
TempOrderList.ManualOrder, 
QuantityShipped*PricePerUnit, 
OrderDetailsQuery.ExpectedShipDate, 
([QuantityOrdered]-[QuantityShipped])*[PricePerUnit];

Open in new window

0
 
greatcomputingAuthor 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;

Open in new window

0
 
GRayLCommented:
If you want my help, present the query so it's readable - line by line - as I posted above.  
0
 
greatcomputingAuthor 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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now