Populating multiple table from a single form

[Guest]

Hi

I am attempting to create a database to store seven bits of data relating to
20 items. To complicate this further all twenty items belong to one person
and the database will eventually hold data for 400 people.

Do I need to create a table for each of the twenty items (the seven bits of
data are the same for all 20 items)?

How would I go about creating a single entry form for each person?

Thanks in advance
Rob

 

[Harold via AccessMonster.com]

Hi,
How do the bits of data relate to the 20 items? It sounds like you need more
than one table. The forms should be made after the tables are set up. You may
need one table for the people.
Send more info.
Harold

 

[Guest]

Hi Harold

There are seven options (appearances) for each item (sites of mouth). The
seven appearances are the same for all 20 sites within the mouth.

options:
None; Ulcer; White; Red; Swelling; Pigmented; Other

Examples of sites:
Upper Lip; Lower Lip, Floor of mouth, Dorsum of tongue, etc.

On examination of patient data will be recorded relatting to the appearance
of each of the 20 sites within the mouth.

I imagine that the options would be simple yes/no.

Thanks again for you help.

Regards,
Rob

 

[Guest]

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

 

[Harold via AccessMonster.com]

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?

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]

 

[Guest]

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.

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?

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

 

[Ed Warren]

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.

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.

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?

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

 

[Guest]

Hi Ed

Many thanks to you & Harold for your time.

I will work through your suggestion, I am very much a novice with access. I
naively thought that I could have a single input form with a grid layout for
each exam, but I am not so sure now.

Once again thank you, I will let you know how I get on.

Rob

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.

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.

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

 

[Guest]

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?

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.

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.

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

 

[Ed Warren]

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

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?

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.

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.

:

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

 

[Guest]

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

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

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?

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.

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.

:

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

 

[Ed Warren]

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.

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

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

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?

:


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.

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.

:

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

 

[Guest]

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.

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.

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

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

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?

:


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.

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.

:

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

 

[Ed Warren]

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

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.

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.

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

:

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

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?

:


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.

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.

:

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

 

[Guest]

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.

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

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.

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.

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

:

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

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?

:


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.

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.

:

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.

 

[Ed Warren]

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

Ed Warren

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.

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

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.

:

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.

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

:

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

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?

:


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.

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.

:

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.