Solved

Access 2007: Help me fix my forms/relationships please

Posted on 2011-03-21
10
896 Views
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.

EE-example-relationships.bmp
form-flow.bmp
EE-ABS-Example.accdb
0
Comment
Question by:Bevos
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 18

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 " "
0
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 167 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.  
0
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 167 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:

http://www.helenfeddema.com/Files/accarch178.zip

and here is a screen shot of the form:
Many-to-Many-Relationship-Form.jpg
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 83 total points
ID: 35185311
Bevos,

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.

;-)

JeffCoachman
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 250 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!
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 250 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
ParticipantDisease
ParticipantAge
ParticipantSex
ParticipantControlOrExperimental
ParticipantTreatment
ParticipantSurvival
ParticipantAdverseReaction
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.


0
 
LVL 26

Accepted Solution

by:
Nick67 earned 250 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:
DiseaseID
AgeID
RaceID
OutcomeID
TTE_ID
PPO_ID
REG_ID
PL_ID
QOL_ID
AVE_ID

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
tblGroupsInStudy

It needs
GroupInStudyID
PtCharID
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

Nick67
0
 

Author Comment

by:Bevos
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.
0
 

Author Comment

by:Bevos
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.
0
 

Author Comment

by:Bevos
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Creating and Managing Databases with phpMyAdmin in cPanel.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now