If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Any benefits of relating two un-directly related tables & queries?
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)] |
#2
|
|||
|
|||
Any benefits of relating two un-directly related tables & queries?
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 he 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 "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)] |
#3
|
|||
|
|||
Any benefits of relating two un-directly related tables & queries?
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" wrote in message ... 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)] |
#4
|
|||
|
|||
Any benefits of relating two un-directly related tables & queries?
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)] |
#5
|
|||
|
|||
Any benefits of relating two un-directly related tables & queries?
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" wrote in message ... 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)] |
Thread Tools | |
Display Modes | |
|
|