Db Relationship problem

[Stuart Jack]

Hi

I am building a database for patient information and am having a problem at
the earliest stage with the relationships. So far I have 3 main tables which
I want to join, 1st table "tblPatientData" contains - Autonumber field, CHI
field (which is the patients unique health number) and fields for the First &
Last name.

2nd table "tblPatientDatesRecord" contains several fields for the dates that
the patient was referred, sent for scan, placed on a list etc, as well as an
Autonumber field and again the CHI field.

3rd table is "tblTests", this stores the tests and dates of tests carried out,
again an Autonumber field, CHI field, Test field and TestDate field.

My problem I am coming up against is that I keep getting "No unique index
found for the referenced field of the primary table" message when I try to
join and enforce referential updtaes, deletes. If I play about with the
primary keys and indexes I have managed to join on one occaision and it
seemed to work, except that I could not enter the same patient on the system
more than once, as the CHI number was being used as a PK I guess, and that
may be something I need to be able to do.

I am a bit stumped at the moment and would appreciate being pointed in the
right direction, I have attached the mdb file in case it makes it easier to
see where I am going wrong.

Thanks

Stuart

 

[Jeff Boyce]

Your second table has "repeating fields", one for each date something
happened.

So when you or your customer decides to record yet another (important) date
something happened, you'll have to modify the table structure, update any
queries that referred to the previous structure, re-design the forms you use
based on that old structure, ditto for the reports, and review/update any
code modules you've created that reference the old structure... do you
REALLY want to do that much maintenance?!

Repeating fields are common and necessary ... in spreadsheets. In a
relational database like Access, you'll get more use of the features and
functions if you use a normalized data structure.

For example, the following structure would allow you to add a new
activity/date combination without modifying ANY objects:

tblPatient
PatientID (a primary key)
FName
LName
DOB
CHI
... (any other patient-specific attributes)

tlkpActivity (the things being measured/dated)
ActivityID (a primary key)
ActivityTitle
ActivityDescription
... (any other activity-specific attributes)

trelPatientActivity
PatientActivityID (PK)
PatientID (serves as a "foreign key", pointing to which Patient on
tblPatient)
ActivityID (serves as a "foreign key", pointing to which Activity on
tlkpActivity)
ActivityDate
... (any other attributes specific to this unique combination of
Patient and Activity ON THIS DATE)

Note that with this structure, a new activity gets added to the
tlkpActivity, and thereafter, is available via your data entry form (via a
combo-box) to be associated with a Patient on a Date.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Stuart Jack via AccessMonster.com]

Many thanks for the quick reply Jeff.

I will modify the db as you suggest and see how it goes, thanks again for the
help.

Stuart

Your second table has "repeating fields", one for each date something
happened.

So when you or your customer decides to record yet another (important) date
something happened, you'll have to modify the table structure, update any
queries that referred to the previous structure, re-design the forms you use
based on that old structure, ditto for the reports, and review/update any
code modules you've created that reference the old structure... do you
REALLY want to do that much maintenance?!

Repeating fields are common and necessary ... in spreadsheets. In a
relational database like Access, you'll get more use of the features and
functions if you use a normalized data structure.

For example, the following structure would allow you to add a new
activity/date combination without modifying ANY objects:

tblPatient
PatientID (a primary key)
FName
LName
DOB
CHI
... (any other patient-specific attributes)

tlkpActivity (the things being measured/dated)
ActivityID (a primary key)
ActivityTitle
ActivityDescription
... (any other activity-specific attributes)

trelPatientActivity
PatientActivityID (PK)
PatientID (serves as a "foreign key", pointing to which Patient on
tblPatient)
ActivityID (serves as a "foreign key", pointing to which Activity on
tlkpActivity)
ActivityDate
... (any other attributes specific to this unique combination of
Patient and Activity ON THIS DATE)

Note that with this structure, a new activity gets added to the
tlkpActivity, and thereafter, is available via your data entry form (via a
combo-box) to be associated with a Patient on a Date.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi

[quoted text clipped - 35 lines]

Stuart

 

[Stuart Jack via AccessMonster.com]

Hi Jeff

I have made the alterations to the DB as you suggested and the joins are in
place, I am however struggling to get my head round how this would work on a
form data entry basis, when I created a query with some info in the tables
nothing came up in the query so I am not sure if it is quite right yet.

Basically I need for the data inputing people to take a patients details and
enter it onto a form, at the moment they use spreadsheets! So they need to be
able to create a new pt entry, enter the waiting list data in the fields on
the form, and presumably the related tables will be sub forms for things like
the activities linked to that patient record, such as tests.

My vision of how this would work was, I would have the main form where they
would enter all clinic appt details etc, a subform would contain the Test
dates and types of tests carried out, this would be stored in the
trelPatientActivity table.


Many thanks for your help.

Stuart

Your second table has "repeating fields", one for each date something
happened.

So when you or your customer decides to record yet another (important) date
something happened, you'll have to modify the table structure, update any
queries that referred to the previous structure, re-design the forms you use
based on that old structure, ditto for the reports, and review/update any
code modules you've created that reference the old structure... do you
REALLY want to do that much maintenance?!

Repeating fields are common and necessary ... in spreadsheets. In a
relational database like Access, you'll get more use of the features and
functions if you use a normalized data structure.

For example, the following structure would allow you to add a new
activity/date combination without modifying ANY objects:

tblPatient
PatientID (a primary key)
FName
LName
DOB
CHI
... (any other patient-specific attributes)

tlkpActivity (the things being measured/dated)
ActivityID (a primary key)
ActivityTitle
ActivityDescription
... (any other activity-specific attributes)

trelPatientActivity
PatientActivityID (PK)
PatientID (serves as a "foreign key", pointing to which Patient on
tblPatient)
ActivityID (serves as a "foreign key", pointing to which Activity on
tlkpActivity)
ActivityDate
... (any other attributes specific to this unique combination of
Patient and Activity ON THIS DATE)

Note that with this structure, a new activity gets added to the
tlkpActivity, and thereafter, is available via your data entry form (via a
combo-box) to be associated with a Patient on a Date.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi

[quoted text clipped - 35 lines]

Stuart

 

[Jeff Boyce]

Stuart

I understand that your users may be familiar with using spreadsheets to
enter this information. Are they amenable to the idea that they would be
able to do more if their data were organized in a database application? Or
are they going to insist on keeping their spreadsheet orientation? Until
you know this answer, you might as well not pursue the Access approach...<g>

If you are saying that you'll need to create a way for the users to:

select a patient
select (or create) an appointment (with details), and
select (or create) TestType + TestDate data

you might be able to use nested sub-forms.

Your main form would allow selection of the patient (and see the patient
info).

Your sub-form would allow selection (or creation) of an appointment, with
details.

Your sub-sub-form would allow selection or creation of the TestType and
TestDate records.

This implies that you have a Patient table, a PatientAppointment table, and
a ?PatientAppointmentTest table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff

I have made the alterations to the DB as you suggested and the joins are
in
place, I am however struggling to get my head round how this would work on
a
form data entry basis, when I created a query with some info in the tables
nothing came up in the query so I am not sure if it is quite right yet.

Basically I need for the data inputing people to take a patients details
and
enter it onto a form, at the moment they use spreadsheets! So they need to
be
able to create a new pt entry, enter the waiting list data in the fields
on
the form, and presumably the related tables will be sub forms for things
like
the activities linked to that patient record, such as tests.

My vision of how this would work was, I would have the main form where
they
would enter all clinic appt details etc, a subform would contain the Test
dates and types of tests carried out, this would be stored in the
trelPatientActivity table.


Many thanks for your help.

Stuart

Your second table has "repeating fields", one for each date something
happened.

So when you or your customer decides to record yet another (important)
date
something happened, you'll have to modify the table structure, update any
queries that referred to the previous structure, re-design the forms you
use
based on that old structure, ditto for the reports, and review/update any
code modules you've created that reference the old structure... do you
REALLY want to do that much maintenance?!

Repeating fields are common and necessary ... in spreadsheets. In a
relational database like Access, you'll get more use of the features and
functions if you use a normalized data structure.

For example, the following structure would allow you to add a new
activity/date combination without modifying ANY objects:

tblPatient
PatientID (a primary key)
FName
LName
DOB
CHI
... (any other patient-specific attributes)

tlkpActivity (the things being measured/dated)
ActivityID (a primary key)
ActivityTitle
ActivityDescription
... (any other activity-specific attributes)

trelPatientActivity
PatientActivityID (PK)
PatientID (serves as a "foreign key", pointing to which Patient
on
tblPatient)
ActivityID (serves as a "foreign key", pointing to which Activity
on
tlkpActivity)
ActivityDate
... (any other attributes specific to this unique combination of
Patient and Activity ON THIS DATE)

Note that with this structure, a new activity gets added to the
tlkpActivity, and thereafter, is available via your data entry form (via a
combo-box) to be associated with a Patient on a Date.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi

[quoted text clipped - 35 lines]

Stuart

 

[Stuart Jack via AccessMonster.com]

Hi Jeff

Yes they are more used to Spreadsheets, it is amazing that such large
organisations such as NHS still run critical data storage in Spreadsheets,
but they do.

The users are more familiar with Excel but they will have to migrate to a
database solution as Excel is not up to the reporting and data analysis that
is needed, and my wife is their boss and she is an Access fan!

I do need to be able to select the pt, select/create appointments and record
test dates etc, I was planning as you suggest to use sub forms so have
started on that basis, and have created the appropriate tables, I hope - that
is what I am testing now.

Thanks for the feedback

Stuart.

Stuart

I understand that your users may be familiar with using spreadsheets to
enter this information. Are they amenable to the idea that they would be
able to do more if their data were organized in a database application? Or
are they going to insist on keeping their spreadsheet orientation? Until
you know this answer, you might as well not pursue the Access approach...<g>

If you are saying that you'll need to create a way for the users to:

select a patient
select (or create) an appointment (with details), and
select (or create) TestType + TestDate data

you might be able to use nested sub-forms.

Your main form would allow selection of the patient (and see the patient
info).

Your sub-form would allow selection (or creation) of an appointment, with
details.

Your sub-sub-form would allow selection or creation of the TestType and
TestDate records.

This implies that you have a Patient table, a PatientAppointment table, and
a ?PatientAppointmentTest table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff

[quoted text clipped - 86 lines]

 

[Tim Ferguson]

The users are more familiar with Excel but they will have to migrate
to a database solution as Excel is not up to the reporting and data
analysis that is needed, and my wife is their boss and she is an
Access fan!

I strongly suspect that it's the very last part that is distorting your
data processing. As for the first part, I take it that you were joking.
For list processing and data analysis, Excel will do _everything_ that
Access can without getting out of bed. Given some serious planning and
programming support it will completely flatten any database system. SQL
is indeed a pretty flexible tool, but it has its limitations. Excel can
do things in one expression that SQL is simply not capable of.

Filtering, sorting, and projections are all easier and more intuitive
than in Access.

Remember that Access's strengths are: its ability to model complex data
models; data integrity and validity checks that are enforced at db engine
level; tight user-level security; and robust multi-user support. If you
don't need these tools, then you are taking a backward step in moving
away from Excel.

Frankly, it's usually a bad day when a boss is a fan of any kind of
software tool. They should insist on solutions not on methods.

Just a thought


Tim F

 

[Stuart Jack via AccessMonster.com]

Tim

Yes it was a poor effort at humour!

I can appreciate where Excel beats databases, I am a fan of Access I must
admit for the reasons you point out, the system my wife needs does have to be
secure and have data validation, as well as being used by a number of users
across a network. ergo the reason for using it.

As to the wife, well she is a boss but also works with data extensively and
uses Excel where it is better suited than a database, this little project has
been dropped on her, the use of the spreadsheet version is not for analysis
but for data storage because thats what people had to hand.

You make some good points though Tim,

thanks.

Stuart