Query needing field containing query 1

[TrekBiker]

I have these fields in a query giving a customer's orders by Delivery Date
OrderID
CustomerID
OrderVolume
DeliveryDate
LoyaltyWeeks
StartDate

StartDate is calculated as DateAdd("d",-[LoyaltyWeeks]*7,[DeliveryDate])

For any new order a loyalty scheme will give them a discount if the total volumes bought from StartDate up to the new order's DeliveryDate exceed a target value.

To begin the calculation I need to sum OrderVolume for all orders between StartDate and DeliveryDate.

Is this possible?

 

[sxschech]

Maybe with dsum something like:
[tt]
SumVolume:dsum("ordervolume","TABLE_OR_QUERY_NAME","StartDate between StartDate and DeliveryDate")
[/tt]

 

[TrekBiker]

Thanks sxschech, still not clear about this.

What I'm trying to do is add a field to the query's output to give for each OrderID the sum of every OrderVolume in the query's output that has a DeliveryDate between the DeliveryDate and StartDate for that OrderID

As an example say this is the query data for a particular customer

OrderID,CustomerID,OrderVolume,DeliveryDate,StartDate
129775, 2, 3, 01/05/17, 01/03/17
129700, 2, 1, 25/04/17, 25/02/17
129656, 2, 2, 21/03/17, 21/01/17
129603, 2, 2, 28/02/17, 28/12/16
129550, 2, 1, 27/12/16, 27/10/16

I need the final output to include a TotalVolume field that calculates like this
OrderID, CustomerID, TotalVolume
129775, 2, 6
129700, 2, 5
129656, 2, 4
129603, 2, 2
129550, 2, 1

 

[MajP]

That is an aggregate query where you group on OrderID, CustomerID, and Sum order volume. Filter on delivery date >= startDate. To make it simple I would make that query and then join it to your main query by CustomerID and OrderID. However, not sure if that is what you originally where asking. If you do not know how to do aggregate queries you can Google examples. Here is one

https://www.youtube.com/watch?v=60o0hRUfBHs

 

[TrekBiker]

Thanks but not clear how I could use a simple aggregate query because for a given Customer ID every row is different so Group By only works for Customer ID. Also, the DeliveryDate with a criterion on it isn't permitted. Or am I missing something?

 

[MajP]

Now that I see what you are doing you need a subqery

SELECT 
 A.OrderID, 
 A.CustomerID, 
 A.OrderVolume, 
 A.DeliveryDate, 
 A.StartDate, 
(SELECT  Sum(B.OrderVolume) 
    FROM TblOrders As B
    WHERE (A.DeliveryDate >= B.DeliveryDate AND A.StartDate <= B.DeliveryDate)
    GROUP BY B.CustomerID) AS TotalOrders
FROM 
 TblOrders AS A
ORDER BY 
 A.DeliveryDate;

 

[TrekBiker]

Thanks MajP, this definitely looks the right way to go about it. Have never used subqueries so will need to spend some time understanding them.

Your SQL produces exactly what I want if the starting table tblOrders has just one CustomerID. But in reality it will have multiple customers so will look something like this.

OrderID,CustomerID,OrderVolume,DeliveryDate,StartDate
129775, 2, 3, 01/05/17, 01/03/17
129700, 2, 1, 25/04/17, 25/02/17
129656, 2, 2, 21/03/17, 21/01/17
129603, 2, 2, 28/02/17, 28/12/16
129550, 2, 1, 27/12/16, 27/10/16
128775, 3, 3, 01/05/17, 01/03/17
128700, 3, 1, 25/04/17, 25/02/17
128656, 3, 2, 21/03/17, 21/01/17
128603, 3, 2, 28/02/17, 28/12/16
128550, 3, 1, 27/12/16, 27/10/16
127775, 4, 3, 01/05/17, 01/03/17
127700, 4, 1, 25/04/17, 25/02/17
127656, 4, 2, 21/03/17, 21/01/17
127603, 4, 2, 28/02/17, 28/12/16
127550, 4, 1, 27/12/16, 27/10/16
etc

I get an error message 'At most one record can be returned by this subquery' and it then shows a single row with #Name? in every column. What needs changing?

 

[TrekBiker]

It'll be a bonus if it's possible to avoid the message 'At most one record can be returned by this subquery' but I can get round it by working customer by customer.

Many thanks for your valuable assistance, it allows me to make huge progress. I'm updating someone else's database design and it's a case of 'if I wanted to get there I wouldn't start from here'.

 

[MajP]

SELECT A.OrderID, A.CustomerID, A.OrderVolume, A.DeliveryDate, A.StartDate, (SELECT  Sum(B.OrderVolume) 
     FROM TblOrders As B
    WHERE (A.DeliveryDate >= B.DeliveryDate AND A.StartDate <= B.DeliveryDate AND A.CustomerID = B.customerID)
   GROUP BY B.CustomerID) AS TotalOrders
FROM TblOrders AS A
ORDER BY A.CustomerID, A.DeliveryDate;

 

[dhookom]

TrekBiker,
You have been using these forums for many years and should have discovered the ease of using TGML to format your posts. The following is much easier for others to read and understand using the Pre tag:

[pre]
OrderID CustomerID OrderVolume DeliveryDate StartDate
129775 2 3 01/05/17 01/03/17
129700 2 1 25/04/17 25/02/17
129656 2 2 21/03/17 21/01/17
129603 2 2 28/02/17 28/12/16
129550 2 1 27/12/16 27/10/16
128775 3 3 01/05/17 01/03/17
128700 3 1 25/04/17 25/02/17
128656 3 2 21/03/17 21/01/17
128603 3 2 28/02/17 28/12/16
128550 3 1 27/12/16 27/10/16
127775 4 3 01/05/17 01/03/17
127700 4 1 25/04/17 25/02/17
127656 4 2 21/03/17 21/01/17
127603 4 2 28/02/17 28/12/16
127550 4 1 27/12/16 27/10/16[/pre]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thanks for that Duane, that makes it much more presentable. Will mend my ways for next time.

 

[MajP]

Untested, but I think you may have to add to the where clause
... AND A.CustomerID = B.CustomerID