HELP with database design!!!

[jsmith]

I am trying to create a database based off of a medical department charge
sheet. The physical charge sheet is 3 pages long and contains 152 possible
items (checkmarks & quanitites only) that can be done to a single patient.
What I want to do is have a basic data entry person enter the date of
service, patient identifiers, and then check off what was performed. Then I
need to generate a report that shows me the date of service, patient id and
ONLY what was checked off or entered as a quantity. (I thought I could
accomplish this by using the "is not null" criteria, but I am still pulling
blank entries.) Then I need to export that data to an excel spreadsheet
where billing will take place from there by a different department. So I
initially created one table with all 152 possible charges but could never get
a query to run because it is "too complex" or a report to run because I have
"chose too many fields". So I tried splitting the table into 6 smaller
sections and let access add an id# and used that to assign relationships.
Reports didn't work from that either. Then I tried splitting a query into 6
sections and while I can run each query individually (with blank entries even
though I stated "is not null") I still can't get a report to run because of
too many fields. The only thing I can do is export the entire single table
to a huge excel spreadsheet that includes every field whether data is there
or not. It's just not as pretty as I was hoping I could make it!

Also, with regard to form design....I made a really nice and very large form
with my single table. I am afraid to use the 6 divided tables because I want
the data entry person to be forced to go through every field but I don't know
how to link them together to do that. But I am sure the 6 divided tables
must be an easier way. My single table form is so big it spreads across two
pages and tabbing to the second page requires you to move the slide bar to
the right to see it. Any suggestions there???

Thanks so much for your help.

 

[Sprinks]

Hi, Jsmith.

The problem is that you're trying to implement a one-to-many relationship
between Office Visit and Services Performed in a single table, this approach
growing out of attetmpting to duplicate your existing paper form. It would
also seem possible to do the billing portion in Access.

It would be much easier with the following design:

Patients
--------------------
PatientID AutoNumber (Primary Key [PK])
FName Text
LName Text
Address Text
....etc.

Services (Master Service table consisting of the 152 possible services to
render)
----------------------------------------------------------------------------------------------
ServiceCode AutoNumber (PK)
Service Text
UnitPrice Currency

OfficeVisits (One Side)
----------------------------------------
OfficeVisitID AutoNumber (PK)
VisitDate Date/Time
PatientID Integer (Foreign Key [FK] to Patients
....etc.

ServicesRendered (Many Side)
------------------------------------------
ServiceRenderedID AutoNumber (PK)
OfficeVisitID Integer (FK to OfficeVisits)
ServiceID Integer (FK to Services)
Qty Integer or Single

Then you would have a main form based on OfficeVisits and a continuous
subform for ServicesRendered, linked by the OfficeVisitID. Instead of
checking boxes, you could select the service from a combo box.

You can keep the checkbox approach with this simpler table structure, but
you'll have to do some work with code. The form would have *unbound* check
boxes. Then using either the form's BeforeUpdate event or a command button,
you will need to loop through the checkboxes and insert appropriate records
into the ServicesRendered table, picking up the Qty from the associated
textbox:

' Loop through controls in form's Controls collection

Dim ctl As Control
Dim intServiceRenderedCode As Integer
Dim intQty As Integer

For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
Select Case ctl.Name
Case "chkPhysicalExam"
' In this case, 1 is the ServiceID associated with this
service
intServiceRenderedCode = 1
' Assign quantity to the textbox associated with this
service
intQty = Me![PhysicalExamQty]
Case "chkLumpectomy"
intServiceRenderedCode = 2
intQty = Me![LumpectomyQty]
' ... other 152 cases
End Select
' Insert Into statement here
End If
Next ctl

This seems inelegant because it is both a lot of effort, and it requires you
change the code each time you add a new service. With the other approach,
you'd simply add a new service to the Services table, and it would show up in
your combo box.

Hope that helps.

Sprinks

 

[jsmith]

Thank you Sprinks!

This is fantastic!!! I have done what you said and am currently working on
the combo box in the continuous subform. (The code idea scared me and you
are right about future modifications.) I don't really like the looks of it
because it looks like a datasheet that runs horizontally when I pictured a
nice neat drop down box that runs vertically. And I am hoping that I can
specify a quantity within the combo box as that will be necessary for some
services. I guess a combo box only allows up to 20 choices, so I'll need to
split up my master service table quite a bit - but that is no problem. I
just hope I don't run into the same reporting problems with "too many fields"
or "query too complex" once I have a chance to enter and test some data. I'm
sure I will have more quesitons as I continue to work on this next week, can
I continue to post here or contact you some other way?

Thanks again for your help.

Jsmith

Hi, Jsmith.

The problem is that you're trying to implement a one-to-many relationship
between Office Visit and Services Performed in a single table, this approach
growing out of attetmpting to duplicate your existing paper form. It would
also seem possible to do the billing portion in Access.

It would be much easier with the following design:

Patients
--------------------
PatientID AutoNumber (Primary Key [PK])
FName Text
LName Text
Address Text
...etc.

Services (Master Service table consisting of the 152 possible services to
render)
----------------------------------------------------------------------------------------------
ServiceCode AutoNumber (PK)
Service Text
UnitPrice Currency

OfficeVisits (One Side)
----------------------------------------
OfficeVisitID AutoNumber (PK)
VisitDate Date/Time
PatientID Integer (Foreign Key [FK] to Patients
...etc.

ServicesRendered (Many Side)
------------------------------------------
ServiceRenderedID AutoNumber (PK)
OfficeVisitID Integer (FK to OfficeVisits)
ServiceID Integer (FK to Services)
Qty Integer or Single

Then you would have a main form based on OfficeVisits and a continuous
subform for ServicesRendered, linked by the OfficeVisitID. Instead of
checking boxes, you could select the service from a combo box.

You can keep the checkbox approach with this simpler table structure, but
you'll have to do some work with code. The form would have *unbound* check
boxes. Then using either the form's BeforeUpdate event or a command button,
you will need to loop through the checkboxes and insert appropriate records
into the ServicesRendered table, picking up the Qty from the associated
textbox:

' Loop through controls in form's Controls collection

Dim ctl As Control
Dim intServiceRenderedCode As Integer
Dim intQty As Integer

For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
Select Case ctl.Name
Case "chkPhysicalExam"
' In this case, 1 is the ServiceID associated with this
service
intServiceRenderedCode = 1
' Assign quantity to the textbox associated with this
service
intQty = Me![PhysicalExamQty]
Case "chkLumpectomy"
intServiceRenderedCode = 2
intQty = Me![LumpectomyQty]
' ... other 152 cases
End Select
' Insert Into statement here
End If
Next ctl

This seems inelegant because it is both a lot of effort, and it requires you
change the code each time you add a new service. With the other approach,
you'd simply add a new service to the Services table, and it would show up in
your combo box.

Hope that helps.

Sprinks

I am trying to create a database based off of a medical department charge
sheet. The physical charge sheet is 3 pages long and contains 152 possible
items (checkmarks & quanitites only) that can be done to a single patient.
What I want to do is have a basic data entry person enter the date of
service, patient identifiers, and then check off what was performed. Then I
need to generate a report that shows me the date of service, patient id and
ONLY what was checked off or entered as a quantity. (I thought I could
accomplish this by using the "is not null" criteria, but I am still pulling
blank entries.) Then I need to export that data to an excel spreadsheet
where billing will take place from there by a different department. So I
initially created one table with all 152 possible charges but could never get
a query to run because it is "too complex" or a report to run because I have
"chose too many fields". So I tried splitting the table into 6 smaller
sections and let access add an id# and used that to assign relationships.
Reports didn't work from that either. Then I tried splitting a query into 6
sections and while I can run each query individually (with blank entries even
though I stated "is not null") I still can't get a report to run because of
too many fields. The only thing I can do is export the entire single table
to a huge excel spreadsheet that includes every field whether data is there
or not. It's just not as pretty as I was hoping I could make it!

Also, with regard to form design....I made a really nice and very large form
with my single table. I am afraid to use the 6 divided tables because I want
the data entry person to be forced to go through every field but I don't know
how to link them together to do that. But I am sure the 6 divided tables
must be an easier way. My single table form is so big it spreads across two
pages and tabbing to the second page requires you to move the slide bar to
the right to see it. Any suggestions there???

Thanks so much for your help.

 

[Sprinks]

Thank you Sprinks!

This is fantastic!!! I have done what you said and am currently working on
the combo box in the continuous subform. (The code idea scared me and you
are right about future modifications.) I don't really like the looks of it
because it looks like a datasheet that runs horizontally when I pictured a
nice neat drop down box that runs vertically. And I am hoping that I can
specify a quantity within the combo box as that will be necessary for some
services. I guess a combo box only allows up to 20 choices, so I'll need to
split up my master service table quite a bit - but that is no problem. I
just hope I don't run into the same reporting problems with "too many fields"
or "query too complex" once I have a chance to enter and test some data. I'm
sure I will have more quesitons as I continue to work on this next week, can
I continue to post here or contact you some other way?

Thanks again for your help.

Jsmith

Hi, Jsmith.

The problem is that you're trying to implement a one-to-many relationship
between Office Visit and Services Performed in a single table, this approach
growing out of attetmpting to duplicate your existing paper form. It would
also seem possible to do the billing portion in Access.

It would be much easier with the following design:

Patients
--------------------
PatientID AutoNumber (Primary Key [PK])
FName Text
LName Text
Address Text
...etc.

Services (Master Service table consisting of the 152 possible services to
render)
----------------------------------------------------------------------------------------------
ServiceCode AutoNumber (PK)
Service Text
UnitPrice Currency

OfficeVisits (One Side)
----------------------------------------
OfficeVisitID AutoNumber (PK)
VisitDate Date/Time
PatientID Integer (Foreign Key [FK] to Patients
...etc.

ServicesRendered (Many Side)
------------------------------------------
ServiceRenderedID AutoNumber (PK)
OfficeVisitID Integer (FK to OfficeVisits)
ServiceID Integer (FK to Services)
Qty Integer or Single

Then you would have a main form based on OfficeVisits and a continuous
subform for ServicesRendered, linked by the OfficeVisitID. Instead of
checking boxes, you could select the service from a combo box.

You can keep the checkbox approach with this simpler table structure, but
you'll have to do some work with code. The form would have *unbound* check
boxes. Then using either the form's BeforeUpdate event or a command button,
you will need to loop through the checkboxes and insert appropriate records
into the ServicesRendered table, picking up the Qty from the associated
textbox:

' Loop through controls in form's Controls collection

Dim ctl As Control
Dim intServiceRenderedCode As Integer
Dim intQty As Integer

For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
Select Case ctl.Name
Case "chkPhysicalExam"
' In this case, 1 is the ServiceID associated with this
service
intServiceRenderedCode = 1
' Assign quantity to the textbox associated with this
service
intQty = Me![PhysicalExamQty]
Case "chkLumpectomy"
intServiceRenderedCode = 2
intQty = Me![LumpectomyQty]
' ... other 152 cases
End Select
' Insert Into statement here
End If
Next ctl

This seems inelegant because it is both a lot of effort, and it requires you
change the code each time you add a new service. With the other approach,
you'd simply add a new service to the Services table, and it would show up in
your combo box.

Hope that helps.

Sprinks

I am trying to create a database based off of a medical department charge
sheet. The physical charge sheet is 3 pages long and contains 152 possible
items (checkmarks & quanitites only) that can be done to a single patient.
What I want to do is have a basic data entry person enter the date of
service, patient identifiers, and then check off what was performed. Then I
need to generate a report that shows me the date of service, patient id and
ONLY what was checked off or entered as a quantity. (I thought I could
accomplish this by using the "is not null" criteria, but I am still pulling
blank entries.) Then I need to export that data to an excel spreadsheet
where billing will take place from there by a different department. So I
initially created one table with all 152 possible charges but could never get
a query to run because it is "too complex" or a report to run because I have
"chose too many fields". So I tried splitting the table into 6 smaller
sections and let access add an id# and used that to assign relationships.
Reports didn't work from that either. Then I tried splitting a query into 6
sections and while I can run each query individually (with blank entries even
though I stated "is not null") I still can't get a report to run because of
too many fields. The only thing I can do is export the entire single table
to a huge excel spreadsheet that includes every field whether data is there
or not. It's just not as pretty as I was hoping I could make it!

Also, with regard to form design....I made a really nice and very large form
with my single table. I am afraid to use the 6 divided tables because I want
the data entry person to be forced to go through every field but I don't know
how to link them together to do that. But I am sure the 6 divided tables
must be an easier way. My single table form is so big it spreads across two
pages and tabbing to the second page requires you to move the slide bar to
the right to see it. Any suggestions there???

Thanks so much for your help.

 

[Sprinks]

I'm not sure why you think a combo box is limited to 20 rows; I don't know
the exact limit, but it will certainly contain the entire contents of your
Services table.

The combo box will be bound to the ServiceID field in your ServicesRendered
table; you will need a textbox bound to Qty to enter any quantities.

As to other questions, just continue this thread. You can find it by
searching for your post name.

Sprinks

Thank you Sprinks!

This is fantastic!!! I have done what you said and am currently working on
the combo box in the continuous subform. (The code idea scared me and you
are right about future modifications.) I don't really like the looks of it
because it looks like a datasheet that runs horizontally when I pictured a
nice neat drop down box that runs vertically. And I am hoping that I can
specify a quantity within the combo box as that will be necessary for some
services. I guess a combo box only allows up to 20 choices, so I'll need to
split up my master service table quite a bit - but that is no problem. I
just hope I don't run into the same reporting problems with "too many fields"
or "query too complex" once I have a chance to enter and test some data. I'm
sure I will have more quesitons as I continue to work on this next week, can
I continue to post here or contact you some other way?

Thanks again for your help.

Jsmith

Hi, Jsmith.

The problem is that you're trying to implement a one-to-many relationship
between Office Visit and Services Performed in a single table, this approach
growing out of attetmpting to duplicate your existing paper form. It would
also seem possible to do the billing portion in Access.

It would be much easier with the following design:

Patients
--------------------
PatientID AutoNumber (Primary Key [PK])
FName Text
LName Text
Address Text
...etc.

Services (Master Service table consisting of the 152 possible services to
render)
----------------------------------------------------------------------------------------------
ServiceCode AutoNumber (PK)
Service Text
UnitPrice Currency

OfficeVisits (One Side)
----------------------------------------
OfficeVisitID AutoNumber (PK)
VisitDate Date/Time
PatientID Integer (Foreign Key [FK] to Patients
...etc.

ServicesRendered (Many Side)
------------------------------------------
ServiceRenderedID AutoNumber (PK)
OfficeVisitID Integer (FK to OfficeVisits)
ServiceID Integer (FK to Services)
Qty Integer or Single

Then you would have a main form based on OfficeVisits and a continuous
subform for ServicesRendered, linked by the OfficeVisitID. Instead of
checking boxes, you could select the service from a combo box.

You can keep the checkbox approach with this simpler table structure, but
you'll have to do some work with code. The form would have *unbound* check
boxes. Then using either the form's BeforeUpdate event or a command button,
you will need to loop through the checkboxes and insert appropriate records
into the ServicesRendered table, picking up the Qty from the associated
textbox:

' Loop through controls in form's Controls collection

Dim ctl As Control
Dim intServiceRenderedCode As Integer
Dim intQty As Integer

For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
Select Case ctl.Name
Case "chkPhysicalExam"
' In this case, 1 is the ServiceID associated with this
service
intServiceRenderedCode = 1
' Assign quantity to the textbox associated with this
service
intQty = Me![PhysicalExamQty]
Case "chkLumpectomy"
intServiceRenderedCode = 2
intQty = Me![LumpectomyQty]
' ... other 152 cases
End Select
' Insert Into statement here
End If
Next ctl

This seems inelegant because it is both a lot of effort, and it requires you
change the code each time you add a new service. With the other approach,
you'd simply add a new service to the Services table, and it would show up in
your combo box.

Hope that helps.

Sprinks

I am trying to create a database based off of a medical department charge
sheet. The physical charge sheet is 3 pages long and contains 152 possible
items (checkmarks & quanitites only) that can be done to a single patient.
What I want to do is have a basic data entry person enter the date of
service, patient identifiers, and then check off what was performed. Then I
need to generate a report that shows me the date of service, patient id and
ONLY what was checked off or entered as a quantity. (I thought I could
accomplish this by using the "is not null" criteria, but I am still pulling
blank entries.) Then I need to export that data to an excel spreadsheet
where billing will take place from there by a different department. So I
initially created one table with all 152 possible charges but could never get
a query to run because it is "too complex" or a report to run because I have
"chose too many fields". So I tried splitting the table into 6 smaller
sections and let access add an id# and used that to assign relationships.
Reports didn't work from that either. Then I tried splitting a query into 6
sections and while I can run each query individually (with blank entries even
though I stated "is not null") I still can't get a report to run because of
too many fields. The only thing I can do is export the entire single table
to a huge excel spreadsheet that includes every field whether data is there
or not. It's just not as pretty as I was hoping I could make it!

Also, with regard to form design....I made a really nice and very large form
with my single table. I am afraid to use the 6 divided tables because I want
the data entry person to be forced to go through every field but I don't know
how to link them together to do that. But I am sure the 6 divided tables
must be an easier way. My single table form is so big it spreads across two
pages and tabbing to the second page requires you to move the slide bar to
the right to see it. Any suggestions there???

Thanks so much for your help.

 

[John Nurick]

In addition to the data structure considerations Sprinks has addressed,
you need to be sure that the database (as installed and used) will
comply with the relevant policies and laws on patient confidentiality
and data protection.

 

[jsmith]

Yes, thanks John. All identifying data will be blinded with the exception of
internal patient identifiers. We're all over the HIPAA thing, believe me.

Sprinks, with regard to your message about the combo box being limited to 20
choices, as I was moving fields over in the combo box wizard from my master
services table (that contains the 152 items) it gave me an error saying that
no more than 20 choices are allowed. Am I doing something wrong?

Thanks,
Jsmith

 

[Larry Linson]

Sprinks, with regard to your message about the
combo box being limited to 20 choices, as I
was moving fields over in the combo box
wizard from my master services table (that
contains the 152 items) it gave me an error
saying that no more than 20 choices are allowed.
Am I doing something wrong?

I'd say your Combo Box should have its Row Source as a Query against your
Master Services Table, retrieving the unique ID, service code, and
description. The wizard will guide you through the Setup. Set the Column
Width for the column containing the unique ID to zero, and the code or
description, whichever is next in the Query, will display.

You shouldn't be setting it up as a Value List -- that will only cause you
an onerous maintenance chore later on. And, I never ran into a 20-item
limit, even for Value List. There is a maximum total characters which isn't
easy to find in Help (I just looked) but which I believe is 32,768 give or
take a few.

Larry Linson
Microsoft Access MVP

 

[jsmith]

This is horrible. Nothing is working.

First of all, I don't see a one to many relationship. All relationships
between my 4 tables that Sprinks had me create just have a solid line type
relationship between each table. Maybe that's my first problem.

Then I created a query for my master services table and was able to insert
that query as a subform, which is great because all of my options (yes, all
152) are displayed for my data entry person to view at once - just like the
paper form we are currently using. (I could never get the combo box thing to
work, it only displayed values in my test entries, not the services
themselves.) But when I enter test data it wont bring up a new patient, it
just continues to scroll through my 152 choices over and over instead of
restarting in my main form so I can add another patient.

This isn't as easy as I thought it would be, but I do appreciate this forum
and those of you who take time to help.

JSmith