Solved

Creating a query calculation depending upon date value

Posted on 2009-04-03
513 Views
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
Question by:greatcomputing
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 5

Assisted Solution

ducky801 earned 50 total points
ID: 24063504
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

LVL 44

Assisted Solution

GRayL earned 75 total points
ID: 24064236
SELECT
Sum(iif(ExpectedShipDate, [QuantityOrdered]-[QuantityShipped])*[PricePerUnit],0) AS TotalB,
Sum(iif(Not ExpectedShipDate, [QuantityOrdered]-[QuantityShipped])*[PricePerUnit],0) AS TotalB
FROM myTable;

0

LVL 30

Expert Comment

ID: 24064440
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 Comment

ID: 24077425
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

LVL 44

Expert Comment

ID: 24077812
Lets see the whole query please.
0

Author Comment

ID: 24080688
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

LVL 44

Assisted Solution

GRayL earned 75 total points
ID: 24081273
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 Comment

ID: 24097310
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

LVL 44

Expert Comment

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

Accepted Solution

greatcomputing earned 0 total points
ID: 24281947
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

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses
Course of the Month2 days, 21 hours left to enroll