Setting Up DB with numeric values

[dashingirish]

I've worked with Access before, but I'm by no means great with it. I'm having trouble figuring out how to set up a the following db and hope you can help me.

The db is to collect assessments for clients. Each client could have several assessments taken over time.

Each assessment consists of a series of conditions for which the user must select 1. a priority and 2. a status. Both the priority and status values are fixed. Each status value has a numeric value, as well, but the user won't see that.

Right now, I have the following set up:

TBLClient
ID
Name
etc

TBLAssessment
ID


Date
HousingPriority
HousingStatus
EducationPriority
EducationStatus
FoodPriority
FoodStatus
Etc

TBLStatus
ID
Status
StatusValue

Priority values are a value list: Primary; Seconday; Not a Concern; Not Determined

Status values are a value list from TBLStatus: In Crisis, 1; Vulnerable, 2; Stable, 3; Safe, 4; Thriving, 5.

So for AssessmentID1, the user might choose:
HousingPriority=Primary
HousingStatus=Vulnerable, 2
EducationPriority=Primary
EducationStatus=Stable, 3
FoodPriority=Secondary
FoodStatus=Vulnerable, 2
etc.

I've set it up this way because I must run reporting on the numeric values in TBLStatus.

Here are my problems. First, I cannot seem to get TBLStatus to connect properly with TBLAssessment. I tried a junction table, but I must be setting it up incorrectly.

Second, TBLAssessment is going to be huge - there are 80 fields. I can certainly break it into smaller tables and link them to the assessment - is this the best thing to do?

I am not wedded to this design. I just can't think of another way to do it. Help?

[June7]

Limit of 255 fields in a table. 80 is not so bad if every record will have value in every field. The problem is that tblStatus would have to be joined to each of the Status fields. Are 5 status categories all there will ever be? An alternative is to not use table but to use a ValueList RowSource that mimics multiple columns from a table:
1;Crisis;2;Vulnerable;3;Stable;4;Safe;5;Thriving

Each number and descriptor pair will be a row in the RowSource.

Another alternative is to save the descriptors instead of the numbers.

And yet another is to restructure tbAssessments (shouldn't it have ClientID as foreign key?).

TBLAssessment
ID
Date
ClientID
Category (Housing, Education, Food)
Priority
Status

Can each client have more than one assessment?

[dashingirish]

Wow. Thanks for the quick reply.

Yes, a client can have many assessments.

I believe the number of status categories will stay at 5.

If I saved the descriptors instead of the numbers, how would I go about converting the descriptors to numbers when I run reports? In a report, for each assessment I'd group like categories (for example, food and housing would be under Basic Needs) and average their status numbers. For each client, I'd compare the averaged numbers for each grouping. So say client A had an assessment on 2/14/2011 and another on 6/15/2011, and the average for Basic Needs on 2/14/2011 was 1.60 and the average for Basic Needs on 6/15/2011 was 1.00. The Basic Needs change for Client A is 0.60. That's the kind of stuff they need to see in the reporting.

[June7]

If you don't want to use tblStatus, regardless of whether you save number or descriptor, will need IIf or Switch or Choose function to convert one to the other. This calculation can be done in a query or textbox.

If you save the number then show descriptor with:
Choose([status], "Crisis", "Vulnerable", "Stable", "Safe", "Thriving)

Here is a reference for functions http://msdn.microsoft.com/en-us/libr...(v=vs.80).aspx

If you want to use table then save the number. The StatusValue field should be PK (the ID Autonumber field is not needed). You might want another field for Group (Basic, Savings, Household, etc).

[June7]

Related thread https://www.accessforums.net/queries...ery-22078.html