INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query needing field containing query

Query needing field containing query

(OP)

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?

RE: Query needing field containing query

Maybe with dsum something like:

SumVolume:dsum("ordervolume","TABLE_OR_QUERY_NAME","StartDate between StartDate and DeliveryDate")

RE: Query needing field containing query

(OP)
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



RE: Query needing field containing query

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

RE: Query needing field containing query

(OP)
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?

RE: Query needing field containing query

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

CODE

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; 

RE: Query needing field containing query

(OP)

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?

RE: Query needing field containing query

(OP)
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'.

RE: Query needing field containing query

CODE -->

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; 

RE: Query needing field containing query

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:

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 

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

RE: Query needing field containing query

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

RE: Query needing field containing query

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close