novice needs help with query

[terry]

I need to create a query to determine whether or not the proper number of
home health visits occurred based on the doctor's orders...

I have set up three new tables in my database for this purpose... they are
as follows

tblCertificationPeriod
fields are
[CERT-PK Cert Period ID] 'primary key for this table
[CERT-FK Client ID] 'foreign key to identify the client
[CERT Certification Start] 'start date for doctor's order
[CERT Certification End] 'end date for doctor's order
[CERT-FK AorR] 'admit or recertification
[CERT-FK Billing Source] 'Foreign key to identify the Doctor who place
the order

tblDoctorsOrders
[DO-FK Cert Period id] 'Foreign key into tblCertificationPeriod
[DO-FK Visit Code] 'Foreign key into visit code list... this would
be x for times, xw for times per week etc
[DO Occurances] 'Occurances for the above time code if visit code
is xw and this number is 2 then the meaning is 2 times per week during the
certification period. if visit code is x and this number is 5 it means 5
times during the certification period
[DO-FK Type of Visit] ' Foreign key into a type of discipline visit
i.e. skilled nursing, occupational therapy, physical therapy etc.
[DO Number Of Weeks] 'this is for number of weeks if the order is for
times per week sometimes the order is 2 times per week for the first two
weeks then 1 time per week for the remaining time period
[DO Date Start Week] 'this is for the date this order starts... this
helps if the order changes in the middle of the cert period
[DO-PKp 'this is the primary key for this table


tblActualVisits
[LCMV-FK Cert Period] 'foreign key into the cert period table
[LCMV-FK Emp ID] 'foreign key into the employee table
[LCMV DOS] 'actual date of home health visit
[LCMV-FK Type of Visit] 'type of visit ie skilled nursing, occupational
therapy, physical therapy etc.
[LCMV-PK] 'primary key into this table


from this I need to compare the doctor's orders to the actual visits to make
sure they match and to create a report identifies those that don't (and those
that do)

basically I need to take the starting date from the doctors orders table for
a specific client/certperiod/discipline and compare the order to the actual
visit for the same client/certperiod/discipline.

the problems I'm having trouble overcoming is designing a query that will
match the correct dates

examples are for doctors orders starting

for same client, same cert period I may have the following doctors orders
[DO-FK Visit Code] XM = times per week
[DO Occurances] 2
[DO-FK Type of Visit] SN = "Skilled Nursing"
[DO Number Of Weeks] 2
[DO Date Start Week] 01/02/05
[DO-PK] 1

[DO-FK Visit Code] XM = times per week
[DO Occurances] 1
[DO-FK Type of Visit] SN = "Skilled Nursing"
[DO Number Of Weeks] 6
[DO Date Start Week] 01/16/05
[DO-PK] 2

[DO-FK Visit Code] XM = times per week
[DO Occurances] 2
[DO-FK Type of Visit] OT = "Occupational therapy"
[DO Number Of Weeks] 3
[DO Date Start Week] 01/02/05
[DO-PK] 3

[DO-FK Visit Code] X = times total
[DO Occurances] 5
[DO-FK Type of Visit] OT = "Occupational therapy"
[DO Number Of Weeks] 0 (meaning not a per week time frame)
[DO Date Start Week] 01/23/05
[DO-PK] 4

and the following actual visits
[LCMV DOS] 01/03/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 1

[LCMV DOS] 01/05/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 2

[LCMV DOS] 01/10/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 3

[LCMV DOS] 01/12/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 4

[LCMV DOS] 01/18/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 5

[LCMV DOS] 01/25/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 6

[LCMV DOS] 01/26/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 7

[LCMV DOS] 01/05/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 8

[LCMV DOS] 01/10/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 9

[LCMV DOS] 01/12/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 10

[LCMV DOS] 01/19/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 11

[LCMV DOS] 01/24/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 12


The report I need would be something like this

For Client/Cert period run on 01/30/05 would show
Week of 01/02/05 SN OK
Week of 01/09/05 SN OK
Week of 01/16/05 SN OK
Week of 01/23/05 SN Not OK
Week of 01/02/05 OT Not OK
Week of 01/09/05 OT OK
Week of 01/16/05 OT Not OK
Week of 01/23/05 OT OK 4 visits remain

or a weekly report in a specific week would go through all the clients and
give report

For the week of 01/02/05 (with other specific information but basically I
need to know which client/certperiod the visits don't match.
SN
Client 1 ok
client 2 not ok
Client 3 ok
Client 4 ok


OT
etc


PT
etc

etc


etc

 

[Chris2]

See Inline:

I need to create a query to determine whether or not the proper number of
home health visits occurred based on the doctor's orders...

I have set up three new tables in my database for this purpose... they are
as follows

tblCertificationPeriod
fields are
[CERT-PK Cert Period ID] 'primary key for this table
[CERT-FK Client ID] 'foreign key to identify the client
[CERT Certification Start] 'start date for doctor's order
[CERT Certification End] 'end date for doctor's order
[CERT-FK AorR] 'admit or recertification
[CERT-FK Billing Source] 'Foreign key to identify the Doctor who place
the order

tblDoctorsOrders
[DO-FK Cert Period id] 'Foreign key into tblCertificationPeriod

[DO-FK Visit Code] 'Foreign key into visit code list... this would
be x for times, xw for times per week etc
[DO Occurances] 'Occurances for the above time code if visit code
is xw and this number is 2 then the meaning is 2 times per week during the
certification period. if visit code is x and this number is 5 it means 5
times during the certification period
[DO-FK Type of Visit] ' Foreign key into a type of discipline visit
i.e. skilled nursing, occupational therapy, physical therapy etc.
[DO Number Of Weeks] 'this is for number of weeks if the order is for
times per week sometimes the order is 2 times per week for the first two
weeks then 1 time per week for the remaining time period
[DO Date Start Week] 'this is for the date this order starts... this
helps if the order changes in the middle of the cert period
[DO-PKp 'this is the primary key for this table


tblActualVisits
[LCMV-FK Cert Period] 'foreign key into the cert period table
[LCMV-FK Emp ID] 'foreign key into the employee table
[LCMV DOS] 'actual date of home health visit
[LCMV-FK Type of Visit] 'type of visit ie skilled nursing, occupational
therapy, physical therapy etc.
[LCMV-PK] 'primary key into this table


from this I need to compare the doctor's orders to the actual visits to make
sure they match and to create a report identifies those that don't (and those
that do)

basically I need to take the starting date from the doctors orders table for
a specific client/certperiod/discipline and compare the order to the actual
visit for the same client/certperiod/discipline.

the problems I'm having trouble overcoming is designing a query that will
match the correct dates

examples are for doctors orders starting

for same client, same cert period I may have the following doctors orders
[DO-FK Visit Code] XM = times per week
[DO Occurances] 2
[DO-FK Type of Visit] SN = "Skilled Nursing"
[DO Number Of Weeks] 2
[DO Date Start Week] 01/02/05
[DO-PK] 1

[DO-FK Visit Code] XM = times per week
[DO Occurances] 1
[DO-FK Type of Visit] SN = "Skilled Nursing"
[DO Number Of Weeks] 6
[DO Date Start Week] 01/16/05
[DO-PK] 2

[DO-FK Visit Code] XM = times per week
[DO Occurances] 2
[DO-FK Type of Visit] OT = "Occupational therapy"
[DO Number Of Weeks] 3
[DO Date Start Week] 01/02/05
[DO-PK] 3

[DO-FK Visit Code] X = times total
[DO Occurances] 5
[DO-FK Type of Visit] OT = "Occupational therapy"
[DO Number Of Weeks] 0 (meaning not a per week time frame)
[DO Date Start Week] 01/23/05
[DO-PK] 4

and the following actual visits
[LCMV DOS] 01/03/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 1

[LCMV DOS] 01/05/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 2

[LCMV DOS] 01/10/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 3

[LCMV DOS] 01/12/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 4

[LCMV DOS] 01/18/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 5

[LCMV DOS] 01/25/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 6

[LCMV DOS] 01/26/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 7

[LCMV DOS] 01/05/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 8

[LCMV DOS] 01/10/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 9

[LCMV DOS] 01/12/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 10

[LCMV DOS] 01/19/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 11

[LCMV DOS] 01/24/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 12


The report I need would be something like this

For Client/Cert period run on 01/30/05 would show
Week of 01/02/05 SN OK
Week of 01/09/05 SN OK
Week of 01/16/05 SN OK
Week of 01/23/05 SN Not OK
Week of 01/02/05 OT Not OK
Week of 01/09/05 OT OK
Week of 01/16/05 OT Not OK
Week of 01/23/05 OT OK 4 visits remain

or a weekly report in a specific week would go through all the clients and
give report

For the week of 01/02/05 (with other specific information but basically I
need to know which client/certperiod the visits don't match.
SN
Client 1 ok
client 2 not ok
Client 3 ok
Client 4 ok


OT
etc


PT
etc

etc


etc

General Commentary:

Naming:

 

[Chris2]

Sorry, I hit the wrong key combination. More coming later.

 

[Chris2]

See below:

I need to create a query to determine whether or not the proper number of
home health visits occurred based on the doctor's orders...

I have set up three new tables in my database for this purpose... they are
as follows

tblCertificationPeriod
fields are
[CERT-PK Cert Period ID] 'primary key for this table
[CERT-FK Client ID] 'foreign key to identify the client
[CERT Certification Start] 'start date for doctor's order
[CERT Certification End] 'end date for doctor's order
[CERT-FK AorR] 'admit or recertification
[CERT-FK Billing Source] 'Foreign key to identify the Doctor who place
the order

tblDoctorsOrders
[DO-FK Cert Period id] 'Foreign key into tblCertificationPeriod
[DO-FK Visit Code] 'Foreign key into visit code list... this would
be x for times, xw for times per week etc
[DO Occurances] 'Occurances for the above time code if visit code
is xw and this number is 2 then the meaning is 2 times per week during the
certification period. if visit code is x and this number is 5 it means 5
times during the certification period
[DO-FK Type of Visit] ' Foreign key into a type of discipline visit
i.e. skilled nursing, occupational therapy, physical therapy etc.
[DO Number Of Weeks] 'this is for number of weeks if the order is for
times per week sometimes the order is 2 times per week for the first two
weeks then 1 time per week for the remaining time period
[DO Date Start Week] 'this is for the date this order starts... this
helps if the order changes in the middle of the cert period
[DO-PKp 'this is the primary key for this table


tblActualVisits
[LCMV-FK Cert Period] 'foreign key into the cert period table
[LCMV-FK Emp ID] 'foreign key into the employee table
[LCMV DOS] 'actual date of home health visit
[LCMV-FK Type of Visit] 'type of visit ie skilled nursing, occupational
therapy, physical therapy etc.
[LCMV-PK] 'primary key into this table


from this I need to compare the doctor's orders to the actual visits to make
sure they match and to create a report identifies those that don't (and those
that do)

basically I need to take the starting date from the doctors orders table for
a specific client/certperiod/discipline and compare the order to the actual
visit for the same client/certperiod/discipline.

the problems I'm having trouble overcoming is designing a query that will
match the correct dates

examples are for doctors orders starting

for same client, same cert period I may have the following doctors orders
[DO-FK Visit Code] XM = times per week
[DO Occurances] 2
[DO-FK Type of Visit] SN = "Skilled Nursing"
[DO Number Of Weeks] 2
[DO Date Start Week] 01/02/05
[DO-PK] 1

[DO-FK Visit Code] XM = times per week
[DO Occurances] 1
[DO-FK Type of Visit] SN = "Skilled Nursing"
[DO Number Of Weeks] 6
[DO Date Start Week] 01/16/05
[DO-PK] 2

[DO-FK Visit Code] XM = times per week
[DO Occurances] 2
[DO-FK Type of Visit] OT = "Occupational therapy"
[DO Number Of Weeks] 3
[DO Date Start Week] 01/02/05
[DO-PK] 3

[DO-FK Visit Code] X = times total
[DO Occurances] 5
[DO-FK Type of Visit] OT = "Occupational therapy"
[DO Number Of Weeks] 0 (meaning not a per week time frame)
[DO Date Start Week] 01/23/05
[DO-PK] 4

and the following actual visits
[LCMV DOS] 01/03/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 1

[LCMV DOS] 01/05/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 2

[LCMV DOS] 01/10/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 3

[LCMV DOS] 01/12/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 4

[LCMV DOS] 01/18/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 5

[LCMV DOS] 01/25/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 6

[LCMV DOS] 01/26/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 7

[LCMV DOS] 01/05/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 8

[LCMV DOS] 01/10/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 9

[LCMV DOS] 01/12/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 10

[LCMV DOS] 01/19/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 11

[LCMV DOS] 01/24/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 12


The report I need would be something like this

For Client/Cert period run on 01/30/05 would show
Week of 01/02/05 SN OK
Week of 01/09/05 SN OK
Week of 01/16/05 SN OK
Week of 01/23/05 SN Not OK
Week of 01/02/05 OT Not OK
Week of 01/09/05 OT OK
Week of 01/16/05 OT Not OK
Week of 01/23/05 OT OK 4 visits remain

or a weekly report in a specific week would go through all the clients and
give report

For the week of 01/02/05 (with other specific information but basically I
need to know which client/certperiod the visits don't match.
SN
Client 1 ok
client 2 not ok
Client 3 ok
Client 4 ok


OT
etc


PT
etc

etc


etc

terry,

Unnecessary Commentary.

Object (Tables, Queries, Columns, etc.) names should not contain
spaces (or special characters).
The benefit to enhanced readability from removing all the brackets
is considerable.
Only PARAMETERS should have spaces in them, and the [] that go
around them identify them for what they are.

The [DO-FK Cert Period id] column from tblDoctorsOrders represents
the same thing as the [CERT-PK Cert Period ID] column in
tblCertificationPeriod, and yet it has a different name.
If a column must be FKd to another table, both columns in both
tables should have exactly the same name.

Column [LCMV-FK Cert Period], which has no "ID" at the end, from
tblActualVisits, is said to be a "foreign key into the cert period
table". It's naming difference is even greater than that discussed in
the above paragraph.

It looks like tblCertificationPeriod.[CERT Certification Start] and
tblCertificationPeriod.[CERT Certification End] seem to cover the time
period pertty well. However, tblDoctorsOrders.[DO Date Start Week]
from also seems to be stating the same thing as [CERT Certification
Start]. And tblDoctorsOrders.[DO Number Of Weeks] seems to be another
way of deriving tblCertificationPeriod.[CERT Certification End]. To
me, this appears to be conflicting sources of information.

Further, in general, there are a variety of columns that have names
that don't seem obviously related to their contents.

All the columns from tblActualVisits begin with "LMCV" "Actual
Visits" and "LMCV" have no obvious connection in meaning. In
tblActualVisits.[LCMV DOS], "DOS" doesn't seem related to "Date of
Visit". Etc.

In general, the naming of Columns should reflect the contents of the
column.


But aside from that:

(Please Note: I don't really have all the information I would have
wanted, and I made a few assumptions.)



Fortunately for me, I already have a table constructed which has all
the weeks of 2005 loaded and numbered (you can make one for yourself,
as well).

CREATE TABLE CalendarWeeks
(WeekStart DATETIME NOT NULL
,WeekEnd DATETIME NOT NULL
,WeekNumber BYTE NOT NULL
,CONSTRAINT pk_CalendarWeeks PRIMARY KEY (WeekStart, WeekEnd)
)

VBA Loading Code:

Public Sub FillCalendarWeeks()

Dim db As DAO.Database
Dim WeekStart As Date
Dim WeekEnd As Date
Dim LoopDate As Date
Dim rs As DAO.Recordset
Dim YearStartDate As Date
Dim YearEndDate As Date

Set db = CurrentDb()

Set rs = db.OpenRecordset("CalendarWeeks")

YearStartDate = "01/01/05"
YearEndDate = "12/31/05"
LoopDate = "01/01/05"
Do While LoopDate <= YearEndDate
With rs
.AddNew
.Fields("WeekStart") = LoopDate
.Fields("WeekEnd") _
= IIf(LoopDate = YearEndDate, YearEndDate, IIf(LoopDate =
YearStartDate, YearStartDate, DateAdd("d", 6, LoopDate)))
.Fields("WeekNumber") = DatePart("ww", LoopDate)
.Update
LoopDate = IIf(LoopDate = YearStartDate, DateAdd("d", 1,
LoopDate), DateAdd("ww", 1, LoopDate))
End With
Loop

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub


I leave certain columns out, as they aren't currently relevant.

CREATE TABLE tblCertificationPeriod
([CERT-PK Cert Period ID] AUTOINCREMENT
,[CERT-FK Client ID] LONG
,[CERT Certification Start] DATETIME
,[CERT Certification End] DATETIME
,CONSTRAINT pk_tblCertificationPeriod
PRIMARY KEY ([CERT-PK Cert Period ID])
)

Sample Data (abbreviated headers):

CP_ID Client_ID Cert_Start Cert_End
1 1 01/05/2005 05/24/2005
2 2 02/01/2005 02/28/2005
3 3 01/01/2005 01/27/2005

CREATE TABLE tblActualVisits
([LCMV-PK] AUTOINCREMENT
,[LCMV-FK Cert Period] LONG
,[LCMV DOS] DATETIME
,[LCMV-FK Type of Visit] TEXT(2)
,CONSTRAINT pk_tblActualVisits PRIMARY KEY ([LCMV-PK])
,CONSTRAINT fk_tblActualVisits_tblCertificationPeriod
FOREIGN KEY ([LCMV-FK Cert Period])
REFERENCES tblCertificationPeriod ([CERT-PK Cert Period ID])
)

PK FK_CP DOS FK_ToV
1 1 01/06/2005 SN
2 1 01/13/2005 SN
3 1 01/20/2005 SN
4 2 02/02/2005 SN
5 2 02/18/2005 SN
6 2 02/25/2005 SN
7 3 01/12/2005 SN
8 3 01/19/2005 SN
9 3 01/26/2005 SN


Query (for Report 1):

[End of Reporting Period] can be replaced by whatever date source is
desired.

SELECT CP1.[CERT-FK Client ID] AS ClientID
,"Week of " & CW1.WeekStart AS WeekStart
,AV1.[LCMV-FK Type of Visit] AS TypeOfVisit
,IIF(CP1.[CERT Certification End] < Date()
,"Visits ended "
& ABS(DateDiff("w", CW1.WeekStart, CP1.[CERT
Certification End]))
& " weeks ago."
,DateDiff("w", CW1.WeekStart, CP1.[CERT Certification
End])
& " visits remain.") As WeeksRemaining
FROM ((tblActualVisits AS AV1
INNER JOIN
CalendarWeeks AS CW1
ON (AV1.[LCMV DOS]
BETWEEN CW1.WeekStart AND CW1.WeekEnd))
INNER JOIN
tblCertificationPeriod AS CP1
ON CP1.[CERT-PK Cert Period ID] = AV1.[LCMV-FK Cert Period])
WHERE CW1.WeekEnd <= CDate([End of Reporting Period])
ORDER BY CP1.[CERT-FK Client ID]
,"Week of " & CW1.WeekStart


My apologies, but I could not figure out where the "status" codes
(OK, Not OK) were coming from, no field seemed appropriate, so I left
that out of the Query.

My Results:

Client ID WeekStart TypeOfVisit WeeksRemaining

1 Week of 01/02/2005 SN 20 visits remain.

1 Week of 01/09/2005 SN 19 visits remain.

1 Week of 01/16/2005 SN 18 visits remain.

3 Week of 01/09/2005 SN Visits ended 2 weeks ago.

3 Week of 01/16/2005 SN Visits ended 1 weeks ago.

3 Week of 01/23/2005 SN Visits ended 0 weeks ago.





Sincerely,



Chris O.

 

[tw]

I'm sorry if I didn't communicate well what I was trying to accomplish.

Cert periond table from and through dates have nothing to do with doctors
orders starting date except that the date must be between the from and to
dates of the cert period. The number of weeks also has nothing to do with
identifying the end of the cert period. It is there to determine how many
weeks withing the certperiod a specific order is valid.

I.E. a doctors order can be something like this
Certification period 01/02/05 - 03/02/05

starting on 01/09/05 for then next 3 weeks there is to be a skilled nursing
visit to the client's home 3 times a week
then starting on 01/30/05 for the next 2 weeks there is to be a skilled
nursing visit to the client's home 2 times a week
then starting on 01/13/05 for the remainder of the cert period there is to
be a skilled nursing visit 8 more times

At the end of that cert period there is usualy a new cert period issued, but
that is not the issue.

What I need to do based on the actual visits is to make sure that the
doctors order is carried out perfectly. And when it isn't there needs to be
a new doctors order to cover the extra or lack of visits. The report is to
be run weekly. My lack of access and vba skills is making it hard for me to
understand your response to me, but it doesn't look like you quite understood
where I was going. Thanks for the attemp to help though.

As far as foreign key and primary key being exactly the same name... that is
a matter of opinion, when using the names in queries I don't want to have to
specify a long table name to identify which table the field is coming from.
I am a novice to access but not to database programming. As far as the
naming conventions go, I was following the conventions of the database
already in place.

See below:

I need to create a query to determine whether or not the proper number of
home health visits occurred based on the doctor's orders...

I have set up three new tables in my database for this purpose... they are
as follows

tblCertificationPeriod
fields are
[CERT-PK Cert Period ID] 'primary key for this table
[CERT-FK Client ID] 'foreign key to identify the client
[CERT Certification Start] 'start date for doctor's order
[CERT Certification End] 'end date for doctor's order
[CERT-FK AorR] 'admit or recertification
[CERT-FK Billing Source] 'Foreign key to identify the Doctor who place
the order

tblDoctorsOrders
[DO-FK Cert Period id] 'Foreign key into tblCertificationPeriod
[DO-FK Visit Code] 'Foreign key into visit code list... this would
be x for times, xw for times per week etc
[DO Occurances] 'Occurances for the above time code if visit code
is xw and this number is 2 then the meaning is 2 times per week during the
certification period. if visit code is x and this number is 5 it means 5
times during the certification period
[DO-FK Type of Visit] ' Foreign key into a type of discipline visit
i.e. skilled nursing, occupational therapy, physical therapy etc.
[DO Number Of Weeks] 'this is for number of weeks if the order is for
times per week sometimes the order is 2 times per week for the first two
weeks then 1 time per week for the remaining time period
[DO Date Start Week] 'this is for the date this order starts... this
helps if the order changes in the middle of the cert period
[DO-PKp 'this is the primary key for this table


tblActualVisits
[LCMV-FK Cert Period] 'foreign key into the cert period table
[LCMV-FK Emp ID] 'foreign key into the employee table
[LCMV DOS] 'actual date of home health visit
[LCMV-FK Type of Visit] 'type of visit ie skilled nursing, occupational
therapy, physical therapy etc.
[LCMV-PK] 'primary key into this table


from this I need to compare the doctor's orders to the actual visits to make
sure they match and to create a report identifies those that don't (and those
that do)

basically I need to take the starting date from the doctors orders table for
a specific client/certperiod/discipline and compare the order to the actual
visit for the same client/certperiod/discipline.

the problems I'm having trouble overcoming is designing a query that will
match the correct dates

examples are for doctors orders starting

for same client, same cert period I may have the following doctors orders
[DO-FK Visit Code] XM = times per week
[DO Occurances] 2
[DO-FK Type of Visit] SN = "Skilled Nursing"
[DO Number Of Weeks] 2
[DO Date Start Week] 01/02/05
[DO-PK] 1

[DO-FK Visit Code] XM = times per week
[DO Occurances] 1
[DO-FK Type of Visit] SN = "Skilled Nursing"
[DO Number Of Weeks] 6
[DO Date Start Week] 01/16/05
[DO-PK] 2

[DO-FK Visit Code] XM = times per week
[DO Occurances] 2
[DO-FK Type of Visit] OT = "Occupational therapy"
[DO Number Of Weeks] 3
[DO Date Start Week] 01/02/05
[DO-PK] 3

[DO-FK Visit Code] X = times total
[DO Occurances] 5
[DO-FK Type of Visit] OT = "Occupational therapy"
[DO Number Of Weeks] 0 (meaning not a per week time frame)
[DO Date Start Week] 01/23/05
[DO-PK] 4

and the following actual visits
[LCMV DOS] 01/03/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 1

[LCMV DOS] 01/05/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 2

[LCMV DOS] 01/10/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 3

[LCMV DOS] 01/12/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 4

[LCMV DOS] 01/18/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 5

[LCMV DOS] 01/25/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 6

[LCMV DOS] 01/26/05
[LCMV-FK Type of Visit] SN
[LCMV-PK] 7

[LCMV DOS] 01/05/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 8

[LCMV DOS] 01/10/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 9

[LCMV DOS] 01/12/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 10

[LCMV DOS] 01/19/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 11

[LCMV DOS] 01/24/05
[LCMV-FK Type of Visit] OT
[LCMV-PK] 12


The report I need would be something like this

For Client/Cert period run on 01/30/05 would show
Week of 01/02/05 SN OK
Week of 01/09/05 SN OK
Week of 01/16/05 SN OK
Week of 01/23/05 SN Not OK
Week of 01/02/05 OT Not OK
Week of 01/09/05 OT OK
Week of 01/16/05 OT Not OK
Week of 01/23/05 OT OK 4 visits remain

or a weekly report in a specific week would go through all the clients and
give report

For the week of 01/02/05 (with other specific information but basically I
need to know which client/certperiod the visits don't match.
SN
Client 1 ok
client 2 not ok
Client 3 ok
Client 4 ok


OT
etc


PT
etc

etc


etc

terry,

Unnecessary Commentary.

Object (Tables, Queries, Columns, etc.) names should not contain
spaces (or special characters).
The benefit to enhanced readability from removing all the brackets
is considerable.
Only PARAMETERS should have spaces in them, and the [] that go
around them identify them for what they are.

The [DO-FK Cert Period id] column from tblDoctorsOrders represents
the same thing as the [CERT-PK Cert Period ID] column in
tblCertificationPeriod, and yet it has a different name.
If a column must be FKd to another table, both columns in both
tables should have exactly the same name.

Column [LCMV-FK Cert Period], which has no "ID" at the end, from
tblActualVisits, is said to be a "foreign key into the cert period
table". It's naming difference is even greater than that discussed in
the above paragraph.

It looks like tblCertificationPeriod.[CERT Certification Start] and
tblCertificationPeriod.[CERT Certification End] seem to cover the time
period pertty well. However, tblDoctorsOrders.[DO Date Start Week]
from also seems to be stating the same thing as [CERT Certification
Start]. And tblDoctorsOrders.[DO Number Of Weeks] seems to be another
way of deriving tblCertificationPeriod.[CERT Certification End]. To
me, this appears to be conflicting sources of information.

Further, in general, there are a variety of columns that have names
that don't seem obviously related to their contents.

All the columns from tblActualVisits begin with "LMCV" "Actual
Visits" and "LMCV" have no obvious connection in meaning. In
tblActualVisits.[LCMV DOS], "DOS" doesn't seem related to "Date of
Visit". Etc.

In general, the naming of Columns should reflect the contents of the
column.


But aside from that:

(Please Note: I don't really have all the information I would have
wanted, and I made a few assumptions.)



Fortunately for me, I already have a table constructed which has all
the weeks of 2005 loaded and numbered (you can make one for yourself,
as well).

CREATE TABLE CalendarWeeks
(WeekStart DATETIME NOT NULL
,WeekEnd DATETIME NOT NULL
,WeekNumber BYTE NOT NULL
,CONSTRAINT pk_CalendarWeeks PRIMARY KEY (WeekStart, WeekEnd)
)

VBA Loading Code:

Public Sub FillCalendarWeeks()

Dim db As DAO.Database
Dim WeekStart As Date
Dim WeekEnd As Date
Dim LoopDate As Date
Dim rs As DAO.Recordset
Dim YearStartDate As Date
Dim YearEndDate As Date

Set db = CurrentDb()

Set rs = db.OpenRecordset("CalendarWeeks")

YearStartDate = "01/01/05"
YearEndDate = "12/31/05"
LoopDate = "01/01/05"
Do While LoopDate <= YearEndDate
With rs
.AddNew
.Fields("WeekStart") = LoopDate
.Fields("WeekEnd") _
= IIf(LoopDate = YearEndDate, YearEndDate, IIf(LoopDate =
YearStartDate, YearStartDate, DateAdd("d", 6, LoopDate)))
.Fields("WeekNumber") = DatePart("ww", LoopDate)
.Update
LoopDate = IIf(LoopDate = YearStartDate, DateAdd("d", 1,
LoopDate), DateAdd("ww", 1, LoopDate))
End With
Loop

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub


I leave certain columns out, as they aren't currently relevant.

CREATE TABLE tblCertificationPeriod
([CERT-PK Cert Period ID] AUTOINCREMENT
,[CERT-FK Client ID] LONG
,[CERT Certification Start] DATETIME
,[CERT Certification End] DATETIME
,CONSTRAINT pk_tblCertificationPeriod
PRIMARY KEY ([CERT-PK Cert Period ID])
)

Sample Data (abbreviated headers):

CP_ID Client_ID Cert_Start Cert_End
1 1 01/05/2005 05/24/2005
2 2 02/01/2005 02/28/2005
3 3 01/01/2005 01/27/2005

CREATE TABLE tblActualVisits
([LCMV-PK] AUTOINCREMENT
,[LCMV-FK Cert Period] LONG
,[LCMV DOS] DATETIME
,[LCMV-FK Type of Visit] TEXT(2)
,CONSTRAINT pk_tblActualVisits PRIMARY KEY ([LCMV-PK])
,CONSTRAINT fk_tblActualVisits_tblCertificationPeriod
FOREIGN KEY ([LCMV-FK Cert Period])
REFERENCES tblCertificationPeriod ([CERT-PK Cert Period ID])
)

PK FK_CP DOS FK_ToV
1 1 01/06/2005 SN
2 1 01/13/2005 SN
3 1 01/20/2005 SN
4 2 02/02/2005 SN
5 2 02/18/2005 SN
6 2 02/25/2005 SN
7 3 01/12/2005 SN
8 3 01/19/2005 SN
9 3 01/26/2005 SN


Query (for Report 1):

[End of Reporting Period] can be replaced by whatever date source is
desired.

SELECT CP1.[CERT-FK Client ID] AS ClientID
,"Week of " & CW1.WeekStart AS WeekStart
,AV1.[LCMV-FK Type of Visit] AS TypeOfVisit
,IIF(CP1.[CERT Certification End] < Date()
,"Visits ended "
& ABS(DateDiff("w", CW1.WeekStart, CP1.[CERT
Certification End]))
& " weeks ago."
,DateDiff("w", CW1.WeekStart, CP1.[CERT Certification
End])
& " visits remain.") As WeeksRemaining
FROM ((tblActualVisits AS AV1
INNER JOIN
CalendarWeeks AS CW1
ON (AV1.[LCMV DOS]
BETWEEN CW1.WeekStart AND CW1.WeekEnd))
INNER JOIN
tblCertificationPeriod AS CP1
ON CP1.[CERT-PK Cert Period ID] = AV1.[LCMV-FK Cert Period])
WHERE CW1.WeekEnd <= CDate([End of Reporting Period])
ORDER BY CP1.[CERT-FK Client ID]
,"Week of " & CW1.WeekStart


My apologies, but I could not figure out where the "status" codes
(OK, Not OK) were coming from, no field seemed appropriate, so I left
that out of the Query.

My Results:

Client ID WeekStart TypeOfVisit WeeksRemaining

1 Week of 01/02/2005 SN 20 visits remain.

1 Week of 01/09/2005 SN 19 visits remain.

1 Week of 01/16/2005 SN 18 visits remain.

3 Week of 01/09/2005 SN Visits ended 2 weeks ago.

3 Week of 01/16/2005 SN Visits ended 1 weeks ago.

3 Week of 01/23/2005 SN Visits ended 0 weeks ago.





Sincerely,



Chris O.