Calculating diff between dates on different records on a report

  • Thread starter Thread starter JRW
  • Start date Start date
J

JRW

I am creating a report that will be sorted by Customer then Date and will
have a Customer header. How can I calculate the number of days since I was
last at the customer and have it displayed on the report. Desired

Customer Date DaysSinceLast
Smith 1/1/04 Null
Smith 1/5/04 4

Jones 1/6/04 Null
Jones 1/11/04 5
Jones 1/20/04 9

Jeff
 
JRW said:
I am creating a report that will be sorted by Customer then Date and will
have a Customer header. How can I calculate the number of days since I was
last at the customer and have it displayed on the report. Desired

Customer Date DaysSinceLast
Smith 1/1/04 Null
Smith 1/5/04 4

Jones 1/6/04 Null
Jones 1/11/04 5
Jones 1/20/04 9
Click to expand...


You need to add the "previous" record's date to the current
record in the report's record source query:

SELECT table.*,
(SELECT Max([Date])
FROM table AS X
WHERE X.[Date] < table.[Date]
) AS PrevDate
FROM table

Then the report's DaysSinceLast text box can use the
expression:

=DateDiff("d", PrevDate, [Date])

BTW, I hope that you don't really have a field named Date,
which is the name of a commonly used function. Using the
square brackets **should** prevent confusion in the above
query, but who knows where else that field name may be used.
 
Thanks Marshall,
Being a relative newbie, where exactly do I put the select statement? Does
it go in the data control source of a text field on the report? or in the
query itself as another field.

My date is actually named [VisitDate] and comes from a query named "MVRqry"
so should the statement be as follows?:

SELECT MVRqry.*,
(SELECT Max([VisitDate])
FROM MVRqry AS X
WHERE X.[VisitDate] < MVRqry.[VisitDate]
) AS PrevDate
FROM MVRqry

Thanks for your guidance!


Marshall Barton said:
JRW said:
I am creating a report that will be sorted by Customer then Date and will
have a Customer header. How can I calculate the number of days since I was
last at the customer and have it displayed on the report. Desired

Customer Date DaysSinceLast
Smith 1/1/04 Null
Smith 1/5/04 4

Jones 1/6/04 Null
Jones 1/11/04 5
Jones 1/20/04 9
Click to expand...


You need to add the "previous" record's date to the current
record in the report's record source query:

SELECT table.*,
(SELECT Max([Date])
FROM table AS X
WHERE X.[Date] < table.[Date]
) AS PrevDate
FROM table

Then the report's DaysSinceLast text box can use the
expression:

=DateDiff("d", PrevDate, [Date])

BTW, I hope that you don't really have a field named Date,
which is the name of a commonly used function. Using the
square brackets **should** prevent confusion in the above
query, but who knows where else that field name may be used.
Click to expand...
 
JRW said:
Being a relative newbie, where exactly do I put the select statement? Does
it go in the data control source of a text field on the report? or in the
query itself as another field.
Click to expand...

As I tried to indicate before, the new query should be used
as the report's Record Source.

(Note that SQL statements can not be used as a control
source.)

My date is actually named [VisitDate] and comes from a query named "MVRqry"
so should the statement be as follows?:

SELECT MVRqry.*,
(SELECT Max([VisitDate])
FROM MVRqry AS X
WHERE X.[VisitDate] < MVRqry.[VisitDate]
) AS PrevDate
FROM MVRqry
Click to expand...


I'm glad to hear that your field isn't named Date and, yes,
you have to replace any generic names with your actual
names.

I just noticed that I failed to include the customer in the
subquery's Where clause, so it really should be more like:

SELECT MVRqry.*,
(SELECT Max([VisitDate])
FROM MVRqry AS X
WHERE X.[VisitDate] < MVRqry.[VisitDate]
AND X.CustomerID = MVRqry.CustomerID
) AS PrevDate
FROM MVRqry

After you create this new query, run it from the query
design window to make sure that it's producing the corrrect
data. Then save the query and use it as the basis for the
report instead of MVRqry.
--
Marsh
MVP [MS Access]


JRW said:
I am creating a report that will be sorted by Customer then Date and will
have a Customer header. How can I calculate the number of days since I was
last at the customer and have it displayed on the report. Desired

Customer Date DaysSinceLast
Smith 1/1/04 Null
Smith 1/5/04 4

Jones 1/6/04 Null
Jones 1/11/04 5
Jones 1/20/04 9
Click to expand...

"Marshall Barton" wrote
You need to add the "previous" record's date to the current
record in the report's record source query:

SELECT table.*,
(SELECT Max([Date])
FROM table AS X
WHERE X.[Date] < table.[Date]
) AS PrevDate
FROM table

Then the report's DaysSinceLast text box can use the
expression:

=DateDiff("d", PrevDate, [Date])

BTW, I hope that you don't really have a field named Date,
which is the name of a commonly used function. Using the
square brackets **should** prevent confusion in the above
query, but who knows where else that field name may be used.
Click to expand...
Click to expand...
 
Please keep the correspondence in the newsgroups.

I think that message indicates that your query is more
complicated than you've told me about. It probably has a
Group By clause.

I wouldn't want to speculate any further without seeing the
query that you are actually using.
--
Marsh
MVP [MS Access]




Thanks Marshall,

I ran the query as you indicate but get the following error message.

"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field."Marshall Barton wrote:
Click to expand...
JRW said:
Being a relative newbie, where exactly do I put the select statement? Does
it go in the data control source of a text field on the report? or in the
query itself as another field.
Click to expand...

As I tried to indicate before, the new query should be used
as the report's Record Source.

(Note that SQL statements can not be used as a control
source.)

My date is actually named [VisitDate] and comes from a query named "MVRqry"
so should the statement be as follows?:

SELECT MVRqry.*,
(SELECT Max([VisitDate])
FROM MVRqry AS X
WHERE X.[VisitDate] < MVRqry.[VisitDate]
) AS PrevDate
FROM MVRqry
Click to expand...


I'm glad to hear that your field isn't named Date and, yes,
you have to replace any generic names with your actual
names.

I just noticed that I failed to include the customer in the
subquery's Where clause, so it really should be more like:

SELECT MVRqry.*,
(SELECT Max([VisitDate])
FROM MVRqry AS X
WHERE X.[VisitDate] < MVRqry.[VisitDate]
AND X.CustomerID = MVRqry.CustomerID
) AS PrevDate
FROM MVRqry

After you create this new query, run it from the query
design window to make sure that it's producing the corrrect
data. Then save the query and use it as the basis for the
report instead of MVRqry.
Click to expand...
 
Back
Top