Cross checking fields to find problem entries

  • Thread starter Thread starter Rocky
  • Start date Start date
R

Rocky

Aloha,
Could someone help me to figure out the best way to cross reference various
fields to ultimately find time overlaps for clients who get services?

Basically, I'm limited by having to deal with a flat .xls data file.
I've linked to it and the fields I've got to work with include:
- ClientID
- CaseID
- BeginHr
- BeingMinute
- EndHr
- EndMinute
- DateofService
- ServiceCode
I know how to filter records using a query to return only the Service codes
I need (40, 41) - So thats good to go.

Now I need to figure out how to look for
same clientID showing up in this data file that has:
the same caseID, clientID, BeginDate is all the same
AND where time overlaps (using the BeginHr, BeingMinute, EndHr, EndMinute)

Thanks if anyone can help at least get me started.
 
These five fields should be two --
- BeginHr
- BeingMinute
- EndHr
- EndMinute
- DateofService

StartSvc – DateTime Datatype
EndSvc – DateTime Datatype

SELECT [XX].ClientID, [XX].CaseID, [XX].ServiceCode, [XX].StartSvc,
[XX].EndSvc, (SELECT [YY].StartSvc FROM YourTable AS [YY] WHERE [YY].ClientID
= [XX].ClientID AND [YY].CaseID = [XX].CaseID AND [YY].ServiceCode =
[XX].ServiceCode AND [YY].StartSvc Between [XX].EndSvc AND [XX].EndSvc) AS
[2ndSvcStart, (SELECT [YY].EndSvc FROM YourTable AS [YY] WHERE [YY].ClientID
= [XX].ClientID AND [YY].CaseID = [XX].CaseID AND [YY].ServiceCode =
[XX].ServiceCode AND [YY].EndSvc Between [XX].EndSvc AND [XX].EndSvc) AS
[2ndSvcEnd
FROM YourTable AS [XX]
WHERE [XX].ServiceCode In(40, 41);
 
If you have control of this database, the first thing (after backup) you
might want to do is to condense the date/hours/minutes fields into a single
date/time field for start and for end. You can use functions and queries to
get the date and hour and minute values, if needed.

So if a service (theoretically) began one minute before midnight and
finished one minute after midnight, you'd need to be able to use the date
AND time of both start and stop to calculate the duration (all within the
same day could be handled the same way).

Next, to compare "like" clients, it sounds like you need to find records
that have the same client (clientID) with either a start date/time AFTER
another record for that client with a start date/time AND before that other
record's end date/time...

.... or an end date/time that comes after another record's (for that client)
start date/time AND before that other record's end date/time.

You'll find these kind of comparisons much easier if you'll take advantage
of Access' date-related functions (which "expect" date/time values to work
with).

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP
 
Aloha,
Could someone help me to figure out the best way to cross reference various
fields to ultimately find time overlaps for clients who get services?

Basically, I'm limited by having to deal with a flat .xls data file.
I've linked to it and the fields I've got to work with include:
- ClientID
- CaseID
- BeginHr
- BeingMinute
- EndHr
- EndMinute
- DateofService
- ServiceCode
I know how to filter records using a query to return only the Service codes
I need (40, 41) - So thats good to go.

Now I need to figure out how to look for
same clientID showing up in this data file that has:
the same caseID, clientID, BeginDate is all the same
AND where time overlaps (using the BeginHr, BeingMinute, EndHr, EndMinute)

Thanks if anyone can help at least get me started.
Click to expand...

This is a perfect case for a "Self Join" query. Create a new query, adding
your (linked spreadsheet?) table to it *twice*.

Join the two instances by dragging CaseID to CaseID, ClientID to ClientID and
DateOfService to DateOfService. Then put criteria on the second instance's
time fields to find overlaps. You're making your job vastly more difficult by
splitting the hours out from the minutes - I'd suggest adding calculated
fields to both tables' instance in the query:

StartTime: TimeSerial([BeginHr], [BeginMinute], 0)
EndTime: TimeSerial([EndHr], [EndMinute] 0)

You could then use criteria to see if one instance of StartTime is less than
the other instance of EndTime, *AND* the first instance of EndTime is greater
than the other instance of StartTime.
 
John, I agree with all the responders in that the date field should be
typical. The report spit's out the info as I've described and therefore I
need to do what you've suggested in calculating serial times. That worked
well - thanks!

Tomorrow I try the rest...
 
Thanks for the post!
Have a couple of questions if you can help again... thanks

In the example SQL you posted you the tables are XX and YY but you have also
slipped in a YourTable. Isn't the YourTable simply another XX? Or is this a
3rd instance of the table...(like a TEMP?)

Secondly, the 2ndSvcStart and 2ndSvcEnd
Are these also Aliases? Should they be the results of your comments on
combining the DateTime datatypes?
I know this gets confusing when it's so abstract. Perhaps it might help if I
paste the code that I've tried to create based on your post.
It's currently taking quite a bit to process but it does result in some
data. No results in the 2ndSvcStart and 2ndSvcEnd columns though.

SELECT details.[Client], details.[Case #], details.[Service Code],
details.[Begin Date], details.[End Date]
(SELECT details1.[Begin Date] FROM details AS details1 WHERE details1.Client
=details.Client
AND details1.[Case #] = details.[Case #]
AND details1.[Service Code]=details.[Service Code]
AND details1.[Begin Date] between details.[End Date]
AND details.[End Date]) AS 2ndSvcStart,

(SELECT details1.[End Date] FROM details AS details1
WHERE details1.[Client] = details.[Client]
AND details1.[Case #] = details.[Case #]
AND details1.[Service Code] = details.[Service Code]
AND details1.[End Date] Between details.[End Date]
AND details.[End Date]) AS 2ndSvcEnd
FROM details as details
WHERE details.[Service Code] in (34,41);


SELECT [XX].ClientID, [XX].CaseID, [XX].ServiceCode, [XX].StartSvc,
[XX].EndSvc, (SELECT [YY].StartSvc FROM YourTable AS [YY] WHERE [YY].ClientID
= [XX].ClientID AND [YY].CaseID = [XX].CaseID AND [YY].ServiceCode =
[XX].ServiceCode AND [YY].StartSvc Between [XX].EndSvc AND [XX].EndSvc) AS
[2ndSvcStart, (SELECT [YY].EndSvc FROM YourTable AS [YY] WHERE [YY].ClientID
= [XX].ClientID AND [YY].CaseID = [XX].CaseID AND [YY].ServiceCode =
[XX].ServiceCode AND [YY].EndSvc Between [XX].EndSvc AND [XX].EndSvc) AS
[2ndSvcEnd
FROM YourTable AS [XX]
WHERE [XX].ServiceCode In(40, 41);

--
Build a little, test a little.


Rocky said:
Aloha,
Could someone help me to figure out the best way to cross reference various
fields to ultimately find time overlaps for clients who get services?

Basically, I'm limited by having to deal with a flat .xls data file.
I've linked to it and the fields I've got to work with include:
- ClientID
- CaseID
- BeginHr
- BeingMinute
- EndHr
- EndMinute
- DateofService
- ServiceCode
I know how to filter records using a query to return only the Service codes
I need (40, 41) - So thats good to go.

Now I need to figure out how to look for
same clientID showing up in this data file that has:
the same caseID, clientID, BeginDate is all the same
AND where time overlaps (using the BeginHr, BeingMinute, EndHr, EndMinute)

Thanks if anyone can help at least get me started.
Click to expand...
Click to expand...
 
Isn't the YourTable simply another XX? Or is this a 3rd instance of the
table...(like a TEMP?)
XX and YY are aliases of YourTable. I used YourTable as I did not know what
the name of the table containing your data.
I guess you could call then aliases, They are the datetimes of the second
service that is being compared for overlap.

I noticed omissions as --
AS [2ndSvcStart, (SELECT [YY].EndSvc FROM
and
AS [2ndSvcEnd
both of these need a closing bracket.

I would think that FROM details as details would cause problems.
Change to FROM details

I would need to build table to test this and have not had time to do so.

--
Build a little, test a little.


Rocky said:
Thanks for the post!
Have a couple of questions if you can help again... thanks

In the example SQL you posted you the tables are XX and YY but you have also
slipped in a YourTable. Isn't the YourTable simply another XX? Or is this a
3rd instance of the table...(like a TEMP?)

Secondly, the 2ndSvcStart and 2ndSvcEnd
Are these also Aliases? Should they be the results of your comments on
combining the DateTime datatypes?
I know this gets confusing when it's so abstract. Perhaps it might help if I
paste the code that I've tried to create based on your post.
It's currently taking quite a bit to process but it does result in some
data. No results in the 2ndSvcStart and 2ndSvcEnd columns though.

SELECT details.[Client], details.[Case #], details.[Service Code],
details.[Begin Date], details.[End Date]
(SELECT details1.[Begin Date] FROM details AS details1 WHERE details1.Client
=details.Client
AND details1.[Case #] = details.[Case #]
AND details1.[Service Code]=details.[Service Code]
AND details1.[Begin Date] between details.[End Date]
AND details.[End Date]) AS 2ndSvcStart,

(SELECT details1.[End Date] FROM details AS details1
WHERE details1.[Client] = details.[Client]
AND details1.[Case #] = details.[Case #]
AND details1.[Service Code] = details.[Service Code]
AND details1.[End Date] Between details.[End Date]
AND details.[End Date]) AS 2ndSvcEnd
FROM details as details
WHERE details.[Service Code] in (34,41);


SELECT [XX].ClientID, [XX].CaseID, [XX].ServiceCode, [XX].StartSvc,
[XX].EndSvc, (SELECT [YY].StartSvc FROM YourTable AS [YY] WHERE [YY].ClientID
= [XX].ClientID AND [YY].CaseID = [XX].CaseID AND [YY].ServiceCode =
[XX].ServiceCode AND [YY].StartSvc Between [XX].EndSvc AND [XX].EndSvc) AS
[2ndSvcStart, (SELECT [YY].EndSvc FROM YourTable AS [YY] WHERE [YY].ClientID
= [XX].ClientID AND [YY].CaseID = [XX].CaseID AND [YY].ServiceCode =
[XX].ServiceCode AND [YY].EndSvc Between [XX].EndSvc AND [XX].EndSvc) AS
[2ndSvcEnd
FROM YourTable AS [XX]
WHERE [XX].ServiceCode In(40, 41);

--
Build a little, test a little.


Rocky said:
Aloha,
Could someone help me to figure out the best way to cross reference various
fields to ultimately find time overlaps for clients who get services?

Basically, I'm limited by having to deal with a flat .xls data file.
I've linked to it and the fields I've got to work with include:
- ClientID
- CaseID
- BeginHr
- BeingMinute
- EndHr
- EndMinute
- DateofService
- ServiceCode
I know how to filter records using a query to return only the Service codes
I need (40, 41) - So thats good to go.

Now I need to figure out how to look for
same clientID showing up in this data file that has:
the same caseID, clientID, BeginDate is all the same
AND where time overlaps (using the BeginHr, BeingMinute, EndHr, EndMinute)

Thanks if anyone can help at least get me started.
Click to expand...
Click to expand...
Click to expand...
 
John,
I have done what you recommended (see code pasted below). The query does run
and I get results. However, I don't quite understand how it's working because
there is only 5029 records to begin with and if I leave off the critera of
"FALSE" then it returns over a millions records - why is that?

When I put in the last criteria to "False" then it returns 5381 records.
This is baffeling. Thanks if you or anyone can tell me what is going on /
wrong. Thanks...

SELECT tblService.ID, tblService_1.ID,
tblService.[Form Number], tblService_1.[Form Number],
tblService.[Client Name], tblService_1.[Client Name],
tblService.[Case Number], tblService_1.[Case Number],
tblService.[Service Code], tblService_1.[Service Code],
tblService.[Begin Date], tblService_1.[Begin Date],
tblService.[Units of Service], tblService_1.[Units of Service],
tblService.[Place of Service], tblService_1.[Place of Service],
tblService.[Person Contacted], tblService_1.[Person Contacted],
(tblService_1.[Begin Date]<>tblService.[Begin Date] Or
tblService_1.[Case Number]<>tblService.[Case Number] Or
tblService_1.[Service Code]<>tblService.[Service Code] Or
tblService_1.[Units of Service]<>tblService.[Units of Service] Or
tblService_1.[Place of Service]<>tblService.[Place of Service] Or
tblService_1.[Person Contacted]<>tblService.[Person Contacted])
AS NoClash
FROM tblService, tblService AS tblService_1
WHERE ((((tblService_1.[Begin Date]<>tblService.[Begin Date] Or
tblService_1.[Case Number]<>tblService.[Case Number] Or
tblService_1.[Service Code]<>tblService.[Service Code] Or
tblService_1.[Units of Service]<>tblService.[Units of Service] Or
tblService_1.[Place of Service]<>tblService.[Place of Service] Or
tblService_1.[Person Contacted]<>tblService.[Person Contacted]))=False))
ORDER BY tblService.ID;
 
I should have read a little further as Allen B. answered my question...
http://allenbrowne.com/appevent.html

"If this discussion of efficiency seems pedantic, bear in mind that the
number of calculations performed in this Cartesian product is the square of
the number of input records. This means that if your table contains just
1,000 events, NoClash is calculated 1,000,000 times, regardless of the actual
number of clashes found."
 
John,
I have done what you recommended (see code pasted below). The query does run
and I get results. However, I don't quite understand how it's working because
there is only 5029 records to begin with and if I leave off the critera of
"FALSE" then it returns over a millions records - why is that?

When I put in the last criteria to "False" then it returns 5381 records.
This is baffeling. Thanks if you or anyone can tell me what is going on /
wrong. Thanks...

SELECT tblService.ID, tblService_1.ID,
tblService.[Form Number], tblService_1.[Form Number],
tblService.[Client Name], tblService_1.[Client Name],
tblService.[Case Number], tblService_1.[Case Number],
tblService.[Service Code], tblService_1.[Service Code],
tblService.[Begin Date], tblService_1.[Begin Date],
tblService.[Units of Service], tblService_1.[Units of Service],
tblService.[Place of Service], tblService_1.[Place of Service],
tblService.[Person Contacted], tblService_1.[Person Contacted],
(tblService_1.[Begin Date]<>tblService.[Begin Date] Or
tblService_1.[Case Number]<>tblService.[Case Number] Or
tblService_1.[Service Code]<>tblService.[Service Code] Or
tblService_1.[Units of Service]<>tblService.[Units of Service] Or
tblService_1.[Place of Service]<>tblService.[Place of Service] Or
tblService_1.[Person Contacted]<>tblService.[Person Contacted])
AS NoClash
FROM tblService, tblService AS tblService_1
WHERE ((((tblService_1.[Begin Date]<>tblService.[Begin Date] Or
tblService_1.[Case Number]<>tblService.[Case Number] Or
tblService_1.[Service Code]<>tblService.[Service Code] Or
tblService_1.[Units of Service]<>tblService.[Units of Service] Or
tblService_1.[Place of Service]<>tblService.[Place of Service] Or
tblService_1.[Person Contacted]<>tblService.[Person Contacted]))=False))
ORDER BY tblService.ID;
Click to expand...

This is NOT what I suggested - you don't have any JOINs (hence the enormous
cartesian join).

Try

SELECT tblService.ID, tblService_1.ID,
tblService.[Form Number], tblService_1.[Form Number],
tblService.[Client Name], tblService_1.[Client Name],
tblService.[Case Number], tblService_1.[Case Number],
tblService.[Service Code], tblService_1.[Service Code],
tblService.[Begin Date], tblService_1.[Begin Date],
tblService.[Units of Service], tblService_1.[Units of Service],
tblService.[Place of Service], tblService_1.[Place of Service],
tblService.[Person Contacted], tblService_1.[Person Contacted],
(tblService_1.[Begin Date]<>tblService.[Begin Date] Or
tblService_1.[Case Number]<>tblService.[Case Number] Or
tblService_1.[Service Code]<>tblService.[Service Code] Or
tblService_1.[Units of Service]<>tblService.[Units of Service] Or
tblService_1.[Place of Service]<>tblService.[Place of Service] Or
tblService_1.[Person Contacted]<>tblService.[Person Contacted])
AS NoClash
FROM tblService INNER JOIN tblService AS tblService_1
ON tblService.CaseID = tblService_1.CaseID
AND tblService.ClientID = tblService_1.ClientID
AND tblService.[Date of Service] = tblService_1.[Date of Service]
WHERE ((((tblService_1.[Begin Date]<>tblService.[Begin Date] Or
tblService_1.[Case Number]<>tblService.[Case Number] Or
tblService_1.[Service Code]<>tblService.[Service Code] Or
tblService_1.[Units of Service]<>tblService.[Units of Service] Or
tblService_1.[Place of Service]<>tblService.[Place of Service] Or
tblService_1.[Person Contacted]<>tblService.[Person Contacted]))=False))
ORDER BY tblService.ID;

With appropriate indexes on the three JOIN fields response should be pretty
snappy and you should not get any false drops.
 
Back
Top