Bevos
asked on
Access 2007: "Am I normal yet?" a question about if my table relationships are normal and correct
Hello, I have been working on an Access Database with some of the folks in this site and I wanted to get feedback about my table structure to see if it is normal. I also wanted to see if, as laid out, my table design could make my forms/output look how I want.
Here is the basic gist of the form: I want to describe academic articles on cancer each with a unique [Call Number]. Four tables are related to call number unique key in a one-to-many relationship (PDF_Files, tblStudyGrading, tblGroup and tblStudyOutcomes). Because they are the most confusing, I would like the most help regarding tblGroup and its relationships as well as tblStudyOutcomes.
So we have a bunch of articles with some information about the article in tblPtCharacteristics. I want to describe the article further in tblGroup (which has a unique key GroupNumber, which relates to subsequent tables) by creating groups of patients who have data separated out in the article (for example a Control group and an Experimental group). After a user has described groups [GroupNumber] describing an article [Call Number] he or she can then describe the groups by entering characteristics about them in the tables tblAgeDesc, tblRaceDesc, and many outcomes (these are linked on tblGroup.[GroupNumber] AND tblstudyoutcomes.[OutcomeI
I want the database to work in this way. A user goes through the many forms necessary for a specific article (unique [call number]) and each will modify only those groups or outcomes related to the call number in question. Are the forms set up in a normal way that allows for this type of relationship? The way I am wanting the form controls to function is that a user selects a 'call number' from a combo box. After that selection he or she enters the remaining information on frmPtCharacteristics and then moves on to frmGroups and its associated forms (frmagedesc frmrace frmdisease) all the while, a filter is in place so that he or she only enters information pertaining to the original [call number] as linked to Group etc...
Of particular concern to me is the tblStudyOutcomes and the tables linking to both [OutcomeID] and [GroupNumber]. have I made these relationships appropriately? For better understanding an outcome could be something like 'survival at 10 years' and values for each group such as 'survival at 10 years for control' & 'survival at 10 years for experimental'.
Any help would be so welcome as I find this a confusing and difficult topic.
EE-ABS-Example.accdb
Here is the basic gist of the form: I want to describe academic articles on cancer each with a unique [Call Number]. Four tables are related to call number unique key in a one-to-many relationship (PDF_Files, tblStudyGrading, tblGroup and tblStudyOutcomes). Because they are the most confusing, I would like the most help regarding tblGroup and its relationships as well as tblStudyOutcomes.
So we have a bunch of articles with some information about the article in tblPtCharacteristics. I want to describe the article further in tblGroup (which has a unique key GroupNumber, which relates to subsequent tables) by creating groups of patients who have data separated out in the article (for example a Control group and an Experimental group). After a user has described groups [GroupNumber] describing an article [Call Number] he or she can then describe the groups by entering characteristics about them in the tables tblAgeDesc, tblRaceDesc, and many outcomes (these are linked on tblGroup.[GroupNumber] AND tblstudyoutcomes.[OutcomeI
I want the database to work in this way. A user goes through the many forms necessary for a specific article (unique [call number]) and each will modify only those groups or outcomes related to the call number in question. Are the forms set up in a normal way that allows for this type of relationship? The way I am wanting the form controls to function is that a user selects a 'call number' from a combo box. After that selection he or she enters the remaining information on frmPtCharacteristics and then moves on to frmGroups and its associated forms (frmagedesc frmrace frmdisease) all the while, a filter is in place so that he or she only enters information pertaining to the original [call number] as linked to Group etc...
Of particular concern to me is the tblStudyOutcomes and the tables linking to both [OutcomeID] and [GroupNumber]. have I made these relationships appropriately? For better understanding an outcome could be something like 'survival at 10 years' and values for each group such as 'survival at 10 years for control' & 'survival at 10 years for experimental'.
Any help would be so welcome as I find this a confusing and difficult topic.
EE-ABS-Example.accdb
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much 8080 Driver! I'm going to make these changes today and then post a new screen shot of the relationship status. After I do, could you let me know if I've gotten everything down correctly?
Thanks,
Bevo S.
Thanks,
Bevo S.
Bevo,
Gladly.
By the way, the handle is 8080 Diver (not Driver) . . . the reference is to the fact that I prefer SCUBA diving in 80 degree water where there is 80 feet of visibility (or greater). ;-)
Gladly.
By the way, the handle is 8080 Diver (not Driver) . . . the reference is to the fact that I prefer SCUBA diving in 80 degree water where there is 80 feet of visibility (or greater). ;-)
hi 8080_diver,
puh, pretty hot, isn't it? I know people bathing in colder Jaccuzis .) btw, I would assume that you have such temperatures at snorkel depth... c--8)
puh, pretty hot, isn't it? I know people bathing in colder Jaccuzis .) btw, I would assume that you have such temperatures at snorkel depth... c--8)
ASKER
Haha... sorry I thought it was a thing about early intel processors.
Well, i did that, too . . . but "The worst day of SCUBA diving is better than the best day in the office!" ;-)
ASKER
Hi Diver and Stefan, sorry for the late reply but I have been a bit under the weather this last week. I made the changes we talked about and I was hoping you could give me one last confirmation that things are okay before I close out the question.
Thanks again for all your wonderful help. I feel like I am beginning to understand relationships in Access much more than before.
Bevo S.
EE-example-relationships.bmp
EE-ABS-Example.accdb
Thanks again for all your wonderful help. I feel like I am beginning to understand relationships in Access much more than before.
Bevo S.
EE-example-relationships.bmp
EE-ABS-Example.accdb
It's a little hard to absoultely discern which of the relationships are/should be 1 to many. For instance if there are multiple TTEOutcomes rows potentially associated with one PtCharacteristics row, you may want to check that relationship. Similarly, unless you are sticking with the comma delimited list in the Outcome column, I would suspect that the relationship between PTCharacteristics and PrePostOutcomes is, again, a 1 to many and not a 1 to 1. (I you are planning on sticking with the co0mma separated list, I would highly advizse you to reconsider that. ;-)
In fact, it appears that virtually every relationship from the PtCharacteristics table is being shown as a 1 to 1 . . . and, from my experience with hiospital systems, I just have to question that.
In fact, it appears that virtually every relationship from the PtCharacteristics table is being shown as a 1 to 1 . . . and, from my experience with hiospital systems, I just have to question that.
ASKER
Thanks so much for the reply 8080 Diver, I thought these were set to 1 to many relationships! I need to go back and do the 'enforce referential integrity' to make this work. (Note, this doesn't seem to work and now I'm stuck on how to make the relationships one to many)
On the topic of PrimaryOutcome and SecondaryOutcome how do you suggest I change this? On the subsequent forms, the user is selecting the outcome from a combo box (single selection) but on this form they were just giving a comma separated list of all the outcomes in the study (this might go in a different table than those specific outcomes giving a brief summary of the study data).
On the topic of PrimaryOutcome and SecondaryOutcome how do you suggest I change this? On the subsequent forms, the user is selecting the outcome from a combo box (single selection) but on this form they were just giving a comma separated list of all the outcomes in the study (this might go in a different table than those specific outcomes giving a brief summary of the study data).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok 8080 Diver, I will try to make that change. Do you know why my relationships aren't one to many? I know that I stopped having Call Number be the primary key on PtCharacteristics, but I thought because it was a unique field (indexed, no duplicates) this would let me make 1 to many relationships as well?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Posted for remaining issues in another question
ASKER
I didn't think having the [Call Number] a meaningful field would be a problem as the key, but I will take your advice and restructure those tables to have an autokey. I will also remove the special characters (space, slash, parenthesis). Is underscore a special character as well? If so I will change those too.
I like that you moved the PDF Filename to the Pt Characteristics table, I think that makes sense. As for the author thing, I don't believe it will be important (it is mainly present so that when I export these as an excel table I can have a citation with the authors there). So I understand correctly Stefan, you are suggesting I take some of the descriptive items from tblPtCharacteristics and move them to another table (tblCharacteristics and then tblArticle for the [call number] [author] etc...)?
8080 Driver, thanks for the suggestion on auto ID field. I think that is a sensible suggestion and I will keep the current descriptive field names as you like. Haha, sorry about the tbl frm prefixes. I didn't use them in earlier projects but every database I see has stuff like that (I have no formal training in this kind of design) so I just started adopting it. For me at least, it can make things a bit easier looking at the database elements but it is a bit silly.
On to your comments about the outcomes tables. I am VERY interested in what you have to say here (I think it was hard to make these correctly related etc...) but I am not sure I understand what you mean. What do you mean outcometype flag? I'm not familiar with the term. Right now, primary outcomes and secondary outcomes are multi-select boxes where you will get a list of the outcomes (such as survival, adverse events, secondary cancers etc...). Each of the following tables will describe more fully the outcomes, but I think you've probably touched on something important and I'm not understanding. These following outcomes will describe tblStudyOutcomes, for example tblTTEOutcomes will have a row for each combination of group and outcome ([OutcomeID and GroupID] defined for a specific [call number]). So lets say in a record with call number of 012 I have the outcomes survival and time til tumor relapse (both TTE outcomes) and 3 groups Drug1 Drug2 Placebo I would have six rows in the TTEOutcomes table describing record 012. Does the layout, as we've suggested thus far, allow for this to work? At the end I basically want to be able to compare via query different [Call Numbers] characteristics and outcomes with one another.
Thanks so much,
Bevo S.