calculating consecutive dates in Access 2000

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to count the number of consecutive days a client stays in
serivces. My variables are: client number and a service date.

Thanks
 
Thank you for your response. I have used the datediff function in the past,
however in this querey it will not work because it is possible for clients to
have a break in service and datediff will count the days inbetween. I need a
funtion that only counts consecutive days per each client. something like :
if client number = client number and date =date+1 then datediff. I just
don't know how to do this in access.

Thanks again
 
You'll need to post examples of the data that you're using and examples of
the result that you expect from those data. Sounds as if you want to have
the query "look back" in the data records to find a "start date"?
--

Ken Snell
<MS ACCESS MVP>
 
Here is an example of the data:
Client Number Service date
22 7/1/2002
22 7/2/2002
22 7/3/2002
22 7/4/2002
22 7/5/2002
22 9/9/2003
22 9/10/2003
22 9/11/2003
22 1/1/2005
22 2/10/2005
22 4/1/2005
22 4/2/2005
33 6/1/2003
33 7/1/2003
33 7/2/2003
33 7/3/2003
33 7/4/2003
33 7/5/2003
33 8/5/2005
33 8/6/2005
33 8/7/2005

So, Client #22 would have 5 separate episodes (an episode being one day or a
number of consecutive days of service) the first with 5 consecutive days,
the second with 3 consecutive days and so on....

The output I would hope to have would count the "episodes" and consecutive
days of each.

Does this make sense?
 
Not sure what you mean by "count", but to get the differences in days
between each chronological record, you can use a query like this:

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<" &
[Service date]), [Service date]), [Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];
--

Ken Snell
<MS ACCESS MVP>
 
Thank you I'll try it.

Ken Snell said:
Not sure what you mean by "count", but to get the differences in days
between each chronological record, you can use a query like this:

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<" &
[Service date]), [Service date]), [Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];
--

Ken Snell
<MS ACCESS MVP>



Analyst 1 said:
Here is an example of the data:
Client Number Service date
22 7/1/2002
22 7/2/2002
22 7/3/2002
22 7/4/2002
22 7/5/2002
22 9/9/2003
22 9/10/2003
22 9/11/2003
22 1/1/2005
22 2/10/2005
22 4/1/2005
22 4/2/2005
33 6/1/2003
33 7/1/2003
33 7/2/2003
33 7/3/2003
33 7/4/2003
33 7/5/2003
33 8/5/2005
33 8/6/2005
33 8/7/2005

So, Client #22 would have 5 separate episodes (an episode being one day or
a
number of consecutive days of service) the first with 5 consecutive days,
the second with 3 consecutive days and so on....

The output I would hope to have would count the "episodes" and consecutive
days of each.

Does this make sense?
Click to expand...
Click to expand...
 
Hi Ken,

The SQL you sent me returned "0" for dayssincelast .

Analyst 1 said:
Thank you I'll try it.

Ken Snell said:
Not sure what you mean by "count", but to get the differences in days
between each chronological record, you can use a query like this:

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<" &
[Service date]), [Service date]), [Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];
--

Ken Snell
<MS ACCESS MVP>



Analyst 1 said:
Here is an example of the data:
Client Number Service date
22 7/1/2002
22 7/2/2002
22 7/3/2002
22 7/4/2002
22 7/5/2002
22 9/9/2003
22 9/10/2003
22 9/11/2003
22 1/1/2005
22 2/10/2005
22 4/1/2005
22 4/2/2005
33 6/1/2003
33 7/1/2003
33 7/2/2003
33 7/3/2003
33 7/4/2003
33 7/5/2003
33 8/5/2005
33 8/6/2005
33 8/7/2005

So, Client #22 would have 5 separate episodes (an episode being one day or
a
number of consecutive days of service) the first with 5 consecutive days,
the second with 3 consecutive days and so on....

The output I would hope to have would count the "episodes" and consecutive
days of each.

Does this make sense?

:

You'll need to post examples of the data that you're using and examples
of
the result that you expect from those data. Sounds as if you want to have
the query "look back" in the data records to find a "start date"?
--

Ken Snell
<MS ACCESS MVP>



Thank you for your response. I have used the datediff function in the
past,
however in this querey it will not work because it is possible for
clients
to
have a break in service and datediff will count the days inbetween. I
need a
funtion that only counts consecutive days per each client. something
like
:
if client number = client number and date =date+1 then datediff. I
just
don't know how to do this in access.

Thanks again

:

Check out the DateDiff function.

--

Ken Snell
<MS ACCESS MVP>

I am trying to count the number of consecutive days a client stays in
serivces. My variables are: client number and a service date.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Let's try this (I had omitted the # delimiter for date, and I'm now
formatting the date into the US format):

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<#" &
Format([Service date],"m/d/yyyy") & "#"), [Service date]),
[Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];


--

Ken Snell
<MS ACCESS MVP>

Analyst 1 said:
Hi Ken,

The SQL you sent me returned "0" for dayssincelast .

Analyst 1 said:
Thank you I'll try it.

Ken Snell said:
Not sure what you mean by "count", but to get the differences in days
between each chronological record, you can use a query like this:

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<" &
[Service date]), [Service date]), [Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];
--

Ken Snell
<MS ACCESS MVP>



Here is an example of the data:
Client Number Service date
22 7/1/2002
22 7/2/2002
22 7/3/2002
22 7/4/2002
22 7/5/2002
22 9/9/2003
22 9/10/2003
22 9/11/2003
22 1/1/2005
22 2/10/2005
22 4/1/2005
22 4/2/2005
33 6/1/2003
33 7/1/2003
33 7/2/2003
33 7/3/2003
33 7/4/2003
33 7/5/2003
33 8/5/2005
33 8/6/2005
33 8/7/2005

So, Client #22 would have 5 separate episodes (an episode being one
day or
a
number of consecutive days of service) the first with 5 consecutive
days,
the second with 3 consecutive days and so on....

The output I would hope to have would count the "episodes" and
consecutive
days of each.

Does this make sense?

:

You'll need to post examples of the data that you're using and
examples
of
the result that you expect from those data. Sounds as if you want to
have
the query "look back" in the data records to find a "start date"?
--

Ken Snell
<MS ACCESS MVP>



Thank you for your response. I have used the datediff function in
the
past,
however in this querey it will not work because it is possible for
clients
to
have a break in service and datediff will count the days
inbetween. I
need a
funtion that only counts consecutive days per each client.
something
like
:
if client number = client number and date =date+1 then datediff.
I
just
don't know how to do this in access.

Thanks again

:

Check out the DateDiff function.

--

Ken Snell
<MS ACCESS MVP>

I am trying to count the number of consecutive days a client
stays in
serivces. My variables are: client number and a service date.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
I get an error message that indicates an invalid date format.

Ken Snell said:
Let's try this (I had omitted the # delimiter for date, and I'm now
formatting the date into the US format):

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<#" &
Format([Service date],"m/d/yyyy") & "#"), [Service date]),
[Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];


--

Ken Snell
<MS ACCESS MVP>

Analyst 1 said:
Hi Ken,

The SQL you sent me returned "0" for dayssincelast .

Analyst 1 said:
Thank you I'll try it.

:

Not sure what you mean by "count", but to get the differences in days
between each chronological record, you can use a query like this:

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<" &
[Service date]), [Service date]), [Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];
--

Ken Snell
<MS ACCESS MVP>



Here is an example of the data:
Client Number Service date
22 7/1/2002
22 7/2/2002
22 7/3/2002
22 7/4/2002
22 7/5/2002
22 9/9/2003
22 9/10/2003
22 9/11/2003
22 1/1/2005
22 2/10/2005
22 4/1/2005
22 4/2/2005
33 6/1/2003
33 7/1/2003
33 7/2/2003
33 7/3/2003
33 7/4/2003
33 7/5/2003
33 8/5/2005
33 8/6/2005
33 8/7/2005

So, Client #22 would have 5 separate episodes (an episode being one
day or
a
number of consecutive days of service) the first with 5 consecutive
days,
the second with 3 consecutive days and so on....

The output I would hope to have would count the "episodes" and
consecutive
days of each.

Does this make sense?

:

You'll need to post examples of the data that you're using and
examples
of
the result that you expect from those data. Sounds as if you want to
have
the query "look back" in the data records to find a "start date"?
--

Ken Snell
<MS ACCESS MVP>



Thank you for your response. I have used the datediff function in
the
past,
however in this querey it will not work because it is possible for
clients
to
have a break in service and datediff will count the days
inbetween. I
need a
funtion that only counts consecutive days per each client.
something
like
:
if client number = client number and date =date+1 then datediff.
I
just
don't know how to do this in access.

Thanks again

:

Check out the DateDiff function.

--

Ken Snell
<MS ACCESS MVP>

I am trying to count the number of consecutive days a client
stays in
serivces. My variables are: client number and a service date.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
What is the data type for Servicedate field in the table?

--

Ken Snell
<MS ACCESS MVP>

Analyst 1 said:
I get an error message that indicates an invalid date format.

Ken Snell said:
Let's try this (I had omitted the # delimiter for date, and I'm now
formatting the date into the US format):

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<#" &
Format([Service date],"m/d/yyyy") & "#"), [Service date]),
[Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];


--

Ken Snell
<MS ACCESS MVP>

Analyst 1 said:
Hi Ken,

The SQL you sent me returned "0" for dayssincelast .

:

Thank you I'll try it.

:

Not sure what you mean by "count", but to get the differences in
days
between each chronological record, you can use a query like this:

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<" &
[Service date]), [Service date]), [Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];
--

Ken Snell
<MS ACCESS MVP>



Here is an example of the data:
Client Number Service date
22 7/1/2002
22 7/2/2002
22 7/3/2002
22 7/4/2002
22 7/5/2002
22 9/9/2003
22 9/10/2003
22 9/11/2003
22 1/1/2005
22 2/10/2005
22 4/1/2005
22 4/2/2005
33 6/1/2003
33 7/1/2003
33 7/2/2003
33 7/3/2003
33 7/4/2003
33 7/5/2003
33 8/5/2005
33 8/6/2005
33 8/7/2005

So, Client #22 would have 5 separate episodes (an episode being
one
day or
a
number of consecutive days of service) the first with 5
consecutive
days,
the second with 3 consecutive days and so on....

The output I would hope to have would count the "episodes" and
consecutive
days of each.

Does this make sense?

:

You'll need to post examples of the data that you're using and
examples
of
the result that you expect from those data. Sounds as if you want
to
have
the query "look back" in the data records to find a "start date"?
--

Ken Snell
<MS ACCESS MVP>



Thank you for your response. I have used the datediff function
in
the
past,
however in this querey it will not work because it is possible
for
clients
to
have a break in service and datediff will count the days
inbetween. I
need a
funtion that only counts consecutive days per each client.
something
like
:
if client number = client number and date =date+1 then
datediff.
I
just
don't know how to do this in access.

Thanks again

:

Check out the DateDiff function.

--

Ken Snell
<MS ACCESS MVP>

message
I am trying to count the number of consecutive days a client
stays in
serivces. My variables are: client number and a service
date.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
It is formatted as date/time.

Ken Snell said:
What is the data type for Servicedate field in the table?

--

Ken Snell
<MS ACCESS MVP>

Analyst 1 said:
I get an error message that indicates an invalid date format.

Ken Snell said:
Let's try this (I had omitted the # delimiter for date, and I'm now
formatting the date into the US format):

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<#" &
Format([Service date],"m/d/yyyy") & "#"), [Service date]),
[Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];


--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

The SQL you sent me returned "0" for dayssincelast .

:

Thank you I'll try it.

:

Not sure what you mean by "count", but to get the differences in
days
between each chronological record, you can use a query like this:

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<" &
[Service date]), [Service date]), [Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];
--

Ken Snell
<MS ACCESS MVP>



Here is an example of the data:
Client Number Service date
22 7/1/2002
22 7/2/2002
22 7/3/2002
22 7/4/2002
22 7/5/2002
22 9/9/2003
22 9/10/2003
22 9/11/2003
22 1/1/2005
22 2/10/2005
22 4/1/2005
22 4/2/2005
33 6/1/2003
33 7/1/2003
33 7/2/2003
33 7/3/2003
33 7/4/2003
33 7/5/2003
33 8/5/2005
33 8/6/2005
33 8/7/2005

So, Client #22 would have 5 separate episodes (an episode being
one
day or
a
number of consecutive days of service) the first with 5
consecutive
days,
the second with 3 consecutive days and so on....

The output I would hope to have would count the "episodes" and
consecutive
days of each.

Does this make sense?

:

You'll need to post examples of the data that you're using and
examples
of
the result that you expect from those data. Sounds as if you want
to
have
the query "look back" in the data records to find a "start date"?
--

Ken Snell
<MS ACCESS MVP>



Thank you for your response. I have used the datediff function
in
the
past,
however in this querey it will not work because it is possible
for
clients
to
have a break in service and datediff will count the days
inbetween. I
need a
funtion that only counts consecutive days per each client.
something
like
:
if client number = client number and date =date+1 then
datediff.
I
just
don't know how to do this in access.

Thanks again

:

Check out the DateDiff function.

--

Ken Snell
<MS ACCESS MVP>

message
I am trying to count the number of consecutive days a client
stays in
serivces. My variables are: client number and a service
date.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
By chance, is Client Number a text field? Assuming that it is, let's try
this SQL statement (am delimiting the Client Number value with ' characters,
and have again omitted the # delimiters for the Service date value):

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]='" &
[Client Number] & "' And [Service date]<" &
[Service date]), [Service date]), [Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];

NOTE: You must replace TableName in the above with the real name of the
table. I am using TableName as a generic name because you didn't post the
real name of the table.




If Client Number is a numeric field, then use this:

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<" &
[Service date]), [Service date]), [Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];

NOTE: You must replace TableName in the above with the real name of the
table. I am using TableName as a generic name because you didn't post the
real name of the table.
--

Ken Snell
<MS ACCESS MVP>

--

Ken Snell
<MS ACCESS MVP>


Analyst 1 said:
It is formatted as date/time.

Ken Snell said:
What is the data type for Servicedate field in the table?

--

Ken Snell
<MS ACCESS MVP>

Analyst 1 said:
I get an error message that indicates an invalid date format.

:

Let's try this (I had omitted the # delimiter for date, and I'm now
formatting the date into the US format):

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<#" &
Format([Service date],"m/d/yyyy") & "#"), [Service date]),
[Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];


--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

The SQL you sent me returned "0" for dayssincelast .

:

Thank you I'll try it.

:

Not sure what you mean by "count", but to get the differences in
days
between each chronological record, you can use a query like this:

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<" &
[Service date]), [Service date]), [Service date])) AS
DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];
--

Ken Snell
<MS ACCESS MVP>



Here is an example of the data:
Client Number Service date
22 7/1/2002
22 7/2/2002
22 7/3/2002
22 7/4/2002
22 7/5/2002
22 9/9/2003
22 9/10/2003
22 9/11/2003
22 1/1/2005
22 2/10/2005
22 4/1/2005
22 4/2/2005
33 6/1/2003
33 7/1/2003
33 7/2/2003
33 7/3/2003
33 7/4/2003
33 7/5/2003
33 8/5/2005
33 8/6/2005
33 8/7/2005

So, Client #22 would have 5 separate episodes (an episode being
one
day or
a
number of consecutive days of service) the first with 5
consecutive
days,
the second with 3 consecutive days and so on....

The output I would hope to have would count the "episodes" and
consecutive
days of each.

Does this make sense?

:

You'll need to post examples of the data that you're using and
examples
of
the result that you expect from those data. Sounds as if you
want
to
have
the query "look back" in the data records to find a "start
date"?
--

Ken Snell
<MS ACCESS MVP>



message
Thank you for your response. I have used the datediff
function
in
the
past,
however in this querey it will not work because it is
possible
for
clients
to
have a break in service and datediff will count the days
inbetween. I
need a
funtion that only counts consecutive days per each client.
something
like
:
if client number = client number and date =date+1 then
datediff.
I
just
don't know how to do this in access.

Thanks again

:

Check out the DateDiff function.

--

Ken Snell
<MS ACCESS MVP>

message
I am trying to count the number of consecutive days a
client
stays in
serivces. My variables are: client number and a service
date.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your help this didn't work either so, I am just going to use
excell to do this.

Ken Snell said:
By chance, is Client Number a text field? Assuming that it is, let's try
this SQL statement (am delimiting the Client Number value with ' characters,
and have again omitted the # delimiters for the Service date value):

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]='" &
[Client Number] & "' And [Service date]<" &
[Service date]), [Service date]), [Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];

NOTE: You must replace TableName in the above with the real name of the
table. I am using TableName as a generic name because you didn't post the
real name of the table.




If Client Number is a numeric field, then use this:

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<" &
[Service date]), [Service date]), [Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];

NOTE: You must replace TableName in the above with the real name of the
table. I am using TableName as a generic name because you didn't post the
real name of the table.
--

Ken Snell
<MS ACCESS MVP>

--

Ken Snell
<MS ACCESS MVP>


Analyst 1 said:
It is formatted as date/time.

Ken Snell said:
What is the data type for Servicedate field in the table?

--

Ken Snell
<MS ACCESS MVP>

I get an error message that indicates an invalid date format.

:

Let's try this (I had omitted the # delimiter for date, and I'm now
formatting the date into the US format):

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<#" &
Format([Service date],"m/d/yyyy") & "#"), [Service date]),
[Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];


--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

The SQL you sent me returned "0" for dayssincelast .

:

Thank you I'll try it.

:

Not sure what you mean by "count", but to get the differences in
days
between each chronological record, you can use a query like this:

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<" &
[Service date]), [Service date]), [Service date])) AS
DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];
--

Ken Snell
<MS ACCESS MVP>



Here is an example of the data:
Client Number Service date
22 7/1/2002
22 7/2/2002
22 7/3/2002
22 7/4/2002
22 7/5/2002
22 9/9/2003
22 9/10/2003
22 9/11/2003
22 1/1/2005
22 2/10/2005
22 4/1/2005
22 4/2/2005
33 6/1/2003
33 7/1/2003
33 7/2/2003
33 7/3/2003
33 7/4/2003
33 7/5/2003
33 8/5/2005
33 8/6/2005
33 8/7/2005

So, Client #22 would have 5 separate episodes (an episode being
one
day or
a
number of consecutive days of service) the first with 5
consecutive
days,
the second with 3 consecutive days and so on....

The output I would hope to have would count the "episodes" and
consecutive
days of each.

Does this make sense?

:

You'll need to post examples of the data that you're using and
examples
of
the result that you expect from those data. Sounds as if you
want
to
have
the query "look back" in the data records to find a "start
date"?
--

Ken Snell
<MS ACCESS MVP>



message
Thank you for your response. I have used the datediff
function
in
the
past,
however in this querey it will not work because it is
possible
for
clients
to
have a break in service and datediff will count the days
inbetween. I
need a
funtion that only counts consecutive days per each client.
something
like
:
if client number = client number and date =date+1 then
datediff.
I
just
don't know how to do this in access.

Thanks again

:

Check out the DateDiff function.

--

Ken Snell
<MS ACCESS MVP>

message
I am trying to count the number of consecutive days a
client
stays in
serivces. My variables are: client number and a service
date.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
If this didn't work, then there is something in your design (a field name, a
table name, etc.) that is not part of what you posted. But, if EXCEL will
work, then go for it! Good luck.

--

Ken Snell
<MS ACCESS MVP>

Analyst 1 said:
Thanks for your help this didn't work either so, I am just going to use
excell to do this.

Ken Snell said:
By chance, is Client Number a text field? Assuming that it is, let's try
this SQL statement (am delimiting the Client Number value with '
characters,
and have again omitted the # delimiters for the Service date value):

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]='" &
[Client Number] & "' And [Service date]<" &
[Service date]), [Service date]), [Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];

NOTE: You must replace TableName in the above with the real name of the
table. I am using TableName as a generic name because you didn't post the
real name of the table.




If Client Number is a numeric field, then use this:

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<" &
[Service date]), [Service date]), [Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];

NOTE: You must replace TableName in the above with the real name of the
table. I am using TableName as a generic name because you didn't post the
real name of the table.
--

Ken Snell
<MS ACCESS MVP>

--

Ken Snell
<MS ACCESS MVP>


Analyst 1 said:
It is formatted as date/time.

:

What is the data type for Servicedate field in the table?

--

Ken Snell
<MS ACCESS MVP>

I get an error message that indicates an invalid date format.

:

Let's try this (I had omitted the # delimiter for date, and I'm now
formatting the date into the US format):

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<#" &
Format([Service date],"m/d/yyyy") & "#"), [Service date]),
[Service date])) AS DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];


--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

The SQL you sent me returned "0" for dayssincelast .

:

Thank you I'll try it.

:

Not sure what you mean by "count", but to get the differences
in
days
between each chronological record, you can use a query like
this:

SELECT [Client Number], [Service date],
(DateDiff("d",Nz(DMax("Service date", "TableName",
"[Client Number]=" &
[Client Number] & " And [Service date]<" &
[Service date]), [Service date]), [Service date])) AS
DaysSinceLast
FROM TableName
ORDER BY [Client Number], [Service date];
--

Ken Snell
<MS ACCESS MVP>



message
Here is an example of the data:
Client Number Service date
22 7/1/2002
22 7/2/2002
22 7/3/2002
22 7/4/2002
22 7/5/2002
22 9/9/2003
22 9/10/2003
22 9/11/2003
22 1/1/2005
22 2/10/2005
22 4/1/2005
22 4/2/2005
33 6/1/2003
33 7/1/2003
33 7/2/2003
33 7/3/2003
33 7/4/2003
33 7/5/2003
33 8/5/2005
33 8/6/2005
33 8/7/2005

So, Client #22 would have 5 separate episodes (an episode
being
one
day or
a
number of consecutive days of service) the first with 5
consecutive
days,
the second with 3 consecutive days and so on....

The output I would hope to have would count the "episodes"
and
consecutive
days of each.

Does this make sense?

:

You'll need to post examples of the data that you're using
and
examples
of
the result that you expect from those data. Sounds as if
you
want
to
have
the query "look back" in the data records to find a "start
date"?
--

Ken Snell
<MS ACCESS MVP>



message
Thank you for your response. I have used the datediff
function
in
the
past,
however in this querey it will not work because it is
possible
for
clients
to
have a break in service and datediff will count the days
inbetween. I
need a
funtion that only counts consecutive days per each
client.
something
like
:
if client number = client number and date =date+1 then
datediff.
I
just
don't know how to do this in access.

Thanks again

:

Check out the DateDiff function.

--

Ken Snell
<MS ACCESS MVP>

in
message
I am trying to count the number of consecutive days a
client
stays in
serivces. My variables are: client number and a
service
date.

Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Consecutive counts with conditions in excel 0
Excel Count of Consecutive Data 0
How to count consecutive records base on 2 groupings 0
Need a consecutive date total Report from one date field in table. 3
Counting Consecutive Years 5
Counting consecutive dates 5
"and" criteria 1
Find the Next Date for a given ID 1

Back
Top