Any benefits of relating two un-directly related tables & ..

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" <Axess08.TakeThisOut@discussions.microsoft.com> wrote in message
news:5F85BA81-AC16-4D56-A28B-0EF2D649B238@microsoft.com...
>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)]
>
>
>

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.

"Axess08" wrote:

> 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)]
>
>
>

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

"Axess08" <Axess08.TakeThisOut@discussions.microsoft.com> wrote in message
news:1561C068-E5F2-49CC-91FF-E4E4EB4A5967@microsoft.com...
> 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.
>
> "Axess08" wrote:
>
>> 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)]
>>
>>
>>