Solved

Creating a query calculation depending upon date value

Posted on 2009-04-03
10
512 Views
Last Modified: 2012-05-06
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
Comment
Question by:greatcomputing
10 Comments
 
LVL 5

Assisted Solution

by:ducky801
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

by:GRayL
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

by:hnasr
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:greatcomputing
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

by:GRayL
ID: 24077812
Lets see the whole query please.
0
 

Author Comment

by:greatcomputing
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

by:GRayL
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, 
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
 

Author Comment

by:greatcomputing
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;

Open in new window

0
 
LVL 44

Expert Comment

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

Accepted Solution

by:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views 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 Access…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question