Comparing Values in Separate Rows in Access

H

homerj0216

I need some help with the following problem.

I have a list of users. Each of those users has a span of service dates. I
need to consolidate the list for each user so that service dates that are
consecutive create a new set of service dates, such that the new service
dates start at the first service date and run through the last service date.

Here is an example of what I mean:

User1 has service dates 09-01 to 09-18
User1 has service dates 09-19 to 10-01
User1 has service dates 10-01 to 10-15
User1 has service dates 11-01 to 11-15

I need a way to consolidate the service dates for this user so that the
service dates are 09-01 to 10-15 for the first three (thus consolidating
three rows into one row), and then counts the last set of service dates as a
separate service date range. It should account for the fact that some
service dates overlap (as the second service end date matches the third start
date), but should also account for dates that are consecutive but not
overlapping (as the first end date is consecutive to the second start date).

So, for User1, I would end up with the following rows of data:
User1 has service dates 09-01 to 10-15
User1 has service dates 11-01 to 11-15.

Any help is appreciated!

Thanks,
Mike
 
H

homerj0216

Marhsall,
Thanks for the direction on that. It doesn't seem to work because I am
using dates, and not whole numbers. The formula reads:
Occurence: [ServiceDate]-DCount("*","DatesofService","ServiceDate<=" &
[ServiceDate])

I keep getting a data type mismatch in criteria expression. I am guessing
it is because I am using dates, and not integers.

This is helpful, but I am not sure it is going to do what I need.

Any other help is appreciated!

Thanks,
Mike
 
H

homerj0216

Marshall,
I fixed the problem with the dates. They were formatted as text, I changed
them to a date format, and the error went away.

I don't think the query method you suggested will work, because it is
calculating the maximum length of continious sequence of integers (dates). I
am not sure how I would apply this to my problem.

I have the list you mention. I have the dates in a new table which shows
each users name, and their service dates. What I need to do is add an
incremental count to those dates that are consecutive. If a user has service
dates that are consecutive, the counter should incrementally count until it
reaches a date that is not consecutive. But, that same user can have more
service dates, and thus the incremental counter should start again at 1, and
count the next set of consecutive dates for that user.

Can you help?

Thanks,
mike

Marshall Barton said:
Working through all the ins & outs of your question would
take more time than I can devote to it. However, I'm pretty
sure that you can convert your situation to one that is
equivalent the one described in that article and I can help
you do that.

First, I seriously doubt that using an integer criteria on a
date field is a problem even If your date field has a time
included. If the error is because of what you suspect, it
would be because youe "date field" is really a Text field.

If the date field has a time component or not, you can
convert a date value or a string that looks like a date to a
number by using the DateValue function.

Another major issue is that you will need to create all the
dates in each record's range. I.e. a record with a range
like 3/23/08 to 3/26/08 would need to be represented by 4
records:
3/23/08
3/24/08
3/25/08
3/26/08

This can be done using a query with another table. This
other table (call it Numbers) would just have one field
(call it Num) and populated with the records:
0
1
2
3
. . . up to more than your biggest range

Then the query would look like:

SELECT startdate+Num, f1, f2, ...
FROM yourtable, Numbers
WHERE startdate+Num <= enddate

Then apply the article's technique to that instead of your
table.
--
Marsh
MVP [MS Access]

Thanks for the direction on that. It doesn't seem to work because I am
using dates, and not whole numbers. The formula reads:
Occurence: [ServiceDate]-DCount("*","DatesofService","ServiceDate<=" &
[ServiceDate])

I keep getting a data type mismatch in criteria expression. I am guessing
it is because I am using dates, and not integers.

This is helpful, but I am not sure it is going to do what I need.


Marshall Barton said:
homerj0216 wrote:

I need some help with the following problem.

I have a list of users. Each of those users has a span of service dates. I
need to consolidate the list for each user so that service dates that are
consecutive create a new set of service dates, such that the new service
dates start at the first service date and run through the last service date.

Here is an example of what I mean:

User1 has service dates 09-01 to 09-18
User1 has service dates 09-19 to 10-01
User1 has service dates 10-01 to 10-15
User1 has service dates 11-01 to 11-15

I need a way to consolidate the service dates for this user so that the
service dates are 09-01 to 10-15 for the first three (thus consolidating
three rows into one row), and then counts the last set of service dates as a
separate service date range. It should account for the fact that some
service dates overlap (as the second service end date matches the third start
date), but should also account for dates that are consecutive but not
overlapping (as the first end date is consecutive to the second start date).

So, for User1, I would end up with the following rows of data:
User1 has service dates 09-01 to 10-15
User1 has service dates 11-01 to 11-15.


A tricky problem. Take a look at
http://www.mvps.org/access/queries/qry0018.htm
for how to deal with a related issue.
Click to expand...
Click to expand...
Click to expand...
 
H

homerj0216

Marshall,
I have created a procedure to take care my problem, however, something is a
miss. Can you review this code, and tell me where I am going wrong?

The table I am creating the new table from has the following format:
UserId
LastName
FirstName
DOB
BeginDate
EndDate

The Function should check for the following:
Does a User have more than one set of service dates?
If Not, then the user's current information should be added to the table.
If the User has more than one set of service dates, then it should check to
see if the service dates are consecutive. If they are consecutive, then the
result should be a new row of data with the original begin date, and then new
end date. (there is also the possiblity that a user can have more than two
sets of consecutive dates, so the code should account for the fact that some
users might have three or four sets of service dates that are consecutive.)

Function BeginEndDate()

Dim rstSmSrvcDts As Recordset
Dim rstSmSrvcDtsRdy As Recordset
Dim rstUsers As Recordset
Dim dtmBeginDate As Date
Dim dtmEndDate As Date
Dim dtmBeginDateNxt As Date
Dim dtmEndDateNxt As Date
Dim strCurrUser As String
Dim SrvcDts As Recordset

With CurrentDb
Set rstSmSrvcDts = .OpenRecordset("SELECT * FROM
[SummaryofServiceDates]")
Set rstSmSrvcDtsRdy = .OpenRecordset("SELECT * FROM
[SummaryofServiceDatesReady]")
End With

Do While Not rstSmSrvcDts.EOF
'this section creates variables for the current records begin and end
date,
'and the next records begin and end date
dtmBeginDate = rstSmSrvcDts!BeginDate
dtmEndDate = rstSmSrvcDts!EndDate
rstSmSrvcDts.MoveNext
dtBeginDateNxt = rstSmSrvcDts!BeginDate
dtEndDateNxt = rstSmSrvcDts!EndDate
'this section checks to see if the Next begin dates value is consecutive
to the
'current records end date.
If (dtBeginDateNxt - 1) = dtmEndDate Then
rstSmSrvcDts.MovePrevious
dtmEndDate = dtEndDateNxt
With rstSmSrvcDtsRdy
.AddNew
!UserId = rstSmSrvcDts!UserId
!LastName = rstSmSrvcDts!LastName
!FirstName = rstSmSrvcDts!FirstName
!DOB = rstSmSrvcDts!DOB
!BeginDate = dtmBeginDate
!EndDate = dtmEndDate
!UnitCost = rstSmSrvcDts!UnitCost
.Update
End With
'this section checks to see if the Next begin dates value is the same as
the
'current records end date.
ElseIf (dtBeginDateNxt) = dtmEndDate Then
rstSmSrvcDts.MovePrevious
dtmEndDate = dtEndDateNxt
With rstSmSrvcDtsRdy
.AddNew
!UserId = rstSmSrvcDts!UserId
!LastName = rstSmSrvcDts!LastName
!FirstName = rstSmSrvcDts!FirstName
!DOB = rstSmSrvcDts!DOB
!BeginDate = dtmBeginDate
!EndDate = dtmEndDate
!UnitCost = rstSmSrvcDts!UnitCost
.Update
End With
'this section checks to see if the Next begin dates value is not equal
to the
'current records end date.
ElseIf (dtBeginDateNxt) <> dtmEndDate Then
rstSmSrvcDts.MovePrevious
dtmEndDate = dtmEndDate
With rstSmSrvcDtsRdy
.AddNew
!UserId = rstSmSrvcDts!UserId
!LastName = rstSmSrvcDts!LastName
!FirstName = rstSmSrvcDts!FirstName
!DOB = rstSmSrvcDts!DOB
!BeginDate = dtmBeginDate
!EndDate = dtmEndDate
!UnitCost = rstSmSrvcDts!UnitCost
.Update
End With
End If
rstSmSrvcDts.MoveNext
Loop

rstSmSrvcDts.Close
rstSmSrvcDtsRdy.Close

Set rstSmSrvcDts = Nothing
Set rstSmSrvcDtsRdy = Nothing

End Function

Thanks,
Mike

Marshall Barton said:
The Numbers table and the query I posted earlier is supposed
to convert your ranges to a continuous sequence of dates by
adding the incremental count in the Numbers table. Try it
and see if you get what you need to apply that article's
technique.

Note that dates are internally represented as numbers so
integer vs date should not be an issue.
--
Marsh
MVP [MS Access]

I fixed the problem with the dates. They were formatted as text, I changed
them to a date format, and the error went away.

I don't think the query method you suggested will work, because it is
calculating the maximum length of continious sequence of integers (dates). I
am not sure how I would apply this to my problem.

I have the list you mention. I have the dates in a new table which shows
each users name, and their service dates. What I need to do is add an
incremental count to those dates that are consecutive. If a user has service
dates that are consecutive, the counter should incrementally count until it
reaches a date that is not consecutive. But, that same user can have more
service dates, and thus the incremental counter should start again at 1, and
count the next set of consecutive dates for that user.


Marshall Barton said:
Working through all the ins & outs of your question would
take more time than I can devote to it. However, I'm pretty
sure that you can convert your situation to one that is
equivalent the one described in that article and I can help
you do that.

First, I seriously doubt that using an integer criteria on a
date field is a problem even If your date field has a time
included. If the error is because of what you suspect, it
would be because youe "date field" is really a Text field.

If the date field has a time component or not, you can
convert a date value or a string that looks like a date to a
number by using the DateValue function.

Another major issue is that you will need to create all the
dates in each record's range. I.e. a record with a range
like 3/23/08 to 3/26/08 would need to be represented by 4
records:
3/23/08
3/24/08
3/25/08
3/26/08

This can be done using a query with another table. This
other table (call it Numbers) would just have one field
(call it Num) and populated with the records:
0
1
2
3
. . . up to more than your biggest range

Then the query would look like:

SELECT startdate+Num, f1, f2, ...
FROM yourtable, Numbers
WHERE startdate+Num <= enddate

Then apply the article's technique to that instead of your
table.


homerj0216 wrote:
Thanks for the direction on that. It doesn't seem to work because I am
using dates, and not whole numbers. The formula reads:
Occurence: [ServiceDate]-DCount("*","DatesofService","ServiceDate<=" &
[ServiceDate])

I keep getting a data type mismatch in criteria expression. I am guessing
it is because I am using dates, and not integers.

This is helpful, but I am not sure it is going to do what I need.


:
A tricky problem. Take a look at
http://www.mvps.org/access/queries/qry0018.htm
for how to deal with a related issue.


homerj0216 wrote:
I have a list of users. Each of those users has a span of service dates. I
need to consolidate the list for each user so that service dates that are
consecutive create a new set of service dates, such that the new service
dates start at the first service date and run through the last service date.

Here is an example of what I mean:

User1 has service dates 09-01 to 09-18
User1 has service dates 09-19 to 10-01
User1 has service dates 10-01 to 10-15
User1 has service dates 11-01 to 11-15

I need a way to consolidate the service dates for this user so that the
service dates are 09-01 to 10-15 for the first three (thus consolidating
three rows into one row), and then counts the last set of service dates as a
separate service date range. It should account for the fact that some
service dates overlap (as the second service end date matches the third start
date), but should also account for dates that are consecutive but not
overlapping (as the first end date is consecutive to the second start date).

So, for User1, I would end up with the following rows of data:
User1 has service dates 09-01 to 10-15
User1 has service dates 11-01 to 11-15.
Click to expand...
Click to expand...
Click to expand...
 
H

homerj0216

Marshall,
I have figured out my issues. Here is my solution:

Function BeginEndDate2()

Dim rstSmSrvcDts As Recordset
Dim rstSmSrvcDtsRdy As Recordset
Dim rstUsers As Recordset
Dim dtmBeginDate As Date
Dim dtmEndDate As Date
Dim dtmCurEndDate As Date
Dim dtmCurBeginDate As Date
Dim dtmBeginDateNxt As Date
Dim dtmEndDateNxt As Date
Dim strCurrUser As String
Dim strNextUser As String
Dim SrvcDts As Recordset
Dim rowcnt As Variant
Dim cntrow As Variant

'this section will clear the records in the SummaryofServiceDates Table

Set SrvcDts = CurrentDb.OpenRecordset("SummaryofServiceDatesReady",
dbOpenTable)

While Not SrvcDts.EOF
SrvcDts.Delete
SrvcDts.MoveNext
Wend

SrvcDts.Close

Set SrvcDts = Nothing
'the records have been cleared.

'this section opens the tables I want to use.

With CurrentDb
Set rstSmSrvcDts = .OpenRecordset("SELECT * FROM
[SummaryofServiceDates]")
Set rstSmSrvcDtsRdy = .OpenRecordset("SELECT * FROM
[SummaryofServiceDatesReady]")
End With

With rstSmSrvcDts
.MoveLast
.MoveFirst
End With

'this section gets the row count of the table I am pulling data from, and
creates a counter for the rows I am going to add.
cntrow = 1
rowcnt = rstSmSrvcDts.RecordCount

'the function runs until the row counts are equal.
Do While cntrow < rowcnt
dtmCurBeginDate = rstSmSrvcDts!BeginDate
dtmCurEndDate = rstSmSrvcDts!EndDate
dtmBeginDate = rstSmSrvcDts!BeginDate
dtmEndDate = rstSmSrvcDts!EndDate
strCurrUser = rstSmSrvcDts!Medicaid
rstSmSrvcDts.MoveNext
dtBeginDateNxt = rstSmSrvcDts!BeginDate
dtEndDateNxt = rstSmSrvcDts!EndDate
strNextUser = rstSmSrvcDts!Medicaid
rstSmSrvcDts.MovePrevious
'this section checks the begin and end dates in consecutive rows until the
dates do not match.
Do While dtBeginDateNxt = dtmCurEndDate Or (dtBeginDateNxt - 1) =
dtmCurEndDate
dtmCurEndDate = dtEndDateNxt
rstSmSrvcDts.MoveNext
dtmBeginDate = rstSmSrvcDts!BeginDate
dtmEndDate = rstSmSrvcDts!EndDate
rowcnt = rowcnt - 1
'If the last record is encountered here, then it skips this section.
If cntrow = rowcnt Then
Else
rstSmSrvcDts.MoveNext
dtBeginDateNxt = rstSmSrvcDts!BeginDate
dtEndDateNxt = rstSmSrvcDts!EndDate
rstSmSrvcDts.MovePrevious
End If
Loop
With rstSmSrvcDtsRdy
.AddNew
!Medicaid = rstSmSrvcDts!Medicaid
!LastName = rstSmSrvcDts!LastName
!FirstName = rstSmSrvcDts!FirstName
!DOB = rstSmSrvcDts!DOB
!BeginDate = dtmCurBeginDate
!EndDate = dtmCurEndDate
!UnitCost = rstSmSrvcDts!UnitCost
.Update
End With
rstSmSrvcDts.MoveNext
cntrow = cntrow + 1
Loop
'this section adds the last record.
If cntrow = rowcnt Then
With rstSmSrvcDtsRdy
'rstSmSrvcDts.MoveNext
.AddNew
!Medicaid = rstSmSrvcDts!Medicaid
!LastName = rstSmSrvcDts!LastName
!FirstName = rstSmSrvcDts!FirstName
!DOB = rstSmSrvcDts!DOB
!BeginDate = rstSmSrvcDts!BeginDate
!EndDate = rstSmSrvcDts!EndDate
!UnitCost = rstSmSrvcDts!UnitCost
.Update
End With
End If

rstSmSrvcDts.Close
rstSmSrvcDtsRdy.Close

Set rstSmSrvcDts = Nothing
Set rstSmSrvcDtsRdy = Nothing


End Function

Not pretty, but it works!

Mike

homerj0216 said:
Marshall,
I have created a procedure to take care my problem, however, something is a
miss. Can you review this code, and tell me where I am going wrong?

The table I am creating the new table from has the following format:
UserId
LastName
FirstName
DOB
BeginDate
EndDate

The Function should check for the following:
Does a User have more than one set of service dates?
If Not, then the user's current information should be added to the table.
If the User has more than one set of service dates, then it should check to
see if the service dates are consecutive. If they are consecutive, then the
result should be a new row of data with the original begin date, and then new
end date. (there is also the possiblity that a user can have more than two
sets of consecutive dates, so the code should account for the fact that some
users might have three or four sets of service dates that are consecutive.)

Function BeginEndDate()

Dim rstSmSrvcDts As Recordset
Dim rstSmSrvcDtsRdy As Recordset
Dim rstUsers As Recordset
Dim dtmBeginDate As Date
Dim dtmEndDate As Date
Dim dtmBeginDateNxt As Date
Dim dtmEndDateNxt As Date
Dim strCurrUser As String
Dim SrvcDts As Recordset

With CurrentDb
Set rstSmSrvcDts = .OpenRecordset("SELECT * FROM
[SummaryofServiceDates]")
Set rstSmSrvcDtsRdy = .OpenRecordset("SELECT * FROM
[SummaryofServiceDatesReady]")
End With

Do While Not rstSmSrvcDts.EOF
'this section creates variables for the current records begin and end
date,
'and the next records begin and end date
dtmBeginDate = rstSmSrvcDts!BeginDate
dtmEndDate = rstSmSrvcDts!EndDate
rstSmSrvcDts.MoveNext
dtBeginDateNxt = rstSmSrvcDts!BeginDate
dtEndDateNxt = rstSmSrvcDts!EndDate
'this section checks to see if the Next begin dates value is consecutive
to the
'current records end date.
If (dtBeginDateNxt - 1) = dtmEndDate Then
rstSmSrvcDts.MovePrevious
dtmEndDate = dtEndDateNxt
With rstSmSrvcDtsRdy
.AddNew
!UserId = rstSmSrvcDts!UserId
!LastName = rstSmSrvcDts!LastName
!FirstName = rstSmSrvcDts!FirstName
!DOB = rstSmSrvcDts!DOB
!BeginDate = dtmBeginDate
!EndDate = dtmEndDate
!UnitCost = rstSmSrvcDts!UnitCost
.Update
End With
'this section checks to see if the Next begin dates value is the same as
the
'current records end date.
ElseIf (dtBeginDateNxt) = dtmEndDate Then
rstSmSrvcDts.MovePrevious
dtmEndDate = dtEndDateNxt
With rstSmSrvcDtsRdy
.AddNew
!UserId = rstSmSrvcDts!UserId
!LastName = rstSmSrvcDts!LastName
!FirstName = rstSmSrvcDts!FirstName
!DOB = rstSmSrvcDts!DOB
!BeginDate = dtmBeginDate
!EndDate = dtmEndDate
!UnitCost = rstSmSrvcDts!UnitCost
.Update
End With
'this section checks to see if the Next begin dates value is not equal
to the
'current records end date.
ElseIf (dtBeginDateNxt) <> dtmEndDate Then
rstSmSrvcDts.MovePrevious
dtmEndDate = dtmEndDate
With rstSmSrvcDtsRdy
.AddNew
!UserId = rstSmSrvcDts!UserId
!LastName = rstSmSrvcDts!LastName
!FirstName = rstSmSrvcDts!FirstName
!DOB = rstSmSrvcDts!DOB
!BeginDate = dtmBeginDate
!EndDate = dtmEndDate
!UnitCost = rstSmSrvcDts!UnitCost
.Update
End With
End If
rstSmSrvcDts.MoveNext
Loop

rstSmSrvcDts.Close
rstSmSrvcDtsRdy.Close

Set rstSmSrvcDts = Nothing
Set rstSmSrvcDtsRdy = Nothing

End Function

Thanks,
Mike

Marshall Barton said:
The Numbers table and the query I posted earlier is supposed
to convert your ranges to a continuous sequence of dates by
adding the incremental count in the Numbers table. Try it
and see if you get what you need to apply that article's
technique.

Note that dates are internally represented as numbers so
integer vs date should not be an issue.
--
Marsh
MVP [MS Access]

I fixed the problem with the dates. They were formatted as text, I changed
them to a date format, and the error went away.

I don't think the query method you suggested will work, because it is
calculating the maximum length of continious sequence of integers (dates). I
am not sure how I would apply this to my problem.

I have the list you mention. I have the dates in a new table which shows
each users name, and their service dates. What I need to do is add an
incremental count to those dates that are consecutive. If a user has service
dates that are consecutive, the counter should incrementally count until it
reaches a date that is not consecutive. But, that same user can have more
service dates, and thus the incremental counter should start again at 1, and
count the next set of consecutive dates for that user.


:
Working through all the ins & outs of your question would
take more time than I can devote to it. However, I'm pretty
sure that you can convert your situation to one that is
equivalent the one described in that article and I can help
you do that.

First, I seriously doubt that using an integer criteria on a
date field is a problem even If your date field has a time
included. If the error is because of what you suspect, it
would be because youe "date field" is really a Text field.

If the date field has a time component or not, you can
convert a date value or a string that looks like a date to a
number by using the DateValue function.

Another major issue is that you will need to create all the
dates in each record's range. I.e. a record with a range
like 3/23/08 to 3/26/08 would need to be represented by 4
records:
3/23/08
3/24/08
3/25/08
3/26/08

This can be done using a query with another table. This
other table (call it Numbers) would just have one field
(call it Num) and populated with the records:
0
1
2
3
. . . up to more than your biggest range

Then the query would look like:

SELECT startdate+Num, f1, f2, ...
FROM yourtable, Numbers
WHERE startdate+Num <= enddate

Then apply the article's technique to that instead of your
table.


homerj0216 wrote:
Thanks for the direction on that. It doesn't seem to work because I am
using dates, and not whole numbers. The formula reads:
Occurence: [ServiceDate]-DCount("*","DatesofService","ServiceDate<=" &
[ServiceDate])

I keep getting a data type mismatch in criteria expression. I am guessing
it is because I am using dates, and not integers.

This is helpful, but I am not sure it is going to do what I need.


:
A tricky problem. Take a look at
http://www.mvps.org/access/queries/qry0018.htm
for how to deal with a related issue.


homerj0216 wrote:
I have a list of users. Each of those users has a span of service dates. I
need to consolidate the list for each user so that service dates that are
consecutive create a new set of service dates, such that the new service
dates start at the first service date and run through the last service date.

Here is an example of what I mean:

User1 has service dates 09-01 to 09-18
User1 has service dates 09-19 to 10-01
User1 has service dates 10-01 to 10-15
User1 has service dates 11-01 to 11-15

I need a way to consolidate the service dates for this user so that the
service dates are 09-01 to 10-15 for the first three (thus consolidating
three rows into one row), and then counts the last set of service dates as a
separate service date range. It should account for the fact that some
service dates overlap (as the second service end date matches the third start
date), but should also account for dates that are consecutive but not
overlapping (as the first end date is consecutive to the second start date).

So, for User1, I would end up with the following rows of data:
User1 has service dates 09-01 to 10-15
User1 has service dates 11-01 to 11-15.
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

Access Count dates within a Month 4
Problem with NetworkDays ... Inconsistent results 0
dates inbetween 3
Windows Vista Macros to delete dates 0
counting based on two dates 2
Working with date fields in a form 2
Count between dates 2
Return Max Date 1

Top