rolling time between dates

  • Thread starter Thread starter mike
  • Start date Start date
M

mike

Hi. I have a report that shows me shipments of customer
orders. They're grouped by customer. Is there a way to
introduce a control that calculates the time between
orders on the report? Any thoughts would be great. Thanks!
 
The report is grouped by customers and the detail looks
like this:

Date Amount
10/05/03 500
10/07/03 200
11/01/03 400
11/07/03 100

I'm trying to do this:

Date Amount DaysElapsed
10/05/03 500
10/07/03 200 2
11/01/03 400 25
11/07/03 100 7
 
mike said:
The report is grouped by customers and the detail looks
like this:

Date Amount
10/05/03 500
10/07/03 200
11/01/03 400
11/07/03 100

I'm trying to do this:

Date Amount DaysElapsed
10/05/03 500
10/07/03 200 2
11/01/03 400 25
11/07/03 100 7
Click to expand...


This kind of thing should be done in the report's record
source query using a subquery. Here's a simple example
query:

SELECT T.Customer, T.OrderDate, T.Amount,
T.OrderDate - (SELECT Max(X.Date)
FROM table AS X
WHERE X.OrderDate < T.OrderDatE
AND C.Customer = T.Customer
) AS DaysElapsed
FROM table
 
Thanks. What would X and C represent. The report is based
off a query called qryCourseLocationAccountsOrders so I'm
using that as T in the example you gave me. What would I
use for X and for C?
 
mike said:
Thanks. What would X and C represent. The report is based
off a query called qryCourseLocationAccountsOrders so I'm
using that as T in the example you gave me. What would I
use for X and for C?
Click to expand...

Sorry, the C is a typo, it should be an X. I also left out
the T in the main query's FROM clause.

T and X are alias names for qryCourseLocationAccountsOrders
in each respective query. This is necessary because both
the main query and the subquery have the same FROM clause.
The subquery's WHERE clause needs to refer to the same
fields in both queries and without the alias names you could
not distinguish which field name was in the main query and
which one was in the subquery. I've tried to clean it up
some:

SELECT T.Customer, T.OrderDate, T.Amount,
T.OrderDate - (SELECT Max(X.Date)
FROM qryCourseLocationAccountsOrders AS X
WHERE X.OrderDate < T.OrderDatE
AND X.Customer = T.Customer
) AS DaysElapsed
FROM qryCourseLocationAccountsOrders AS T

the T refers to qryCourseLocationAccountsOrders
in the main query and the X refers to
qryCourseLocationAccountsOrders in the subquery.
--
Marsh
MVP [MS Access]


 
Marshall, Thanks for hanging in there on this. Learning
how to do this is going to be a real help. Looks like I'm
basically there except for one exception. It's definately
counting days between orders which is GREAT, but it looks
like it's not including all the records from the data
source. There are 189 records in
qryCourseLocationAccountsOrders. When I was grouping on
[Customer Name] there were 109 records showing up in the
query's results and when I group by SAPNumber as shown
below, I get 53 records. Do you see something in my code
that would limit the records returned? Thanks again. This
is VERY helpful!

The record source qryCourseLocationAccountsOrders is in
the query's design view and its alias goes by the same
name. Do I have to change the alias to X?

SAPNumber is the customer account number and
DollarsShipped is just how much the order was for.
Sometimes account names can be the same so I'm actually
doing this by account number which I should have done in
the example. Sorry about that. This is what I have:

SELECT qryCourseLocationAccountsOrders.SAPNumber,
qryCourseLocationAccountsOrders.[Customer Name],
qryCourseLocationAccountsOrders.InvoiceDate,
qryCourseLocationAccountsOrders.DollarsShipped,
qryCourseLocationAccountsOrders.InvoiceDate-(SELECT Max
(X.InvoiceDate)
FROM qryCourseLocationAccountsOrders AS X
WHERE X.InvoiceDate <
qryCourseLocationAccountsOrders.InvoiceDate AND X.
[SAPNumber] = qryCourseLocationAccountsOrders.[SAPNumber])
AS DaysElapsed
FROM qryCourseLocationAccountsOrders
ORDER BY qryCourseLocationAccountsOrders.SAPNumber,
qryCourseLocationAccountsOrders.InvoiceDate;

-----Original Message-----
mike said:
Thanks. What would X and C represent. The report is based
off a query called qryCourseLocationAccountsOrders so I'm
using that as T in the example you gave me. What would I
use for X and for C?
Click to expand...

Sorry, the C is a typo, it should be an X. I also left out
the T in the main query's FROM clause.

T and X are alias names for qryCourseLocationAccountsOrders
in each respective query. This is necessary because both
the main query and the subquery have the same FROM clause.
The subquery's WHERE clause needs to refer to the same
fields in both queries and without the alias names you could
not distinguish which field name was in the main query and
which one was in the subquery. I've tried to clean it up
some:

SELECT T.Customer, T.OrderDate, T.Amount,
T.OrderDate - (SELECT Max(X.Date)
FROM qryCourseLocationAccountsOrders AS X
WHERE X.OrderDate < T.OrderDatE
AND X.Customer = T.Customer
) AS DaysElapsed
FROM qryCourseLocationAccountsOrders AS T

the T refers to qryCourseLocationAccountsOrders
in the main query and the X refers to
qryCourseLocationAccountsOrders in the subquery.
--
Marsh
MVP [MS Access]



.
Click to expand...
 
mike said:
Marshall, Thanks for hanging in there on this. Learning
how to do this is going to be a real help. Looks like I'm
basically there except for one exception. It's definately
counting days between orders which is GREAT,
Click to expand...

I sort of cheated on the date calculation. It won't make a
difference in a strictly Access/Jet database, but to be
rigorous I shouldn't just subtract the two dates, but use
the DateDiff function instead.

but it looks
like it's not including all the records from the data
source. There are 189 records in
qryCourseLocationAccountsOrders. When I was grouping on
[Customer Name] there were 109 records showing up in the
query's results and when I group by SAPNumber as shown
below, I get 53 records. Do you see something in my code
that would limit the records returned? Thanks again. This
is VERY helpful!
Click to expand...

I don't see a GROUP BY clause in the query below. Are you
saying that qryCourseLocationAccountsOrders is a GROUP BY
query? If so, it will only return records with unique
values in the grouped fields. How does the number of
records in the final query compare to the number of records
returned by qryCourseLocationAccountsOrders when it is
grouped by the SAPNumber field?

Maybe what you mean by grouped is that you're restricting
the subquery's latest date to SAPNumber instead of customer.
If so, it sounds like a good idea, but I don't know enough
about what you're doing to understand the implications of
this change to knw for sure. Regardless, I don't see how
this could affect the number of records returned by the
query.

The record source qryCourseLocationAccountsOrders is in
the query's design view and its alias goes by the same
name. Do I have to change the alias to X?
Click to expand...

No, definitely not X, we're using X in the subquery. The
way I had it originally, the alias would have been set to T.

SAPNumber is the customer account number and
DollarsShipped is just how much the order was for.
Sometimes account names can be the same so I'm actually
doing this by account number which I should have done in
the example. Sorry about that. This is what I have:

SELECT qryCourseLocationAccountsOrders.SAPNumber,
qryCourseLocationAccountsOrders.[Customer Name],
qryCourseLocationAccountsOrders.InvoiceDate,
qryCourseLocationAccountsOrders.DollarsShipped,
qryCourseLocationAccountsOrders.InvoiceDate-(SELECT Max
(X.InvoiceDate)
FROM qryCourseLocationAccountsOrders AS X
WHERE X.InvoiceDate <
qryCourseLocationAccountsOrders.InvoiceDate AND X.
[SAPNumber] = qryCourseLocationAccountsOrders.[SAPNumber])
AS DaysElapsed
FROM qryCourseLocationAccountsOrders
ORDER BY qryCourseLocationAccountsOrders.SAPNumber,
qryCourseLocationAccountsOrders.InvoiceDate;
Click to expand...
 
Thanks, Marshall. I knew I should have said OrderBy
instead of GroupBy. You're right in your assumption. I
meant they're just ordered by SAPNumber. Nothing is
grouped. By they way, what I'm trying to find out is how
many days elapse between orders at each account so that we
can improve our planning process. Your suggestions is
exactly what I'm looking for and it's sooooo close. The
only conundrum is figuring out why it's not returning all
the records from the recordset.

This is just an Access database so I think the date
calculation is working okay. If I see something odd, I'll
switch to datediff.

There are 189 records in the record source
qryCourseLocationAccountsOrders. When I group
qryCourseLocationAccountsOrders by SAPNumber I get 129.
When I run the query we've contsructed (which is ordered
by SAPNumber but not grouped) I get 49 records
only...wait, this time I got 180??? Weird. I'll keep
after. This is a great start and further than I've ever
come with this technique, so thanks a hundred times over
for the help. I appreciate it.


-----Original Message-----
mike said:
Marshall, Thanks for hanging in there on this. Learning
how to do this is going to be a real help. Looks like I'm
basically there except for one exception. It's definately
counting days between orders which is GREAT,
Click to expand...

I sort of cheated on the date calculation. It won't make a
difference in a strictly Access/Jet database, but to be
rigorous I shouldn't just subtract the two dates, but use
the DateDiff function instead.

but it looks
like it's not including all the records from the data
source. There are 189 records in
qryCourseLocationAccountsOrders. When I was grouping on
[Customer Name] there were 109 records showing up in the
query's results and when I group by SAPNumber as shown
below, I get 53 records. Do you see something in my code
that would limit the records returned? Thanks again. This
is VERY helpful!
Click to expand...

I don't see a GROUP BY clause in the query below. Are you
saying that qryCourseLocationAccountsOrders is a GROUP BY
query? If so, it will only return records with unique
values in the grouped fields. How does the number of
records in the final query compare to the number of records
returned by qryCourseLocationAccountsOrders when it is
grouped by the SAPNumber field?

Maybe what you mean by grouped is that you're restricting
the subquery's latest date to SAPNumber instead of customer.
If so, it sounds like a good idea, but I don't know enough
about what you're doing to understand the implications of
this change to knw for sure. Regardless, I don't see how
this could affect the number of records returned by the
query.

The record source qryCourseLocationAccountsOrders is in
the query's design view and its alias goes by the same
name. Do I have to change the alias to X?
Click to expand...

No, definitely not X, we're using X in the subquery. The
way I had it originally, the alias would have been set to T.
SAPNumber is the customer account number and
DollarsShipped is just how much the order was for.
Sometimes account names can be the same so I'm actually
doing this by account number which I should have done in
the example. Sorry about that. This is what I have:

SELECT qryCourseLocationAccountsOrders.SAPNumber,
qryCourseLocationAccountsOrders.[Customer Name],
qryCourseLocationAccountsOrders.InvoiceDate,
qryCourseLocationAccountsOrders.DollarsShipped,
qryCourseLocationAccountsOrders.InvoiceDate-(SELECT Max
(X.InvoiceDate)
FROM qryCourseLocationAccountsOrders AS X
WHERE X.InvoiceDate <
qryCourseLocationAccountsOrders.InvoiceDate AND X.
[SAPNumber] = qryCourseLocationAccountsOrders. [SAPNumber])
AS DaysElapsed
FROM qryCourseLocationAccountsOrders
ORDER BY qryCourseLocationAccountsOrders.SAPNumber,
qryCourseLocationAccountsOrders.InvoiceDate;
Click to expand...
Click to expand...
 
mike said:
Thanks, Marshall. I knew I should have said OrderBy
instead of GroupBy. You're right in your assumption. I
meant they're just ordered by SAPNumber. Nothing is
grouped. By they way, what I'm trying to find out is how
many days elapse between orders at each account so that we
can improve our planning process. Your suggestions is
exactly what I'm looking for and it's sooooo close. The
only conundrum is figuring out why it's not returning all
the records from the recordset.

This is just an Access database so I think the date
calculation is working okay. If I see something odd, I'll
switch to datediff.

There are 189 records in the record source
qryCourseLocationAccountsOrders. When I group
qryCourseLocationAccountsOrders by SAPNumber I get 129.
When I run the query we've contsructed (which is ordered
by SAPNumber but not grouped) I get 49 records
only...wait, this time I got 180??? Weird. I'll keep
after. This is a great start and further than I've ever
come with this technique, so thanks a hundred times over
for the help. I appreciate it.
Click to expand...

You are just running the query, not the entire report,
right? Be aware that sub query's can be slow (but I doubt
that a few hundred records would be noticeable unless the
qryCourseLocationAccountsOrders query is really complex) and
the query's sheet view may be a little slow in displaying
all the records. Generally you should click on the last
record navigation button before getting an accurate count of
the records returned by the query..

If you're running the entire report, then I can not imagine
why the number of records would vary from one run to the
next.
--
Marsh
MVP [MS Access]


-----Original Message-----
mike said:
Marshall, Thanks for hanging in there on this. Learning
how to do this is going to be a real help. Looks like I'm
basically there except for one exception. It's definately
counting days between orders which is GREAT,
Click to expand...

I sort of cheated on the date calculation. It won't make a
difference in a strictly Access/Jet database, but to be
rigorous I shouldn't just subtract the two dates, but use
the DateDiff function instead.

but it looks
like it's not including all the records from the data
source. There are 189 records in
qryCourseLocationAccountsOrders. When I was grouping on
[Customer Name] there were 109 records showing up in the
query's results and when I group by SAPNumber as shown
below, I get 53 records. Do you see something in my code
that would limit the records returned? Thanks again. This
is VERY helpful!
Click to expand...

I don't see a GROUP BY clause in the query below. Are you
saying that qryCourseLocationAccountsOrders is a GROUP BY
query? If so, it will only return records with unique
values in the grouped fields. How does the number of
records in the final query compare to the number of records
returned by qryCourseLocationAccountsOrders when it is
grouped by the SAPNumber field?

Maybe what you mean by grouped is that you're restricting
the subquery's latest date to SAPNumber instead of customer.
If so, it sounds like a good idea, but I don't know enough
about what you're doing to understand the implications of
this change to knw for sure. Regardless, I don't see how
this could affect the number of records returned by the
query.

The record source qryCourseLocationAccountsOrders is in
the query's design view and its alias goes by the same
name. Do I have to change the alias to X?
Click to expand...

No, definitely not X, we're using X in the subquery. The
way I had it originally, the alias would have been set to T.
SAPNumber is the customer account number and
DollarsShipped is just how much the order was for.
Sometimes account names can be the same so I'm actually
doing this by account number which I should have done in
the example. Sorry about that. This is what I have:

SELECT qryCourseLocationAccountsOrders.SAPNumber,
qryCourseLocationAccountsOrders.[Customer Name],
qryCourseLocationAccountsOrders.InvoiceDate,
qryCourseLocationAccountsOrders.DollarsShipped,
qryCourseLocationAccountsOrders.InvoiceDate-(SELECT Max
(X.InvoiceDate)
FROM qryCourseLocationAccountsOrders AS X
WHERE X.InvoiceDate <
qryCourseLocationAccountsOrders.InvoiceDate AND X.
[SAPNumber] = qryCourseLocationAccountsOrders. [SAPNumber])
AS DaysElapsed
FROM qryCourseLocationAccountsOrders
ORDER BY qryCourseLocationAccountsOrders.SAPNumber,
qryCourseLocationAccountsOrders.InvoiceDate;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top