showing all values in query

[dashingirish]

I think this is easy, but I just can't figure it out:



Here's a simplified version of my tables:

TBLMain
ID
HousingStatus=Valuelist from TBLStatus
FoodStatus=valuelist from TBLStatus

TBLStatus
ID
Status
StatusValue

I'm running a query that displays all the fields in TBLMain. I show only 1 field in the value list for HousingStatus and FoodStatus, but I'd like my query to show Status and StatusValue.

This is really easy, right?

[jzwp11]

Although Access has the capability of having lookups (list/combo boxes) at the table level it is generally not a good idea because it can cause issues as you describe. This site has more details on the problems.

I would recommend that you store only the key fields value of the status in your main table

TBLMain
ID
fkHousingStatusID foreign (fk) key to tblStatus
fkFoodStatusID foreign (fk) key to tblStatus



TBLStatus
-pkStatusID primary key, autonumber
-Status
-StatusValue


I would also recommend that you use more descriptive names for your "ID" fields otherwise things will get confusing when you try to create your relationships.

Speaking of relationships, if an record in your main table can have many items that each have a status then I believe your structure is wrong. Perhaps something like this


tblMain
-pkMainID primary key, autonumber
?

tblItems (housing, food, etc. each as a record in this table)
-pkItemID primary key, autonumber
-txtItemName

Now relate the applicable items and their status to the record from the main table. This assumes that an item can have only 1 status, but that a particular status can apply to many items.

tblMainItems
-pkMainItemID primary key, autonumber
-fkMainID foreign key to tblMain
-fkItemID foreign key to tblItem
-fkStatusID foreign key to tblStatus

[dashingirish]

Ooo...thank you for the quick reply!

My fields are more descriptive in the actual database, but thank you for the reminder.

Yes, you are correct that an item can have only one status per assessment and the status can apply to many items. I don't mean to be dense, but what does the ? under tblMain represent?

[jzwp11]

I don't mean to be dense, but what does the ? under tblMain represent?

The ? was meant to represent other fields in the table which I assume you have. BTW, what type of data is in your main table?

[June7]

Related thread https://www.accessforums.net/access/...ues-22075.html

[dashingirish]

Oh, of course.

Maybe I'd better give more details about this database.

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

Each assessment consists of a series of items 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, which is what we've been discussing.

That said, the main table contains the date and three fields with value lists plus a foreign key for tblClient. It also could contain about 40 fields relating to items (i.e., housing, food, etc) for which the user selects a priority level from a value list.

[June7]

Will every assessment include all categories?

Consider:

tblClients
ClientID (pk)

tblAssessments
AssessmentID (pk)
ClientID (fk)
AssessmentDate
Assessor

tblAssessmentDetails
AssessmentID (fk)
Category
Priority
Status

tblStatus (if you want one)
StatusValue (pk)
Status
Group

[jzwp11]

It also could contain about 40 fields relating to items (i.e., housing, food, etc) for which the user selects a priority level from a value list.

Having 40 fields is a sign that your database structure is not normalized.

And looking at your other post that June7 pointed out there are other things you need to work

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

Regarding the above, I would store the text in one field and the value in another. If you join via the key field you can access the value; there would be no need to store both the text and the value in your assessment table.

tblStatus
-pkStatusID primary key, autonumber
-txtStatus
-longStatus (your value 1, 2,3,..)


Since a client can have many assessments

tblClient
-pkClientID primary key, autonumber
-txtFName
-txtLName

tblClientAssessments
-pkClientAssessID primary key, autonumber
-fkClientID foreign key to tblClients
-dteAssess (date of assessment)

An assessment has many items that need to be assessed and an item can apply to many assessments

tblAssessmentItems
-pkAssessItemID primary key, autonumber
-fkClientAssessID foreign key to tblClientAssessments
-fkItemID foreign key to tblItems (that holds the assessment items)
-fkStatusID foreign key to tblStatus

tblItems (housing, food, etc. each as a record in this table)
-pkItemID primary key, autonumber
-txtItemName

[dashingirish]

Every assessment should include all categories. There are around 40 categories, with priority and status selected for each. Additionally, the client's children are included in the assessment, so I thought I'd make a separate table for children so that I can link multiple childIDs to single assessmentID - if that makes sense - mainly because there can be any number of children and the number of kids may change from assessment to assessment.

I'm feeling like I'm in over my head! Yikes.

[jzwp11]

, the client's children are included in the assessment, so I thought I'd make a separate table for children so that I can link multiple childIDs to single assessmentID

I would generalize tblClient to tblPeople. All people (adults, children etc.) should be in tblPeople each as a record.

How are the children in an assessment? Children are related to parents, I assume a parent is taking the assessment.

[June7]

You are assessing families? Each family can have multiple assessments? Each assessment might not involve the same family members? What is this db about?

[dashingirish]

Case manager conducts the assessment for the client. The client may or may not have children (depends on agency program). The children's data is not separate from the client's. For example, clientA may have two children (Ch1 and Ch2) at his initial assessment. The data collected for each child during that assessment consists of child's name and several items such as child's health, behavior in school, contact with non-custodial parent, etc. Like all other items, these are give both a priority and a status. But the child's data is specific to the assessment. On a follow-up assessment, Ch1 may be emancipated and therefore no longer part of the assessment, but Ch2 is still part of it. From my perspective, each could be considered an area of items, like Basic Needs, on the use gets to name the area. That way users can add or subtract children for each assessment.

[June7]

I did a search and could find only one source for social work database. You might explore it http://www.socialworksoftware.com/

Maybe another subtable for child info and assessment factors. It would have a fk to the assessment ID. Decide whether to store a ChildID from a Children table or repeat specifics (name, birthdate).

[dashingirish]

Thank you both so much for your help. I appreciate your time and expertise.

I'll take a look at that DB for social work and also see how I fare with the suggestions you've made.

I hope you have a lovely weekend.