Too many fields?

[NoviceIan]

Hi,

We're currently planning a new database which will monitor assessments that
our staff carryout. During each assessment our staff can make referrals to 5
different agencies amounting to 65 different referrals.

We're trying to figure out how this will work. So far the best idea we have
is to have 7 tables:

Customer - General Custmomer Details
Assessment - Date, Assessor's Name etc

Then 5 tables grouping related referrals, with Yes/No fields. These are:

Organisation Referrals - All referrals to other departments within our
organisation
GP Referrals - All GP referrals
Local Authority Referrals - All LA referrals
Voluntary Referrals - All referrals to voluntary organisations
Other Referrals - All other referrals

This is the best idea we currently have only I'm not sure how feasible it
is. My concern is how would we display them all? Would it be possible to
use sub forms on the main assessment form or is this not a good idea?

Not all customers have referrals to all the different organisations, so
again we were thinking that the referrals themselves could be hidden and
appear if we needed them? Does anybody have any advice for us as we are a
little confused??

Many thanks

Ian

 

[Jeff Boyce]

Ian

I'd suggest you step away from the keyboard and spend a bit more time
normalizing your data structure. As soon as you start adding "repeating
fields" (Referral to A, Referral to B, Referral to C...), you've created ...
a spreadsheet (and a headache!).

Access is a relational database, not a spreadsheet, and you won't get the
benefit (or ease) of Access' features/functions if you don't design your
data structure accordingly.

Based on the description you provided, it sounds like you have Customers,
Assessments and you have (potential) "referred to"
departments/organizations/... I'll assume that the referral portion is an
"outcome" of an Assessment.

Can a Customer have more than one Assessment?

Can an Assessment have more than one Referral? (or, as you pointed out,
none)

What are the basic entities and relationships of your underlying data? You
need this in place before you begin on the forms & reports.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[NoviceIan]

The entities are:

I understand what your saying I did not make myself very clear. Customers
should only have one assessment however each assessment can result in
anything from no referrals to 65 referrals.

What I'm trying to discover is how should I accomadate these referrals in a
database structure? I've thought a bit more on what I initially typed and
each referral will need to be have a value list as they go thru stages, for
example Audit, Evaluated etc etc.

I have normalised my data and all 65 referrals must be present as individual
options. My idea was to group them under loose agency headings such Local
Authority etc and have 5 seperate tables holding them.

Customer May Only Have One Assessment
Assessments Can Have Many Referrals

Is that any clearer? Sorry about before I never know how best to describe
the problem.

Ian

 

[Sprinks]

NoviceIan,

If I may jump in and add to Jeff's excellent advice, there is a natural
one-to-many relationship between Assessments and Referrals. This implies a
Main form based on Assessments with a continuous subform based on Referrals.

Something like:

Assessments
------------------------------------------------------
AssessmentID AutoNumber (PK)
CustomerID Number (FK to Customers)
AssessmentDate Date/Time
AssessorID Number (FK to Assessors)

Referrals
------------------------------------------------------
ReferalID AutoNumber (PK)
AssessmentID Number (FK to Assessments)
AgencyID Number (FK to Agencies)
ReferralDate Date/Time
Status Number (FK to Status--Audit, Evaluated, Etc.)

Use combo boxes for all foreign key fields. To make selecting the Agency
easier, by all means group them by subcategories:

Agencies
---------------------
AgencyID AutoNumber (PK)
AgencyGroupID Number (FK to AgencyGroups)
Agency Text

Then on the subform, include an Unbound combo box for the AgencyGroup, which
will then filter the RowSource of the 2nd (for AgencyID). Search the forum
for "Cascading Combo Boxes" for how to do this.

Also, I suggest you understand normalization VERY well before creating your
database. It's by far the most important topic. Doing it well makes the
application easy. The following sites have useful information:

ACC: Database Normalization Basics
http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Support WebCast: Database Normalization Basic
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc060600/wcblurb060600.asp

Database Normalization:
http://burks.bton.ac.uk/burks/foldoc/35/28.htm

5 Rules of Database Normalization:
http://www.datamodel.org/NormalizationRules.html

"Understanding Relational Database Design" Document Available in Download
Center:
http://support.microsoft.com/?id=283698
http://support.microsoft.com/?id=164172

ACC2000: "Understanding Relational Database Design"
http://support.microsoft.com/?id=234208

Fundamentals of Relational Database Design:
http://support.microsoft.com/?id=129519

Database Deisgn Principles:
http://msdn.microsoft.com/library/en-us/dndbdes/html/ch04DDP.asp

Sprinks

 

[Jeff Boyce]

Ian

Even though you (currently) have up to 65 referrals, tomorrow could have 80
(or 10). If you try to build one field per referral, every time something
changes, you'll need to modify your table structure, revise your queries,
update your forms & reports, and revisit all your code that refers to the
fields.

What I was suggesting is that you build a "narrow-but-deep" table that holds
only one ReferredToID and the AssessmentID. This gives you a "list" of all
the referrals made on an assessment, whether zero or 2 or 65 (or 200).

I'm not sure you and I use the same definition for "normalized"...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[NoviceIan]

I dont think we are on the same page since I am only a novice and you are
clearly an expert. I like the sounds of what your saying but unfortunately
I'm not sure what you mean.

I think your saying that I build a referal table and add the different
referrals as records within that table. then if any more referals are needed
they can be added easily or edited if needed. Is that what you mean?

I really appreciate your advice, I'm sorry 'm not more helpful.

Ian

 

[NoviceIan]

Assessment (Assessment #, Date, Assessor # etc)

1 - Many

Referral (Assessment #, Agency # - Composite Key, Status)

Many - 1

Agency (Agency #, Agency Group #, Agency Name,)

Many – 1

Agency Group (Agency Group #, Agency Group Name)


I think this is what you mean. Now taking a step back would it be possible
to use a lookup field for the Agency # on a Referral form to make it more
user friendly. I ask because it will form part of the primary key. Its not
essential I was just wondering if I should add a primary key or use the
composite key I suggested.

Also should I add a table for ‘Status’?

Ian

 

[Jeff Boyce]

Ian

The systems I design/build/support use "lookup" tables for any values that
are known and MUST be consistently entered. The problem with using "fill in
this field" is that users DO! And so creatively, too!

By using combo boxes (based on the lookup tables) in forms (to allow use of
the form's events for code), you will avoid unnecessary user creativity.

The only issue I see with composite primary keys is if you need to then
"migrate" that primary key as a foreign key in a "Child" table. There are
quasi-religious wars in this newsgroup about whether a totally arbitrary
(e.g., Autonumber) primary key field is OK, good, or evil. I tend to use
them to simplify connecting a "child" row back to its "parent" row.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[BruceM]

To what Jeff has written I will add that if within an Assessment there can
be several referrals that list the same agency, the composite key will not
be unique. Actually, you would be prohibited from adding a second referral
(within a particular Assessment) that lists the same agency as does another
referral in that Assessment.