Populating multiple table from a single form

Hi Ed

The patient has not to be identified, the examID is the patient.

Sorry if this is a dumb question but how do you set the table relationships?

Regards,
Rob

"Ed Warren" wrote:

I think you also need a way to identify the 'patient' in the tbl Exam. (you
have the OperatorID, but nothing about the patient) ;

Assuming you have also set the required table relationships.

tblExamSite m:1 to tblExam and m:1 to tblSites
tbleLesions m:1 to tblExamSite and m:1 to tblSymptoms
'--------------------------------------------------------------------note
Note: For all of the ComboBoxes below I would use something like

Select SiteID, SiteText from tblSites Orderby SiteText
(save as lkpSite)

ListSource: query -- lkpSite
Bound Column: 1 (SiteID)
Number of Columns:2
Column Widths; "0";"2" (this says hide the first column, then show the
second)

So for the Site Case you would see
Site 1
Site 2
Site 3
etc.
and not
1 Site 1
2 Site 2
3 Site 3
'-----------------------------------------------------------------------

1. build a form for tblSymptoms (default view Continuous Forms) [As a start
use the autoformwizard, tabular]
Use a comboBox for the Symptoms Field (get the list from the
tbleLesions)
Put all the other fields in the form footer section, then hide them by
setting the form footer visible property to false.
2. build a form for tblExamSite (default view Single Form) [As a start use
the autofromwizard, columnar]
Use a comboBox for the Site Field (get the list from the tblSites)
Move all the other fields to the form footer and set the footer visible
property to false
Add the form build above as a subform under the site comboBox.
3. Build a form for tblExam (default view Single Form)
Hide what's not needed
Add the form from 2 above as a subform.


You should now have a nested form.

Exam (single)
Exam Site (single)
Symptom (list)

If you look at the bottom of the form you will have three sets of record
navigation buttons (nested)
the outside ones move through exams
the next set move through Exam Sites
the last set moves through Symptoms

Add an Exam
Add a site
Add symptoms
Add a site
Add symptoms
Add a site
Add symptoms
Add an Exam
etc.

Now you can go back and start 'dressing things up', add a set of record
navigaton buttons to each form, etc, different colored backgrounds, etc.

From here you can get as fancy as you like, depending on you interest and
level of knowledge of visual basic. Rather than using sub-forms you can
use synchroized forms. You could build a form that uses option buttons for
data input. Build a temp table for datainput that provides a blank for each
possible combination of site/symptom.

Hope this is of some help.

Ed Warren

"Rob McKerlie" wrote in message
...
Hi Ed

Following your instructions (I hope) I have produced a total of 5 tables:

tblSites (lookup table)
All 20 sites of the mouth

tbleLesions (lookup table)
All 7 symptoms

tblExam
ExamID (autonumber)
OperatorID (initials)
DateofExam

tblExamSite
ExamSiteID (autonumber)
ExamID (lookup from tblExam:ExamID)
SiteID (Lookup from tblSites)

tblSymptoms
ExamSiteSymptomID (autonumber)
ExamSiteID (lookup from tblExamSite:ExamSiteID)
Symptoms (lookup from tblLesions)

My problem is how to produce a form that will allow entry of multiple
symptoms for an individual site without becoming massive. I have tried
setting up a form containing the exam details with 2 subforms for the site
and symptoms.

Any ideas how to simplify the data entry?

"Ed Warren" wrote:


Each patient has one and only one mouth
Each mouth can have many sites
Each site can have many symptoms

So we need a set of lookup tables:

A table to track people (400 + rows)
A table to identify sites (20 rows)
A table to identify symptoms (7 rows)

Patients can have many exams

Each Exam covers many sites

Each Site can have many symptoms

So we will need at least the following:

tblExam
ExamId PK
PatientID Fk (refers to the patient table above)
DateOfExam

1: many to:

tblExamSite
ExamSiteID Pk
ExamID fk (refers to the exam being recorded)
SiteID fk (refers to the lookup site table above)

1 to many to:

tblExamSiteSymptom

ExamSiteSymptomID PK
ExamSiteID fk
SymptomID fk (refers to lookup symptom table above)

Of course each table could include additional data properly related to
the
level of exam, notes, scores etc.

Hope this helps

Ed Warren.

"Rob McKerlie" wrote in message
...
Hi Harold

The only problem I see is that each location in the mouth can have more
than
one symptom e.g. the floor of the mouth may be 'red' as well as show
'swelling'. So for each location there could in theory be the need to
store
7 pieces of data. For each patient that means 7 poeces of data
multiplied
by
the 20 locations.

"Harold via AccessMonster.com" wrote:

It seems like your will need two tables. One for patient info, (name,
id
number, and such), and a table for the locations within the mouth. In
the
patient info table you can use the id number as a primary key and link
it
to
the location in the mouth table. On the form you could use a list box
with
the different options to populate the table.

What do you think?

Rob McKerlie wrote:
I should say that the patient will only be identified by a code, no
patient
related data will be recorded.

Hi,
How do the bits of data relate to the 20 items? It sounds like you
need more
[quoted text clipped - 15 lines]
Thanks in advance
Rob


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200508/1

Not to belabor the point but what happens when you see the same patient next
year? Another unique ExamID is generated (autoinc). So you will not be able
to capture the exams through time for the patient.

To set relationships. Right click anywhere on the database window and
select Relationships. That will open a relationship window, add tables,
drag from one to the other to create the relationships.

Ed Warren.

"Rob McKerlie" wrote in message
...
Hi Ed

The patient has not to be identified, the examID is the patient.

Sorry if this is a dumb question but how do you set the table
relationships?

Regards,
Rob

"Ed Warren" wrote:

I think you also need a way to identify the 'patient' in the tbl Exam.
(you
have the OperatorID, but nothing about the patient) ;

Assuming you have also set the required table relationships.

tblExamSite m:1 to tblExam and m:1 to tblSites
tbleLesions m:1 to tblExamSite and m:1 to tblSymptoms
'--------------------------------------------------------------------note
Note: For all of the ComboBoxes below I would use something like

Select SiteID, SiteText from tblSites Orderby SiteText
(save as lkpSite)

ListSource: query -- lkpSite
Bound Column: 1 (SiteID)
Number of Columns:2
Column Widths; "0";"2" (this says hide the first column, then show the
second)

So for the Site Case you would see
Site 1
Site 2
Site 3
etc.
and not
1 Site 1
2 Site 2
3 Site 3
'-----------------------------------------------------------------------

1. build a form for tblSymptoms (default view Continuous Forms) [As a
start
use the autoformwizard, tabular]
Use a comboBox for the Symptoms Field (get the list from the
tbleLesions)
Put all the other fields in the form footer section, then hide them
by
setting the form footer visible property to false.
2. build a form for tblExamSite (default view Single Form) [As a start
use
the autofromwizard, columnar]
Use a comboBox for the Site Field (get the list from the tblSites)
Move all the other fields to the form footer and set the footer
visible
property to false
Add the form build above as a subform under the site comboBox.
3. Build a form for tblExam (default view Single Form)
Hide what's not needed
Add the form from 2 above as a subform.


You should now have a nested form.

Exam (single)
Exam Site (single)
Symptom (list)

If you look at the bottom of the form you will have three sets of record
navigation buttons (nested)
the outside ones move through exams
the next set move through Exam Sites
the last set moves through Symptoms

Add an Exam
Add a site
Add symptoms
Add a site
Add symptoms
Add a site
Add symptoms
Add an Exam
etc.

Now you can go back and start 'dressing things up', add a set of record
navigaton buttons to each form, etc, different colored backgrounds, etc.

From here you can get as fancy as you like, depending on you interest and
level of knowledge of visual basic. Rather than using sub-forms you can
use synchroized forms. You could build a form that uses option buttons
for
data input. Build a temp table for datainput that provides a blank for
each
possible combination of site/symptom.

Hope this is of some help.

Ed Warren

"Rob McKerlie" wrote in message
...
Hi Ed

Following your instructions (I hope) I have produced a total of 5
tables:

tblSites (lookup table)
All 20 sites of the mouth

tbleLesions (lookup table)
All 7 symptoms

tblExam
ExamID (autonumber)
OperatorID (initials)
DateofExam

tblExamSite
ExamSiteID (autonumber)
ExamID (lookup from tblExam:ExamID)
SiteID (Lookup from tblSites)

tblSymptoms
ExamSiteSymptomID (autonumber)
ExamSiteID (lookup from tblExamSite:ExamSiteID)
Symptoms (lookup from tblLesions)

My problem is how to produce a form that will allow entry of multiple
symptoms for an individual site without becoming massive. I have tried
setting up a form containing the exam details with 2 subforms for the
site
and symptoms.

Any ideas how to simplify the data entry?

"Ed Warren" wrote:


Each patient has one and only one mouth
Each mouth can have many sites
Each site can have many symptoms

So we need a set of lookup tables:

A table to track people (400 + rows)
A table to identify sites (20 rows)
A table to identify symptoms (7 rows)

Patients can have many exams

Each Exam covers many sites

Each Site can have many symptoms

So we will need at least the following:

tblExam
ExamId PK
PatientID Fk (refers to the patient table above)
DateOfExam

1: many to:

tblExamSite
ExamSiteID Pk
ExamID fk (refers to the exam being recorded)
SiteID fk (refers to the lookup site table above)

1 to many to:

tblExamSiteSymptom

ExamSiteSymptomID PK
ExamSiteID fk
SymptomID fk (refers to lookup symptom table above)

Of course each table could include additional data properly related to
the
level of exam, notes, scores etc.

Hope this helps

Ed Warren.

"Rob McKerlie" wrote in
message
...
Hi Harold

The only problem I see is that each location in the mouth can have
more
than
one symptom e.g. the floor of the mouth may be 'red' as well as show
'swelling'. So for each location there could in theory be the need
to
store
7 pieces of data. For each patient that means 7 poeces of data
multiplied
by
the 20 locations.

"Harold via AccessMonster.com" wrote:

It seems like your will need two tables. One for patient info,
(name,
id
number, and such), and a table for the locations within the mouth.
In
the
patient info table you can use the id number as a primary key and
link
it
to
the location in the mouth table. On the form you could use a list
box
with
the different options to populate the table.

What do you think?

Rob McKerlie wrote:
I should say that the patient will only be identified by a code,
no
patient
related data will be recorded.

Hi,
How do the bits of data relate to the 20 items? It sounds like
you
need more
[quoted text clipped - 15 lines]
Thanks in advance
Rob


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200508/1

Hi Ed

Point taken, I have inserted a seperated patientID.

All done and working well, many thanks.

A suplementary question relating to useability, is there any way of
preventing an operator from say for patient 'x' entering 2 records for
'floor_of_mouth'?

I ain't any good when it comes to Visual Basic, so although from an operator
point of view having a grid input form would prevent duplication, it is not
going to happen.

Rob.

"Ed Warren" wrote:

Not to belabor the point but what happens when you see the same patient next
year? Another unique ExamID is generated (autoinc). So you will not be able
to capture the exams through time for the patient.

To set relationships. Right click anywhere on the database window and
select Relationships. That will open a relationship window, add tables,
drag from one to the other to create the relationships.

Ed Warren.

"Rob McKerlie" wrote in message
...
Hi Ed

The patient has not to be identified, the examID is the patient.

Sorry if this is a dumb question but how do you set the table
relationships?

Regards,
Rob

"Ed Warren" wrote:

I think you also need a way to identify the 'patient' in the tbl Exam.
(you
have the OperatorID, but nothing about the patient) ;

Assuming you have also set the required table relationships.

tblExamSite m:1 to tblExam and m:1 to tblSites
tbleLesions m:1 to tblExamSite and m:1 to tblSymptoms
'--------------------------------------------------------------------note
Note: For all of the ComboBoxes below I would use something like

Select SiteID, SiteText from tblSites Orderby SiteText
(save as lkpSite)

ListSource: query -- lkpSite
Bound Column: 1 (SiteID)
Number of Columns:2
Column Widths; "0";"2" (this says hide the first column, then show the
second)

So for the Site Case you would see
Site 1
Site 2
Site 3
etc.
and not
1 Site 1
2 Site 2
3 Site 3
'-----------------------------------------------------------------------

1. build a form for tblSymptoms (default view Continuous Forms) [As a
start
use the autoformwizard, tabular]
Use a comboBox for the Symptoms Field (get the list from the
tbleLesions)
Put all the other fields in the form footer section, then hide them
by
setting the form footer visible property to false.
2. build a form for tblExamSite (default view Single Form) [As a start
use
the autofromwizard, columnar]
Use a comboBox for the Site Field (get the list from the tblSites)
Move all the other fields to the form footer and set the footer
visible
property to false
Add the form build above as a subform under the site comboBox.
3. Build a form for tblExam (default view Single Form)
Hide what's not needed
Add the form from 2 above as a subform.


You should now have a nested form.

Exam (single)
Exam Site (single)
Symptom (list)

If you look at the bottom of the form you will have three sets of record
navigation buttons (nested)
the outside ones move through exams
the next set move through Exam Sites
the last set moves through Symptoms

Add an Exam
Add a site
Add symptoms
Add a site
Add symptoms
Add a site
Add symptoms
Add an Exam
etc.

Now you can go back and start 'dressing things up', add a set of record
navigaton buttons to each form, etc, different colored backgrounds, etc.

From here you can get as fancy as you like, depending on you interest and
level of knowledge of visual basic. Rather than using sub-forms you can
use synchroized forms. You could build a form that uses option buttons
for
data input. Build a temp table for datainput that provides a blank for
each
possible combination of site/symptom.

Hope this is of some help.

Ed Warren

"Rob McKerlie" wrote in message
...
Hi Ed

Following your instructions (I hope) I have produced a total of 5
tables:

tblSites (lookup table)
All 20 sites of the mouth

tbleLesions (lookup table)
All 7 symptoms

tblExam
ExamID (autonumber)
OperatorID (initials)
DateofExam

tblExamSite
ExamSiteID (autonumber)
ExamID (lookup from tblExam:ExamID)
SiteID (Lookup from tblSites)

tblSymptoms
ExamSiteSymptomID (autonumber)
ExamSiteID (lookup from tblExamSite:ExamSiteID)
Symptoms (lookup from tblLesions)

My problem is how to produce a form that will allow entry of multiple
symptoms for an individual site without becoming massive. I have tried
setting up a form containing the exam details with 2 subforms for the
site
and symptoms.

Any ideas how to simplify the data entry?

"Ed Warren" wrote:


Each patient has one and only one mouth
Each mouth can have many sites
Each site can have many symptoms

So we need a set of lookup tables:

A table to track people (400 + rows)
A table to identify sites (20 rows)
A table to identify symptoms (7 rows)

Patients can have many exams

Each Exam covers many sites

Each Site can have many symptoms

So we will need at least the following:

tblExam
ExamId PK
PatientID Fk (refers to the patient table above)
DateOfExam

1: many to:

tblExamSite
ExamSiteID Pk
ExamID fk (refers to the exam being recorded)
SiteID fk (refers to the lookup site table above)

1 to many to:

tblExamSiteSymptom

ExamSiteSymptomID PK
ExamSiteID fk
SymptomID fk (refers to lookup symptom table above)

Of course each table could include additional data properly related to
the
level of exam, notes, scores etc.

Hope this helps

Ed Warren.

"Rob McKerlie" wrote in
message
...
Hi Harold

The only problem I see is that each location in the mouth can have
more
than
one symptom e.g. the floor of the mouth may be 'red' as well as show
'swelling'. So for each location there could in theory be the need
to
store
7 pieces of data. For each patient that means 7 poeces of data
multiplied
by
the 20 locations.

"Harold via AccessMonster.com" wrote:

It seems like your will need two tables. One for patient info,
(name,
id
number, and such), and a table for the locations within the mouth.
In
the
patient info table you can use the id number as a primary key and
link
it
to
the location in the mouth table. On the form you could use a list
box
with
the different options to populate the table.

What do you think?

Rob McKerlie wrote:
I should say that the patient will only be identified by a code,
no
patient
related data will be recorded.

Hi,
How do the bits of data relate to the 20 items? It sounds like
you
need more
[quoted text clipped - 15 lines]
Thanks in advance
Rob


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200508/1

Yes you go to the table design for the table symptoms and build a
combination key index for

ExamSiteID (lookup from tblExamSite:ExamSiteID)
Symptoms (lookup from tblLesions)

And set it a no duplicates: (this says for an exam you can have one and
only one occurance of the SymptomId)

Use the same logic to make sure there is no more than one occurrence of each
siteID in an exam.

Best of luck

Ed Warren

"Rob McKerlie" wrote in message
...
Hi Ed

Point taken, I have inserted a seperated patientID.

All done and working well, many thanks.

A suplementary question relating to useability, is there any way of
preventing an operator from say for patient 'x' entering 2 records for
'floor_of_mouth'?

I ain't any good when it comes to Visual Basic, so although from an
operator
point of view having a grid input form would prevent duplication, it is
not
going to happen.

Rob.

"Ed Warren" wrote:

Not to belabor the point but what happens when you see the same patient
next
year? Another unique ExamID is generated (autoinc). So you will not be
able
to capture the exams through time for the patient.

To set relationships. Right click anywhere on the database window and
select Relationships. That will open a relationship window, add tables,
drag from one to the other to create the relationships.

Ed Warren.

"Rob McKerlie" wrote in message
...
Hi Ed

The patient has not to be identified, the examID is the patient.

Sorry if this is a dumb question but how do you set the table
relationships?

Regards,
Rob

"Ed Warren" wrote:

I think you also need a way to identify the 'patient' in the tbl Exam.
(you
have the OperatorID, but nothing about the patient) ;

Assuming you have also set the required table relationships.

tblExamSite m:1 to tblExam and m:1 to tblSites
tbleLesions m:1 to tblExamSite and m:1 to tblSymptoms
'--------------------------------------------------------------------note
Note: For all of the ComboBoxes below I would use something like

Select SiteID, SiteText from tblSites Orderby SiteText
(save as lkpSite)

ListSource: query -- lkpSite
Bound Column: 1 (SiteID)
Number of Columns:2
Column Widths; "0";"2" (this says hide the first column, then show
the
second)

So for the Site Case you would see
Site 1
Site 2
Site 3
etc.
and not
1 Site 1
2 Site 2
3 Site 3
'-----------------------------------------------------------------------

1. build a form for tblSymptoms (default view Continuous Forms) [As a
start
use the autoformwizard, tabular]
Use a comboBox for the Symptoms Field (get the list from the
tbleLesions)
Put all the other fields in the form footer section, then hide
them
by
setting the form footer visible property to false.
2. build a form for tblExamSite (default view Single Form) [As a start
use
the autofromwizard, columnar]
Use a comboBox for the Site Field (get the list from the tblSites)
Move all the other fields to the form footer and set the footer
visible
property to false
Add the form build above as a subform under the site comboBox.
3. Build a form for tblExam (default view Single Form)
Hide what's not needed
Add the form from 2 above as a subform.


You should now have a nested form.

Exam (single)
Exam Site (single)
Symptom (list)

If you look at the bottom of the form you will have three sets of
record
navigation buttons (nested)
the outside ones move through exams
the next set move through Exam Sites
the last set moves through Symptoms

Add an Exam
Add a site
Add symptoms
Add a site
Add symptoms
Add a site
Add symptoms
Add an Exam
etc.

Now you can go back and start 'dressing things up', add a set of
record
navigaton buttons to each form, etc, different colored backgrounds,
etc.

From here you can get as fancy as you like, depending on you interest
and
level of knowledge of visual basic. Rather than using sub-forms you
can
use synchroized forms. You could build a form that uses option
buttons
for
data input. Build a temp table for datainput that provides a blank
for
each
possible combination of site/symptom.

Hope this is of some help.

Ed Warren

"Rob McKerlie" wrote in
message
...
Hi Ed

Following your instructions (I hope) I have produced a total of 5
tables:

tblSites (lookup table)
All 20 sites of the mouth

tbleLesions (lookup table)
All 7 symptoms

tblExam
ExamID (autonumber)
OperatorID (initials)
DateofExam

tblExamSite
ExamSiteID (autonumber)
ExamID (lookup from tblExam:ExamID)
SiteID (Lookup from tblSites)

tblSymptoms
ExamSiteSymptomID (autonumber)
ExamSiteID (lookup from tblExamSite:ExamSiteID)
Symptoms (lookup from tblLesions)

My problem is how to produce a form that will allow entry of
multiple
symptoms for an individual site without becoming massive. I have
tried
setting up a form containing the exam details with 2 subforms for
the
site
and symptoms.

Any ideas how to simplify the data entry?

"Ed Warren" wrote:


Each patient has one and only one mouth
Each mouth can have many sites
Each site can have many symptoms

So we need a set of lookup tables:

A table to track people (400 + rows)
A table to identify sites (20 rows)
A table to identify symptoms (7 rows)

Patients can have many exams

Each Exam covers many sites

Each Site can have many symptoms

So we will need at least the following:

tblExam
ExamId PK
PatientID Fk (refers to the patient table above)
DateOfExam

1: many to:

tblExamSite
ExamSiteID Pk
ExamID fk (refers to the exam being recorded)
SiteID fk (refers to the lookup site table above)

1 to many to:

tblExamSiteSymptom

ExamSiteSymptomID PK
ExamSiteID fk
SymptomID fk (refers to lookup symptom table above)

Of course each table could include additional data properly related
to
the
level of exam, notes, scores etc.

Hope this helps

Ed Warren.

"Rob McKerlie" wrote in
message
...
Hi Harold

The only problem I see is that each location in the mouth can
have
more
than
one symptom e.g. the floor of the mouth may be 'red' as well as
show
'swelling'. So for each location there could in theory be the
need
to
store
7 pieces of data. For each patient that means 7 poeces of data
multiplied
by
the 20 locations.

"Harold via AccessMonster.com" wrote:

It seems like your will need two tables. One for patient info,
(name,
id
number, and such), and a table for the locations within the
mouth.
In
the
patient info table you can use the id number as a primary key
and
link
it
to
the location in the mouth table. On the form you could use a
list
box
with
the different options to populate the table.

What do you think?

Rob McKerlie wrote:
I should say that the patient will only be identified by a
code,
no
patient
related data will be recorded.

Hi,
How do the bits of data relate to the 20 items? It sounds
like
you
need more
[quoted text clipped - 15 lines]
Thanks in advance
Rob


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200508/1

Sorry Ed,

I have the table 'symptoms with
ExamSIteID (lookup from tblExamSite:ExamSiteID)
Symptoms (lookup fro tblLesions)

How do I create a combination key?

Rob.

"Ed Warren" wrote:

Yes you go to the table design for the table symptoms and build a
combination key index for

ExamSiteID (lookup from tblExamSite:ExamSiteID)
Symptoms (lookup from tblLesions)

And set it a no duplicates: (this says for an exam you can have one and
only one occurance of the SymptomId)

Use the same logic to make sure there is no more than one occurrence of each
siteID in an exam.

Best of luck

Ed Warren

"Rob McKerlie" wrote in message
...
Hi Ed

Point taken, I have inserted a seperated patientID.

All done and working well, many thanks.

A suplementary question relating to useability, is there any way of
preventing an operator from say for patient 'x' entering 2 records for
'floor_of_mouth'?

I ain't any good when it comes to Visual Basic, so although from an
operator
point of view having a grid input form would prevent duplication, it is
not
going to happen.

Rob.

"Ed Warren" wrote:

Not to belabor the point but what happens when you see the same patient
next
year? Another unique ExamID is generated (autoinc). So you will not be
able
to capture the exams through time for the patient.

To set relationships. Right click anywhere on the database window and
select Relationships. That will open a relationship window, add tables,
drag from one to the other to create the relationships.

Ed Warren.

"Rob McKerlie" wrote in message
...
Hi Ed

The patient has not to be identified, the examID is the patient.

Sorry if this is a dumb question but how do you set the table
relationships?

Regards,
Rob

"Ed Warren" wrote:

I think you also need a way to identify the 'patient' in the tbl Exam.
(you
have the OperatorID, but nothing about the patient) ;

Assuming you have also set the required table relationships.

tblExamSite m:1 to tblExam and m:1 to tblSites
tbleLesions m:1 to tblExamSite and m:1 to tblSymptoms
'--------------------------------------------------------------------note
Note: For all of the ComboBoxes below I would use something like

Select SiteID, SiteText from tblSites Orderby SiteText
(save as lkpSite)

ListSource: query -- lkpSite
Bound Column: 1 (SiteID)
Number of Columns:2
Column Widths; "0";"2" (this says hide the first column, then show
the
second)

So for the Site Case you would see
Site 1
Site 2
Site 3
etc.
and not
1 Site 1
2 Site 2
3 Site 3
'-----------------------------------------------------------------------

1. build a form for tblSymptoms (default view Continuous Forms) [As a
start
use the autoformwizard, tabular]
Use a comboBox for the Symptoms Field (get the list from the
tbleLesions)
Put all the other fields in the form footer section, then hide
them
by
setting the form footer visible property to false.
2. build a form for tblExamSite (default view Single Form) [As a start
use
the autofromwizard, columnar]
Use a comboBox for the Site Field (get the list from the tblSites)
Move all the other fields to the form footer and set the footer
visible
property to false
Add the form build above as a subform under the site comboBox.
3. Build a form for tblExam (default view Single Form)
Hide what's not needed
Add the form from 2 above as a subform.


You should now have a nested form.

Exam (single)
Exam Site (single)
Symptom (list)

If you look at the bottom of the form you will have three sets of
record
navigation buttons (nested)
the outside ones move through exams
the next set move through Exam Sites
the last set moves through Symptoms

Add an Exam
Add a site
Add symptoms
Add a site
Add symptoms
Add a site
Add symptoms
Add an Exam
etc.

Now you can go back and start 'dressing things up', add a set of
record
navigaton buttons to each form, etc, different colored backgrounds,
etc.

From here you can get as fancy as you like, depending on you interest
and
level of knowledge of visual basic. Rather than using sub-forms you
can
use synchroized forms. You could build a form that uses option
buttons
for
data input. Build a temp table for datainput that provides a blank
for
each
possible combination of site/symptom.

Hope this is of some help.

Ed Warren

"Rob McKerlie" wrote in
message
...
Hi Ed

Following your instructions (I hope) I have produced a total of 5
tables:

tblSites (lookup table)
All 20 sites of the mouth

tbleLesions (lookup table)
All 7 symptoms

tblExam
ExamID (autonumber)
OperatorID (initials)
DateofExam

tblExamSite
ExamSiteID (autonumber)
ExamID (lookup from tblExam:ExamID)
SiteID (Lookup from tblSites)

tblSymptoms
ExamSiteSymptomID (autonumber)
ExamSiteID (lookup from tblExamSite:ExamSiteID)
Symptoms (lookup from tblLesions)

My problem is how to produce a form that will allow entry of
multiple
symptoms for an individual site without becoming massive. I have
tried
setting up a form containing the exam details with 2 subforms for
the
site
and symptoms.

Any ideas how to simplify the data entry?

"Ed Warren" wrote:


Each patient has one and only one mouth
Each mouth can have many sites
Each site can have many symptoms

So we need a set of lookup tables:

A table to track people (400 + rows)
A table to identify sites (20 rows)
A table to identify symptoms (7 rows)

Patients can have many exams

Each Exam covers many sites

Each Site can have many symptoms

So we will need at least the following:

tblExam
ExamId PK
PatientID Fk (refers to the patient table above)
DateOfExam

1: many to:

tblExamSite
ExamSiteID Pk
ExamID fk (refers to the exam being recorded)
SiteID fk (refers to the lookup site table above)

1 to many to:

tblExamSiteSymptom

ExamSiteSymptomID PK
ExamSiteID fk
SymptomID fk (refers to lookup symptom table above)

Of course each table could include additional data properly related
to
the
level of exam, notes, scores etc.

Hope this helps

Ed Warren.

"Rob McKerlie" wrote in
message
...
Hi Harold

The only problem I see is that each location in the mouth can
have
more
than
one symptom e.g. the floor of the mouth may be 'red' as well as
show
'swelling'. So for each location there could in theory be the
need
to
store
7 pieces of data. For each patient that means 7 poeces of data
multiplied
by
the 20 locations.

"Harold via AccessMonster.com" wrote:

It seems like your will need two tables. One for patient info,
(name,
id
number, and such), and a table for the locations within the
mouth.
In
the
patient info table you can use the id number as a primary key
and
link
it
to
the location in the mouth table. On the form you could use a
list
box
with
the different options to populate the table.

What do you think?

Rob McKerlie wrote:
I should say that the patient will only be identified by a
code,
no
patient
related data will be recorded.

Select Help
Search for Indexes
Select create a multiple field index
Follow the steps listed.

Ed Warren

"Rob McKerlie" wrote in message
...
Sorry Ed,

I have the table 'symptoms with
ExamSIteID (lookup from tblExamSite:ExamSiteID)
Symptoms (lookup fro tblLesions)

How do I create a combination key?

Rob.

"Ed Warren" wrote:

Yes you go to the table design for the table symptoms and build a
combination key index for

ExamSiteID (lookup from tblExamSite:ExamSiteID)
Symptoms (lookup from tblLesions)

And set it a no duplicates: (this says for an exam you can have one and
only one occurance of the SymptomId)

Use the same logic to make sure there is no more than one occurrence of
each
siteID in an exam.

Best of luck

Ed Warren

"Rob McKerlie" wrote in message
...
Hi Ed

Point taken, I have inserted a seperated patientID.

All done and working well, many thanks.

A suplementary question relating to useability, is there any way of
preventing an operator from say for patient 'x' entering 2 records for
'floor_of_mouth'?

I ain't any good when it comes to Visual Basic, so although from an
operator
point of view having a grid input form would prevent duplication, it is
not
going to happen.

Rob.

"Ed Warren" wrote:

Not to belabor the point but what happens when you see the same
patient
next
year? Another unique ExamID is generated (autoinc). So you will not
be
able
to capture the exams through time for the patient.

To set relationships. Right click anywhere on the database window and
select Relationships. That will open a relationship window, add
tables,
drag from one to the other to create the relationships.

Ed Warren.

"Rob McKerlie" wrote in
message
...
Hi Ed

The patient has not to be identified, the examID is the patient.

Sorry if this is a dumb question but how do you set the table
relationships?

Regards,
Rob

"Ed Warren" wrote:

I think you also need a way to identify the 'patient' in the tbl
Exam.
(you
have the OperatorID, but nothing about the patient) ;

Assuming you have also set the required table relationships.

tblExamSite m:1 to tblExam and m:1 to tblSites
tbleLesions m:1 to tblExamSite and m:1 to tblSymptoms
'--------------------------------------------------------------------note
Note: For all of the ComboBoxes below I would use something like

Select SiteID, SiteText from tblSites Orderby SiteText
(save as lkpSite)

ListSource: query -- lkpSite
Bound Column: 1 (SiteID)
Number of Columns:2
Column Widths; "0";"2" (this says hide the first column, then show
the
second)

So for the Site Case you would see
Site 1
Site 2
Site 3
etc.
and not
1 Site 1
2 Site 2
3 Site 3
'-----------------------------------------------------------------------

1. build a form for tblSymptoms (default view Continuous Forms) [As
a
start
use the autoformwizard, tabular]
Use a comboBox for the Symptoms Field (get the list from the
tbleLesions)
Put all the other fields in the form footer section, then hide
them
by
setting the form footer visible property to false.
2. build a form for tblExamSite (default view Single Form) [As a
start
use
the autofromwizard, columnar]
Use a comboBox for the Site Field (get the list from the
tblSites)
Move all the other fields to the form footer and set the footer
visible
property to false
Add the form build above as a subform under the site comboBox.
3. Build a form for tblExam (default view Single Form)
Hide what's not needed
Add the form from 2 above as a subform.


You should now have a nested form.

Exam (single)
Exam Site (single)
Symptom (list)

If you look at the bottom of the form you will have three sets of
record
navigation buttons (nested)
the outside ones move through exams
the next set move through Exam Sites
the last set moves through Symptoms

Add an Exam
Add a site
Add symptoms
Add a site
Add symptoms
Add a site
Add symptoms
Add an Exam
etc.

Now you can go back and start 'dressing things up', add a set of
record
navigaton buttons to each form, etc, different colored backgrounds,
etc.

From here you can get as fancy as you like, depending on you
interest
and
level of knowledge of visual basic. Rather than using sub-forms
you
can
use synchroized forms. You could build a form that uses option
buttons
for
data input. Build a temp table for datainput that provides a blank
for
each
possible combination of site/symptom.

Hope this is of some help.

Ed Warren

"Rob McKerlie" wrote in
message
...
Hi Ed

Following your instructions (I hope) I have produced a total of 5
tables:

tblSites (lookup table)
All 20 sites of the mouth

tbleLesions (lookup table)
All 7 symptoms

tblExam
ExamID (autonumber)
OperatorID (initials)
DateofExam

tblExamSite
ExamSiteID (autonumber)
ExamID (lookup from tblExam:ExamID)
SiteID (Lookup from tblSites)

tblSymptoms
ExamSiteSymptomID (autonumber)
ExamSiteID (lookup from tblExamSite:ExamSiteID)
Symptoms (lookup from tblLesions)

My problem is how to produce a form that will allow entry of
multiple
symptoms for an individual site without becoming massive. I have
tried
setting up a form containing the exam details with 2 subforms for
the
site
and symptoms.

Any ideas how to simplify the data entry?

"Ed Warren" wrote:


Each patient has one and only one mouth
Each mouth can have many sites
Each site can have many symptoms

So we need a set of lookup tables:

A table to track people (400 + rows)
A table to identify sites (20 rows)
A table to identify symptoms (7 rows)

Patients can have many exams

Each Exam covers many sites

Each Site can have many symptoms

So we will need at least the following:

tblExam
ExamId PK
PatientID Fk (refers to the patient table above)
DateOfExam

1: many to:

tblExamSite
ExamSiteID Pk
ExamID fk (refers to the exam being recorded)
SiteID fk (refers to the lookup site table above)

1 to many to:

tblExamSiteSymptom

ExamSiteSymptomID PK
ExamSiteID fk
SymptomID fk (refers to lookup symptom table above)

Of course each table could include additional data properly
related
to
the
level of exam, notes, scores etc.

Hope this helps

Ed Warren.

"Rob McKerlie" wrote in
message
...
Hi Harold

The only problem I see is that each location in the mouth can
have
more
than
one symptom e.g. the floor of the mouth may be 'red' as well
as
show
'swelling'. So for each location there could in theory be the
need
to
store
7 pieces of data. For each patient that means 7 poeces of
data
multiplied
by
the 20 locations.

"Harold via AccessMonster.com" wrote:

It seems like your will need two tables. One for patient
info,
(name,
id
number, and such), and a table for the locations within the
mouth.
In
the
patient info table you can use the id number as a primary key
and
link
it
to
the location in the mouth table. On the form you could use a
list
box
with
the different options to populate the table.

What do you think?

Rob McKerlie wrote:
I should say that the patient will only be identified by a
code,
no
patient
related data will be recorded.