Solved

Creating a query calculation depending upon date value

Posted on 2009-04-03
10
510 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
 

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now