Not getting the data output I want

[jsmithjvh]

Hello,

I need help with my database. Here's the basic layout: I have a table for
patient data that is imported on a daily basis. That data is then queried to
eliminate null values and the query is the source for my main form. I then
have a subform based on a table with procedures that has no data in it. It
is strictly for data entry to include the date of service and checkboxes for
procedures rendered. I used the PtID (auto number) as my primary key in my
main form and the same name for a foreign key in the subform. Those fields
are used as the link parent/child fields. I have a one-to-many relationship
(patient = 1, procedures = many) although I could not enforce referential
integrity due to some error and I don't know if that matters. You use the
main form to choose the correct patient (from a combo box) and it displays
that pt data correctly. Then you tab over to the subform and add the date of
service and procedures (data entry set to no). I was hoping Access would
nicely group the information together so that I can see each patient, their
date of service, and their procedures rendered. Instead, it appears that it
writes the same data over and over using the first patient record in my
query. It also appears that the auto number is not changing, not increasing.
Just writing the same number to the procedures table over and over. Any way
to remedy this?

I'm also a little nervous about data integrity.....I'd love the main form to
copy some patient data to the subform, at least for visual confirmation, as
well as write that info into the procedures table, which would then include
all the data I need to do my job. Any way to do this? Or should I trust a
query based solely on the auto number as a linking field?

Thanks in advance for your help.
jsmith

 

[tina]

yes, referential integrity matters. if you're unable to enforce referential
integrity on a link between two tables, in the Relationships window, there's
something wrong. for a one-to-many relationship, check the following:

1. make sure the link goes *from* the parent table (patients) *to* the child
table (procedures done on each patient).
2. make sure that the data type of the foreign key field in the child table
matches the data type of the primary key field in the parent table. it
should be an exact match - EXCEPT where the primary key field in the parent
table is Autonumber; in that case, the data type of the foreign key field in
the child table must be Number, Long Integer.
3. make sure that the foreign key field in the child table has its' Index
property set to Yes (Duplicates OK), *not* to "no duplicates".

I'm also a little nervous about data integrity.....I'd love the main form to
copy some patient data to the subform, at least for visual confirmation, as
well as write that info into the procedures table

no, no, bad idea. don't store patient data in the
procedures-done-on-patients child table; it doesn't belong there, because it
doesn't describe an instance of a specific procedure performed on a specific
patient.

one way to verify that the form/subform link is working correctly *during
development*, is to display the primary key field in the parent form
(patients) and display the foreign key field in the subform (procedures
performed on patients). in a properly linked subform, the two key fields
should match, as you move from patient record to patient record. since those
fields would not normally be displayed during daily use, delete or hide them
once you're satisfied that the links are working correctly.

hth

 

[jsmithjvh]

Thank you Tina! Now I am getting somewhere.

I was able to fix the referential integrity issue by getting rid of some
conflicting data in my tables. But I still have a problem, the linked field
between my main form and subform (PtID) is not updating in my subform. So
for example, in my main form I choose a patient from my combo box and it
fills in the correct auto ptid number from that table (#1528, for example).
However, when I tab over to the subform (which again is based on a table with
no data in it so far), it does not copy the auto id number to the field with
the same name (also the linked child/master field). It is using the number
1503 for some reason, and does not change when I try to enter data for other
patients. It keeps using #1503 and just writes the same procedures to one
row in the table over and over each other. The PtID is my linked field, it
is the primary key in the master and the foreign key in the child, and I've
checked to make sure that it is an exact match (the Patient table is an auto
id, long integer, yes-no duplicates, the Procedures talbe is a number, long
integer, yes-duplicates ok).

Any idea what is going wrong?

Thanks for your help.
JSmith

 

[John W. Vinson]

It
is strictly for data entry to include the date of service and checkboxes for
procedures rendered. I used the PtID (auto number) as my primary key in my
main form and the same name for a foreign key in the subform. Those fields
are used as the link parent/child fields.

Just to add to Tina's excellent advice... consider restructuring your tables.

If you have one FIELD per procedure, you'll be in a tough condition when you
need to add, remove or change a procedure. What do you do? Change your table
structure, redesign all your queries, redesign all your forms, redesign all
your reports to accommodate the new field!? Ouch!

Much better is to model this as what it is: a Many (patients) to Many
(procedures) relationship, using a third table. There should be a (rather
small) Procedures table listing all of the procedures done at your clinic; the
Primary Key might be the insurance code for that procedure. Then you need a
PatientProcedures table with fields PatientID (foreign key to the PK of
Patients), ProcedureID (foreign key to the PK of Procedures), DateRendered,
and any other fields needed about *this* procedure on *this* patient on *this*
visit.

The Form would be based on the Patients table with the Subform based on
PatientProcedures, using the PatientID as the master/child link field; there'd
be a combo box on the subform bound to ProcedureID to select the procedure.

John W. Vinson [MVP]

 

[John W. Vinson]

However, when I tab over to the subform (which again is based on a table with
no data in it so far), it does not copy the auto id number to the field with
the same name (also the linked child/master field).

It won't and shouldn't do so until you enter data in some other field. Neither
a foreign key nor a master/child relationship automagically creates a new
record!

John W. Vinson [MVP]

 

[tina]

i'm slipping, John, how did i miss that?!

Just to add to Tina's excellent advice... consider restructuring your tables.

If you have one FIELD per procedure, you'll be in a tough condition when you
need to add, remove or change a procedure. What do you do? Change your table
structure, redesign all your queries, redesign all your forms, redesign all
your reports to accommodate the new field!? Ouch!

Much better is to model this as what it is: a Many (patients) to Many
(procedures) relationship, using a third table. There should be a (rather
small) Procedures table listing all of the procedures done at your clinic; the
Primary Key might be the insurance code for that procedure. Then you need a
PatientProcedures table with fields PatientID (foreign key to the PK of
Patients), ProcedureID (foreign key to the PK of Procedures), DateRendered,
and any other fields needed about *this* procedure on *this* patient on *this*
visit.

The Form would be based on the Patients table with the Subform based on
PatientProcedures, using the PatientID as the master/child link field; there'd
be a combo box on the subform bound to ProcedureID to select the procedure.

John W. Vinson [MVP]

 

[tina]

hmm, ok, i assume you mean that you're using an unbound combo box control in
the main form to choose a patient record, and after you choose, that record
is displayed in the main form as expected. but when you begin entering data
in a new record in the subform, the foreign key field is not populated with
the primary key value from the parent record. but if it also won't let you
enter more than one record in the subform....

well, sounds like a problem specifically with the subform. first, check the
RecordSource; is it based *solely* on the procedures table? if it is based
on a query, make sure that the query does NOT include the patient table.
also open the query directly and make sure that you can enter a new record;
if you can't, the problem is with the query itself, not the form.

if all is well with the form's RecordSource, check the settings of the
subform Form object: make sure the RecordsetType is Dynaset, and the
AllowAdditions property is set to Yes. also check the primary key field and
the foreign key field, at the table level, at the form level, and at the
control level (if those fields are bound to controls in the subform), to
make sure there are no validation rules set on either field.

also, note MVP John Vinson's remarks about table design elsewhere in this
thread. i strongly recommend that you take his suggestions to heart.

hth

 

[jsmithjvh]

Thanks again guys, you are obviously brilliant with this program. Me, on the
other hand, I am finding I am way out of my league here and about ready to
throw in the towel!

I have done as John suggested. I actually made revisions and now have FOUR
tables. One for patient data (imported daily), one for procedure data
(containing 26 procedure codes that our physicians use), one for the
physicians names (there are 2), and one for visit details that has now become
my "junction table". Each table has its own unique primary key (i.e., PtID,
ProcID, PhysID, VisitID) and the visit details junction table contains the
exact same field names as foreign keys as well as a field to enter the date
of service. They all have a one to many relationship to the visit details
table and referential integrity is enforced. I love the idea of it all, but
so far it is failing miserably. Here's what's going on:

1) I used the patient table as my main form and tried a couple different
ways (wizard and dragging) to add the visit details as my subform. The
subform is always visible in design view, but when I switch to form view,
it's gone. I've tried fiddling with the options in the properties and cannot
get it to show. Bizarre.

2) The visit details subform contains the foreign keys of the other tables,
and I have made the procedure and physician fields combo boxes, set the
record source to each individual table, and tried to point the field in the
right direction to pick up the name, not the auto number. I've played with
the column count and bound column and cannot get it to display the associated
name, it only displays the auto number. So I tried pulling different fields
into the subform from the different tables and still no luck. The combo box
for patient name in my main form works like a charm, so I can't figure out
what I'm doing wrong.

I haven't even made it far enough yet to be able to test data. But I am
assuming - if I can get the basic forms to work - that I will be able to run
a query to pull values from all tables based on what is entered in the
junction table to help me accurately bill for these procedures.....right?

Thanks again. I'm such a novice.
Jsmith

 

[John W. Vinson]

I have done as John suggested. I actually made revisions and now have FOUR
tables. One for patient data (imported daily), one for procedure data
(containing 26 procedure codes that our physicians use), one for the
physicians names (there are 2), and one for visit details that has now become
my "junction table". Each table has its own unique primary key (i.e., PtID,
ProcID, PhysID, VisitID) and the visit details junction table contains the
exact same field names as foreign keys as well as a field to enter the date
of service. They all have a one to many relationship to the visit details
table and referential integrity is enforced. I love the idea of it all, but
so far it is failing miserably. Here's what's going on:

That sounds like a correct design.

1) I used the patient table as my main form and tried a couple different
ways (wizard and dragging) to add the visit details as my subform. The
subform is always visible in design view, but when I switch to form view,
it's gone. I've tried fiddling with the options in the properties and cannot
get it to show. Bizarre.

What are you actually using as the Recordsource for the subform? It *sounds*
like you're basing the subform on a (non-updateable) Query, perhaps joining
all the tables - that gives the "blank form" symptom. The Subform should be
based on the Visits table, *only*.

2) The visit details subform contains the foreign keys of the other tables,
and I have made the procedure and physician fields combo boxes, set the
record source to each individual table, and tried to point the field in the
right direction to pick up the name, not the auto number. I've played with
the column count and bound column and cannot get it to display the associated
name, it only displays the auto number. So I tried pulling different fields
into the subform from the different tables and still no luck. The combo box
for patient name in my main form works like a charm, so I can't figure out
what I'm doing wrong.

Did you use the notorious Lookup Wizard misfeature in designing your visits
table? Don't. See
http://www.mvps.org/access/lookupfields.htm
for a critique.

You should certainly use Combo Boxes *on the Form* but it is neither necessary
nor (particularly) helpful to use them in a Table.

I haven't even made it far enough yet to be able to test data. But I am
assuming - if I can get the basic forms to work - that I will be able to run
a query to pull values from all tables based on what is entered in the
junction table to help me accurately bill for these procedures.....right?

Yep.

John W. Vinson [MVP]

 

[tina]

don't give up, hon. i'm guessing you're like a lot of newbies who (as i did)
began to learn the software without learning anything about relational
design principles first. that's definitely putting the cart before the horse
and, as you have found, makes it so much harder to put together a working
database. suggest you stick with John; if anyone can help you, he can. and
any other time you post a question and get a response from an Access MVP,
just consider that you've struck gold, and try to get as much out of that
vein as you can! <g>

also, suggest you take a look at the following website; i built it
specifically for newbies, so you may find some useful info there.
http://home.att.net/~california.db/tips.html

hth