Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Access 2007: Help me fix my forms/relationships please

Posted on 2011-03-21
Medium Priority
Last Modified: 2012-05-11
Hello, I have been working on a database with some of the experts here for some time, but I still can't quite get things to work.  My hope is that I can explain what I want to occur and then maybe someone can point me in the right direction.
When the form opens, the user will begin entering characteristics (frmPtCharacteristics) about an article on cancer with a unique [Call Number] field.  After this, the user will describe the groups in the article (for example the control and experimental groups which each have their own composition of characteristic diseases/ages/race).  Next they will give the treatment characteristics for the study attempting to cure the patient group of cancer (for example treatment gets drug A control gets drug B). And lastly they will enter the outcome information (for example after treatment Group A had 10% survival at 5 years and group B had 0% survival at 5 years, or Group A had an increased quality of life etc...).  The end result of this will hopefully be a series of filters/list boxes where users can select any pertinent fields from the database and export a landscape table to MS Word (for example saying you only wanted to look at Group A persons with Cancer type X in stage 4 and you want all the survival outcomes).   I have attached an image of the proposed order that users will navigate the forms and also a current image of the realtionshps in the database.  I have been trying very hard to get this to work, but before this time I have only made simple databases with two or three tables linked on a key with no sense of normalization.  
Basically, I'm having a lot of trouble getting the database's forms to work how I envisioned.  When a person begins using the database for the first time some of the fields will already be populated in tblPtCharacteristics (Author, Year, and Call Number).  The user will select from the combo box at the top of frmPtCharacteristics the article they want to review (ex. [Call Number] = 0003).  When a user moves from this first form (frmPtCharacteristics) to frmGroup I want to copy the active [call number] to tblGroup then they can enter however many groups they want all while keeping the same reference to the initial call number (i.e. I enter three groups each with call number 0003) and this assignment doesn't change unless the user uses the drop down box to search for another call number.  Each form has a subform where I want to display the groups for that call number (i.e. only show the three groups for ID 0003 and none for 0002) so that the user can be aware of what has been entered thus far.  Currently the forms are severely bothed and do not function like this.  I get frequent calls to 'Enter Parameter Values' and I feel like I have something basic not set up correctly.  Also, in some fields I do not think the forms are related correctly, or I have misspecified row source because I do not get a selection choice where there should be one (for example if I create several groups then I can not select them from frmAge frmRace etc... where I should be able to enter information about those groups)
If anyone can help with this I would appreciate it so, so much.  I'd give you a million points if I could.  I've been stuck on this for a while so any comment is helpful.  This database is to make some electronic work easier for non-profit researchers of cancer so your contributions would be for a good cause.

Thank you again,
Bevo S.

Question by:Bevos
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
LVL 19

Expert Comment

by:Richard Daneke
ID: 35184359
Certainly, understanding all this is quite a commitment.  I will comment that getting a lot of 'Enter Parameters' means you have fields in use that do not exist.   This could be a misspelling in your field names or it could be that you intended to use a text value and enclosed it in squar brackets [ ] instead of quotes " "
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 668 total points
ID: 35184637
This is more of a major database redesign project than a question.  But I think you are right about there being problems with many of the relationships.  I see some relationships (for example, between tblGroup and tblDiseaseDesc) that appear to be wrong.  The relationship is one-to-many from group to disease, and it seems to me that it should be the other way.  Some of the other relationships might need to be many-to-many, with a junction table.  Some tables look to me as if they should be lookup tables, in which case they don't really need to be linked in the Relationships diagram.

I recommend removing most of the tables from the Relationships diagram, except for the main tblPLCharacteristics, and then add them in one by one, being careful to set the correct relationship type for each.  
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 668 total points
ID: 35184667
As an example, if one group can study many diseases, but each disease is only studied by one group, then the relationship should be one-to-many, Group-Disease.

If one disease can be studied by many groups, but each group studies only one disease, then the relationship is one-to-many, Disease-Group.

If one disease can be studied by many groups, and one group can study many diseases, then the relationship is many-to-may, with a junction table linking Groups and Diseases.  See my Access Archon #178 for an example, using Scouts and Badges.  Here is a link to download it:


and here is a screen shot of the form:
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 332 total points
ID: 35185311

I see that you have been posting may questions about the "design" of this database here lately.

This site is not really the best vehicle to post broad, design questions about a database that is this far along in it's development.
As any proposals we might make would be based on fairly basic principles, and may not take into consideration all of your unique parameters and/or requirements.
(For ex: because this is Cancer related, you must have iron-clad systems to validate the data, and provide rock solid security)

For example, look at all you had to type just to present your question here...
...it is not really clear what the one "Answer" would be...

As with your "am I normal yet" question, (http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26891785.html) ...a majority of these issues appear to be rooted in your table design and relationships, and as such, should be fixed there, ...not in your interface (forms)

Remember my comment:
   "Your tables should be properly normalized and related *Before* you ever create your first form"

I suggest you partner with and experienced Access developer to help you evaluate this DB from top to bottom, to ensure it meets all of your requirements and parameters.


LVL 26

Assisted Solution

Nick67 earned 1000 total points
ID: 35185665
I have to agree with everybody above.  You have a problem even more basic that normalizing your data.  You need to firmly visualize it.  That spider web of relationships tells me that you don't firmly have a clear picture of how the data is going to be related.

Get out a VERY large piece of paper, like off a roll of packing paper, and start drawing Venn Diagrams.
A big circle in the middle for articles. Add in that circle only the details that are unique to an article
An intersecting circle for diseases.  Does each study only look at one disease?
Circles for groups and their unique things
Circles for sub groups and their unique things
Circles for outcomes and their unique things
Circles for adverse outcomes and their unique things

Far too many things in your relationship diagram look they relate back to the main table.
Too many of the secondary tables look like they are joined together

A spider web usually means that the data just hasn't been visualized right.

I'm with JeffCoachman.  Throw away all the forms.  Get the data right.
Walk through some studies.
Write them into the circles you draw.
You're 'normal' when you only write the data down once somewhere on your big diagrams of circles.
And you've got your design right when you start re-using data you wrote down earlier.

Good Luck!
LVL 26

Assisted Solution

Nick67 earned 1000 total points
ID: 35186177
I see it now, thinking about it.
I think your data design is ALL wrong.
You have studies.
You have participants
Participants have a whack of charateristics.
The 'groups' are an unhelpful abstraction.

You need a ParticipantType table
Yadda Yadda....
For each possible thing that happened to a participant you need to define a participant type.
That starts getting big in hurry, but you can't help it
If you have 10 characteristics, each with two outcomes, you have 1024 possible ParticipantTypes

Then you have a table tblWhoParticipated
WhoParticipatedID as long 'primary key
StudyID as long 'foreign key to the study table
ParticipantTypeID as long 'a defined, unique patient outcome
NumParticipants as long ' how many of this type of participant came out of the study

Then for each study you have some associated participant types and how many of each participant type were in the study.
Your spiderweb of relationships goes away
tblStudies relates to tblWhoParticipated one to many
tblWhoParticipated relates one to many to tblParticipantTypes
tblParticipantTypes relates one-to-many to as many tables as it takes to describe each participant in every study uniquely.
None of those participant attribute tables relate to each other.
None of those participant attribute tables relate to tblStudies
None of those participant attribute tables relate to tblWhoParticipated.

Sorry, mang.
You've got to start over.
NOTHING is about groups
Groups are an aggregation.

"How many white men 50-60yrs with stage IV disease x treated with y lived 5+ years after treatment?"

You ultimately want to know about participants.  The above is the aggregation of an arbitrary group based on characteristics.
There is nothing unique about a group.  Ultimately it never even existed.  It was just a number of participants

You can then filter by any or all of the unique data in tblParticipantTypes to get the data you want.

LVL 26

Accepted Solution

Nick67 earned 1000 total points
ID: 35194916
Looking at your relationship chart, everything with Outcome in its name should NOT have a call number field or be related on such a field.
If you want to calll tblGroup what I've called ParticipantType, you could.

Start with that.  Get rid of all those Call Numbers and relationships based on them.
They are wrong.  Studies don't have outcomes, participants in them do.

Now, does each study only focus on one disease and one stage?
If so tblDiseaseDesc and be related to tblPtCharacteristics on DiseaseID ( which isn't in that table yet)
If not, then tblDiseaseDesc has to be related to tblGroup on DiseaseID ( which isn't in that table yet)

Next, all the various tbl...outcomes.'
Call Number has to go
OutcomeID has to go
GroupNumber has to go
Outcome has to go.

I think outcome.value is an evil known as multi-valued table entries, which keeps this from being made into an .mdb
It has to go.

Now tblGroup needs:

and each of those tables need to be related one-to-many to tblGroup on those columns

GroupNumber doesn't belong in the disease, age or race tables

Next, you need a new table

It needs
GroupNumber(assuming that that is an ID)
NumberInGroup(how many of the participants have the same groupnumber)

The 'N' and 'Per' fields in disease, age ,and race need to go
Those will be calculations, not fields.

tblStudyOutcomes is something I can't determine its purrpose.
It has multi-value entries --which are evil and must die.
What is in those?
Whether a study was good or bad?  Poorly designed or well conducted?
Or whether treatment regimes were good or bad?

if it related to participants, it relates on OutcomeID in tblGroup.
If it relates to the study, it relates on OutcomeID in tblPtCharacteristics.

Adverse event is another of those multivalued fields.
Fix that.

Save it as an mdb and post it.
Then we'll have a go at your forms


Author Comment

ID: 35225592
Wow! Thanks for all the wonderful responses everyone.  I haven't had Access to my computer due to a laptop problem that required me to get a replacement from my vendor.  I am going to go through all of your suggestions tomorrow.

Thanks so much for all the help and my apologies for not getting in touch with you all sooner,
Bevo S.

Author Comment

ID: 35272322
I am closing this question and reposting after making my corrections.  Thank everyone for your thoughtful responses and being patient as I try to learn how to address this correctly.

Bevo S.

Author Comment

ID: 35272323
I am closing this question and reposting after making my corrections.  Thank everyone for your thoughtful responses and being patient as I try to learn how to address this correctly.

Bevo S.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question