I've Lost Myself

[Darkwater]

Fingers crossed I'll get some direction to resolve an issue that I can't even begin to imagine how to search for.

My database is used to record detailed work done with clients who are receiving regular support. I'm designing it around a Common Assessment Support Framework that I have no choice but to follow, except for some minor points. There is the potential for approximately 40 different assessments for each client and therefore a lot of duplication in personal details.

So far this forum has been a great help and I have learned a lot from those who volunteer their time to those of us who need it from time to time.

Following the numerous examples of normalisation posts I have attempted to ensure there is no duplication of information across tables. I've come to a point however where I am stuck:

The end result of what I am trying to do is create an individual report called Needs Assessment Planning Tool - rptNAPT.

The report will be populated from the fields in qryNAPT that is made up from tblIA1 and tblIA2

tblIA1 consists of 17 x 'area of need' that is broken down in to 5 elements: 'Now - Y/N', 'Later - Y/N', 'User Level - Number', 'Worker Level - Number' and 'Disagreement - Memo'. These fields are entered by using frmIA1, and these fields are entered by using frmIA2.

So, now I have to enter data in to another form; frmNAPT that includes both theseand thesefields. I only need to show the area of need where Now is True in the form and the subsequent report; rptNAPT.

My thoughts about this are (please don't laugh) in the form frmNAPT the 'Area of Need' because in the table there are 17 different areas e.g. Accommodation (improvement), Homelessness, DomesticViolence etc will need to be unbound and in some way filtered to show only those area of need where fldNow is True? and fldUserLevel is NotNull or doesn't equal zero. I will need a minimum of five such fields to show the Areas of Need that the Client and Worker may elect to work on.

Sorry for the long post but I'd really appreciate some direction on this, even if it is only to point me in the direction of a link that can be helpful.

Regards
Doug

 

[CraigDolphin]

I'm having trouble following your question.

You mention tblIA1 as having 17 x 'area of need' that is broken down into 5 elements.

Do you mean that the Area Of Need field, in tblIA1, has 17 possible values? And that each record in that table has 5 other attibutes (fields) named Now, Later, User Level, Worker Level, and Disagreement?

If so, what are the fields in tblIA2 and how does that table factor into what you are wanting to report? Also, is there a reason why you are entering data into the same table from two different forms?

I assume you have a separate table listing the 17 values that might occur in the Area Of Need field, with its own autonumber Primary key, and that you're storing this key value in your Area Of Need field in tblIA1?

 

[Darkwater]

Hi CraigDolphin,

Thanks for the quick response, and sorry for the confusion. I'll try and answer your questions as best I can.

I'm having trouble following your question.

You mention tblIA1 as having 17 x 'area of need' that is broken down into 5 elements.

Do you mean that the Area Of Need field, in tblIA1, has 17 possible values? And that each record in that table has 5 other attibutes (fields) named Now, Later, User Level, Worker Level, and Disagreement?

tblIA1 contains 17 Area's of Need: e.g Accommodation (making it safer, or more suitable.) For that field the user can say he needs help with this area of need 'Now' or 'Later' and if he needs it 'Now' then he will indicate the 'Level' of urgency (1, 2 or 3), and so on for the other 16 Areas of Need. All of these Areas of need and the indicated fields need to be produced in a report that the Client will sign. The other two fields 'WorkerLevel' and Disagreement appear in a different report; frm/rptNAPT (Needs Assessment Planning Tool) that duplicates the Area of Need, Now, Later and User Level fields.


If so, what are the fields in tblIA2 and how does that table factor into what you are wanting to report? Also, is there a reason why you are entering data into the same table from two different forms?

On this point I've realised after reading your question that I could and probably should put the fields in tblIA2 in to tblIA1.

I assume you have a separate table listing the 17 values that might occur in the Area Of Need field, with its own autonumber Primary key, and that you're storing this key value in your Area Of Need field in tblIA1?

Yes to that.

I appreciate your assistance and can only apologise again if I have confused you further.

Regards
Doug

 

[CraigDolphin]

Ok, your table has 17 fields, each the name of an area of need? The values for each field are either now or later, and for 'now' results, you need to store the level of urgency?

If so, then I see a problem with your table structure.

Here's what I would suggest:

tblNeeds
-NeedID (Pk, autonumber)
-Area_Of_Need (text)

tblClients
-ClientID (Pk, autonumber)
-ClientName
etc

tblInterviews
-InterviewID (Pk, autonumber)
-InterviewDate
-InterviewTime
-WorkerID (FK, Long Int)
-ClientID (FK, Long Int)

tblWorkers
-WorkerID (Pk, autonumber)
-WorkerName
-WorkerLevel
etc

tblInterviewAnswers
-AnswerID (Pk, autonumber)
-InterviewID (FK, Long Int)
-NeedID (FK, Long Int)
-NeedNow (Yes/No)
-NeedPriority (Long Integer)
-Disagreement

This assumes that you are gathering the data from the client in something analogous to an interview situation. I'm not sure if the field disagreement really belongs in tblInterview Answers. If the disagreement is inteneded to relate to a specific area of need then it does. If it is more general it may belong in tblInterviews. If it is meant to be an indicator of trouble bwteen a client and a worker then you might need another table called tblDisagreements that would have fields for ClientID and Worker ID (FK's) along with its own autonumber PK field.

In relationships view, link the tables using the key fields indicated. (Pk to FK of the same name).

This structure should give you the flexibility to work on forms and reports to produce what you need. If you need help with those steps then post back.

 

[Darkwater]

Thanks again CraigDolphin,

What you advise makes great sense and I will work on it later. You are correct in your assumption that the disagreement field relates to a specific area of need.

Really appreciate your help on this, and can't help but be impressed by the speed in which you formulated the response.

I'll let you know how it goes.

Regards
Doug

 

[CraigDolphin]

Glad to help. Good luck with it. Normalisation is a concept that takes time to grow but it solves so many problems! I'm still learning myself