Any benefits of relating two un-directly related tables & queries?

[Axess08]

I would say that I am an intermediate to an advanced user of Access. I have
to admit that I don't completely understand the actual methods that the
software uses to relate tables and how they effect any saved queries. As a
result, I have now resorted to using programming to get queries done because
the query wizard is far too simple for research needs. My question relates to
the following:

I have been working on what will end up becoming a giant database that will
contain patient information for research. The idea of creating this database
was to help with data analysis ranging from very simple to complex. So far I
have the following tables:

(One to one relationships)
Patient Information (Main form - it includes pt id#, medical record #, name
of patient, etc.)
Contact Information
Birth History
Diagnosis
Outcomes
Neuromotor Exam
Hypothermia Entry
Hypothermia Course
Hospital Course
EEG Seizure Character
EEG Seizure Onset
Seizure Reporting and Response

(One to many relationships)
Research Studies Table
Lab Studies - Biopsy
Lab Studies - EEG
Lab Studies - Genetics
Lab Studies - CT
Lab Studies - MRI
MRI - T1 Image
MRI - T2 Image
MRI - MRS Image
MRI - DWI_ADC Image
EEG BAckground
aEEG Background

In the end I am going to create a bunch of queries for the research and I
was really wanting to know if there is any benefit to relating tables that
are further down the line in the chain of relationships (i.e. unrelated
tables) to one another to make the queries easier? Or would this cause some
problems in the long run? Should I just discard this idea and keep it simple?

[I haven't yet created the queries, but I already know that base on what I
want to know, that I will be doing it in code, because the filtering option
is far too limited.
(i.e. How many pt had an MRI, a CT, EEG, and had a seizure, and had a
specific type of seizure, and were how many days old? - something like that)]

 

[Klatuu]

Well to start with, I question your table design.
Are you saying that one patient will ever only have one:

Diagnosis
Outcomes
Neuromotor Exam
Hypothermia Entry
Hypothermia Course
Hospital Course
EEG Seizure Character
EEG Seizure Onset
Seizure Reporting and Response

?
It would seem to me that a patient could have multiple diagonises, etc, but
then you know your data requirements better than I.

One of the things creating relationships between tables does is create some
behind the scenes indexing for you that will enhance performance.

I am not sure what you mean by relating unrelated tables. Either they are
or they are not. You would have to describe that situation before I could
understand what you are really asking.

It is not uncommon to hand write queries using SQL either in the Query
builder (just switch to sql view) or in VBA, but nothing described here:

How many pt had an MRI, a CT, EEG, and had a seizure, and had a

specific type of seizure, and were how many days old?

Is beyond the ability of the graphical query builder if you know how to use
it.

--
Dave Hargis, Microsoft Access MVP

I would say that I am an intermediate to an advanced user of Access. I have
to admit that I don't completely understand the actual methods that the
software uses to relate tables and how they effect any saved queries. As a
result, I have now resorted to using programming to get queries done because
the query wizard is far too simple for research needs. My question relates to
the following:

I have been working on what will end up becoming a giant database that will
contain patient information for research. The idea of creating this database
was to help with data analysis ranging from very simple to complex. So far I
have the following tables:

(One to one relationships)
Patient Information (Main form - it includes pt id#, medical record #, name
of patient, etc.)
Contact Information
Birth History
Diagnosis
Outcomes
Neuromotor Exam
Hypothermia Entry
Hypothermia Course
Hospital Course
EEG Seizure Character
EEG Seizure Onset
Seizure Reporting and Response

(One to many relationships)
Research Studies Table
Lab Studies - Biopsy
Lab Studies - EEG
Lab Studies - Genetics
Lab Studies - CT
Lab Studies - MRI
MRI - T1 Image
MRI - T2 Image
MRI - MRS Image
MRI - DWI_ADC Image
EEG BAckground
aEEG Background

In the end I am going to create a bunch of queries for the research and I
was really wanting to know if there is any benefit to relating tables that
are further down the line in the chain of relationships (i.e. unrelated
tables) to one another to make the queries easier? Or would this cause some
problems in the long run? Should I just discard this idea and keep it simple?

[I haven't yet created the queries, but I already know that base on what I
want to know, that I will be doing it in code, because the filtering option
is far too limited.
(i.e. How many pt had an MRI, a CT, EEG, and had a seizure, and had a
specific type of seizure, and were how many days old? - something like that)]

 

[Jeff Boyce]

To add to Dave's comments, I'm wondering what kind of data is being stored
in your [Research Studies] table. From your description, it looks like you
would have to add a new column if you added a new test/lab.

If this is an accurate description, then reconsider your data design. If
you were limited to using a spreadsheet, you'd probably add a new column for
each new test or lab. But in Access (a relational database, not a
spreadsheet on steroids), "adding a new field" will require maintenance on
the table(s), on your form(s), on your query(s), on your report(s), on your
code, ...?!

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Axess08]

Well if I link two tables to one another in a 1-1 relationship (and they are
also linked to the "main" table) would that mess up my queries later down the
line (i.e. does it make my queries more restrictive)?

I have linked things like this through Patient ID (For example):

Patient Information (1)--->(1) Contact Information
Patient Information (1)--->(1) Birth History
Patient Information (1)--->(1) Diagnosis
Patient Information (1)--->(Many) Lab Studies - Biopsy
Patient Information (1)--->(Many) Lab Studies - EEG
Patient Information (1)--->(Many) Lab Studies - Genetics
Patient Information (1)--->(Many) Lab Studies - CT
Patient Information (1)--->(Many) Lab Studies - MRI

And I am wondering if I would also benefit or be hindered from doing this
(only the ones viewed here in 1-1 connection):

Patient Information (1)--->(1) Contact Information (1)<--->(1) Birth History
(1)<--->(1) Diagnosis
Patient Information (1)--->(1) Birth History
Patient Information (1)--->(1) Diagnosis

To Jeff Boyce: For this particular study, there are several checkboxes for
specific types of diagnoses. As the patients can only be diagnosed once for
this study (only first visit) this seems to work well especially for what the
doctors want (which in the end is really to be able to count the number of
patients with 'type a' diagnosis or 'type b' diagnosis for example).

The Research Studies table has a listing of the various studies that the
patients are involved in. 1 patient can be involved in many research studies.
For each of those research studies they have specific data that is relevant
(so they are in several tables) and there is of course data that is relevant
to all of the studies. I have linked them through switchboards (of sorts) to
try to keep things "user friendly".

To Klaatu: As far as the unrelated tables thing goes, it is simply that the
data fields are unrelated with exception to "patient id". However for
queries, I was under the impression that the further downfield a table is and
is not directly linked to any other table besides the "main" table, that
running queries becomes more difficult because certain tables are not linked.
Since I do not know what kinds of queries the doctors would want in the
future, I was trying to figure out ahead of time a simple way to link the
tables to make the querying simpler. I know they will want to know the age
and birthdays of patients (a month before) (to send a birthday card), quality
control information, and of course actual research specific data. Problem is
(besides what I have linked so far) I am not sure what they consider relevant
to one another. In the end I suspect it will all come down to programming
because I know that the graphical query builder basically cannot handle more
than 2 filters.

I hope I clarified things a little bit.

I would say that I am an intermediate to an advanced user of Access. I have
to admit that I don't completely understand the actual methods that the
software uses to relate tables and how they effect any saved queries. As a
result, I have now resorted to using programming to get queries done because
the query wizard is far too simple for research needs. My question relates to
the following:

I have been working on what will end up becoming a giant database that will
contain patient information for research. The idea of creating this database
was to help with data analysis ranging from very simple to complex. So far I
have the following tables:

(One to one relationships)
Patient Information (Main form - it includes pt id#, medical record #, name
of patient, etc.)
Contact Information
Birth History
Diagnosis
Outcomes
Neuromotor Exam
Hypothermia Entry
Hypothermia Course
Hospital Course
EEG Seizure Character
EEG Seizure Onset
Seizure Reporting and Response

(One to many relationships)
Research Studies Table
Lab Studies - Biopsy
Lab Studies - EEG
Lab Studies - Genetics
Lab Studies - CT
Lab Studies - MRI
MRI - T1 Image
MRI - T2 Image
MRI - MRS Image
MRI - DWI_ADC Image
EEG BAckground
aEEG Background

In the end I am going to create a bunch of queries for the research and I
was really wanting to know if there is any benefit to relating tables that
are further down the line in the chain of relationships (i.e. unrelated
tables) to one another to make the queries easier? Or would this cause some
problems in the long run? Should I just discard this idea and keep it simple?

[I haven't yet created the queries, but I already know that base on what I
want to know, that I will be doing it in code, because the filtering option
is far too limited.
(i.e. How many pt had an MRI, a CT, EEG, and had a seizure, and had a
specific type of seizure, and were how many days old? - something like that)]

 

[Jeff Boyce]

I'm having trouble visualizing the underlying data...

In Access, it isn't necessary (or desireable) to try to make your table
structures match your form designs. Tables store data (and hopefully in a
well-normalized structure, as that is what Access works with best), while
forms display it.

I don't understand why you are separating "Patient Information" from
"Contact Information". I am not clear on how a patient could have only ONE
"Diagnosis".

It appears you're describing a one-to-many relationship between patients and
Biopsy-type studies.

It is not a good use of a relational database to design tables with data
embedded in the table names ... and lacking further description, that's what
[Lab Studies - Biopsy], [Lab Studies - EEG], ... appear to be -- tables used
to segregate types of studies. This is how you'd handle it with
spreadsheets, but not with a relational database.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Well if I link two tables to one another in a 1-1 relationship (and they
are
also linked to the "main" table) would that mess up my queries later down
the
line (i.e. does it make my queries more restrictive)?

I have linked things like this through Patient ID (For example):

Patient Information (1)--->(1) Contact Information
Patient Information (1)--->(1) Birth History
Patient Information (1)--->(1) Diagnosis
Patient Information (1)--->(Many) Lab Studies - Biopsy
Patient Information (1)--->(Many) Lab Studies - EEG
Patient Information (1)--->(Many) Lab Studies - Genetics
Patient Information (1)--->(Many) Lab Studies - CT
Patient Information (1)--->(Many) Lab Studies - MRI

And I am wondering if I would also benefit or be hindered from doing this
(only the ones viewed here in 1-1 connection):

Patient Information (1)--->(1) Contact Information (1)<--->(1) Birth
History
(1)<--->(1) Diagnosis
Patient Information (1)--->(1) Birth History
Patient Information (1)--->(1) Diagnosis

To Jeff Boyce: For this particular study, there are several checkboxes for
specific types of diagnoses. As the patients can only be diagnosed once
for
this study (only first visit) this seems to work well especially for what
the
doctors want (which in the end is really to be able to count the number of
patients with 'type a' diagnosis or 'type b' diagnosis for example).

The Research Studies table has a listing of the various studies that the
patients are involved in. 1 patient can be involved in many research
studies.
For each of those research studies they have specific data that is
relevant
(so they are in several tables) and there is of course data that is
relevant
to all of the studies. I have linked them through switchboards (of sorts)
to
try to keep things "user friendly".

To Klaatu: As far as the unrelated tables thing goes, it is simply that
the
data fields are unrelated with exception to "patient id". However for
queries, I was under the impression that the further downfield a table is
and
is not directly linked to any other table besides the "main" table, that
running queries becomes more difficult because certain tables are not
linked.
Since I do not know what kinds of queries the doctors would want in the
future, I was trying to figure out ahead of time a simple way to link the
tables to make the querying simpler. I know they will want to know the age
and birthdays of patients (a month before) (to send a birthday card),
quality
control information, and of course actual research specific data. Problem
is
(besides what I have linked so far) I am not sure what they consider
relevant
to one another. In the end I suspect it will all come down to programming
because I know that the graphical query builder basically cannot handle
more
than 2 filters.

I hope I clarified things a little bit.

I would say that I am an intermediate to an advanced user of Access. I
have
to admit that I don't completely understand the actual methods that the
software uses to relate tables and how they effect any saved queries. As
a
result, I have now resorted to using programming to get queries done
because
the query wizard is far too simple for research needs. My question
relates to
the following:

I have been working on what will end up becoming a giant database that
will
contain patient information for research. The idea of creating this
database
was to help with data analysis ranging from very simple to complex. So
far I
have the following tables:

(One to one relationships)
Patient Information (Main form - it includes pt id#, medical record #,
name
of patient, etc.)
Contact Information
Birth History
Diagnosis
Outcomes
Neuromotor Exam
Hypothermia Entry
Hypothermia Course
Hospital Course
EEG Seizure Character
EEG Seizure Onset
Seizure Reporting and Response

(One to many relationships)
Research Studies Table
Lab Studies - Biopsy
Lab Studies - EEG
Lab Studies - Genetics
Lab Studies - CT
Lab Studies - MRI
MRI - T1 Image
MRI - T2 Image
MRI - MRS Image
MRI - DWI_ADC Image
EEG BAckground
aEEG Background

In the end I am going to create a bunch of queries for the research and I
was really wanting to know if there is any benefit to relating tables
that
are further down the line in the chain of relationships (i.e. unrelated
tables) to one another to make the queries easier? Or would this cause
some
problems in the long run? Should I just discard this idea and keep it
simple?

[I haven't yet created the queries, but I already know that base on what
I
want to know, that I will be doing it in code, because the filtering
option
is far too limited.
(i.e. How many pt had an MRI, a CT, EEG, and had a seizure, and had a
specific type of seizure, and were how many days old? - something like
that)]