How do I get copied information saved within another table?

[jzwp11]

I would set up a table to hold the 18 card choices and another table that relates them to the client interview table (tblClientInterviewCards) we set up previously. Then you will need another table linked to tblClientInterviewCards to capture the specific action plan items related to each of the 6 cards. Regarding the action plan items, are these typically unique to the client? In other words, do you use the same action plan items across multiple clients?

I also had second thoughts on this:

Motivation tab


All questions have 2 drop downs that need completing

If an item has multiple items related to it then you have a one (motivation) to many items. You will have to explain more about this and what the 2 drop downs consist of in order to determine the best table design.

[REAPER_110]

ok, I will try and sort the other tables and then upload the latest file again for you to check. Would you be able to list what I should set up like you did before for me please, I understood that. Also, yes the action plan points are client specific so will be different for each client.

With regards to the motivation drop downs the other 2 drop downs in question are for the client and advisor to agree on what level of support is required.

Thank you so much for your help, its really helping me to look at things differently

[jzwp11]

Copying over what we had before:

tblClient
-pkClientID primary key, autonumber
-txtFName
-txtLName
other fields

Then we need a table to hold the interview questions. each question will be a record in this table.

tblQuestions
-pkQuestionID primary key, autonumber
-txtQuestion

Can a client undergo multiple interviews over time? If so, then we need a table to hold the interview details

tblClientInterviews
-pkClientInterviewID primary key, autonumber
-fkClientID foreign key to tblClients
-dteInterview (interview date)

Now we need to relate the questions asked during the interview and capture the corresponding response

tblClientInterviewQuestions
-pkClientIntQuesID primary key autonumber
-fkClientInterviewID foreign key to tblClientInterview
-fkQuestionID foreign key to tblQuestions
-txtResponse

Now for the card stuff:

tblCircumstances (holds your 18 choices so 18 records)
-pkCircumstanceID primary key, autonumber
-txtCircumstance

Now relate the applicable 6 circumstances to the client at the particular interview

tblClientInterviewCircumstances
-pkClientIntCircumID primary key, autonumber
-fkClientInterviewID foreign key to tblClientInterviews
-fkCircumstanceID foreign key to tblCircumstances

Now relate the action plan items applicable to each of the 6 circumstances assigned to the client assuming that each circumstance can have more than 1 action plan item

tblClientInterviewCircumstanceActions
-pkClIntCircumActionID primary key, autonumber
-fkClientIntCircumID foreign key to tblClientInterviewCircumstances
-txtActionPlanItem

Now you will want to track progress on each action item

tblClientInterviewCircumstanceActionProgress
-pkClIntCircumActProgID primary key, autonumber
-fkClIntCircumActionID foreign key to tblClientInterviewCircumstanceActions
-dteProgess (a date field)
-ProgressDescription (a description of what progress has been made)

With regards to the motivation drop downs the other 2 drop downs in question are for the client and advisor to agree on what level of support is required.

I'm not quite sure I follow, could you please give an example?

[REAPER_110]

Hi,

Please find attached the database so far. Please could you confirm that what I have done so far is correct as you have described??

Also, the motivation section has a list of set questions where the required responsed are selected from 2 drop down menus. The first drop down menu has the options "Agree, Doesn't Apply, Disagree" and the 2nd drop down box for each question has the numbers "1-6".

The client goes down the list of questions and for each one can then select "agree, doesn't apply or disagree" then uses the 2nd drop down for each question to select the level of affect the "question" has on them, for example 1,2,3,4,5 or 6.

Does that make it any easier to understand or am I confusing you as much as myself? lol.

Kind Regards,
Andrew.

[jzwp11]

The tables and relationships you set up based on my posts look good except for enforcing referential integrity, so I took care of that.

I noticed that you had some lookup fields at the table level. Although Access has this capability, it is generally best to not use them. This site has more details of the problems the table level lookups can cause. The lookups are best left for forms. I migrated the data you had in the value lists to respective tables (tblTitles and tblLanguage) and joined them back to tblClient. Having the data in tables will allow easier future additions/changes etc.

Since a person can have many contact methods (phone, fax, mobile phone, e-mail etc.) that is a one-to-many relationship so I separated that out as well into appropriate tables.

In the attached database, I only have the tables and relationships. I was getting several errors, so I thought it best to start fresh.

[REAPER_110]

Hi,

Just checked the table structure, its looking a lot better, more organised and a lot easier to change if required. What do I need to do next to get my database running correctly??

I don't know whether you have or not but if you look at my original database you might be able to see what I need to do next for this version. Everything within the forms of the original will need to be incorporated into this one.

Kind Regards,
Andrew

[jzwp11]

After looking at your forms, I redesigned my example database; it is attached.

The goals/achievements and experience still need to be structured. For the goals/achievements do the job goal stuff on the left side of the form correlate (i.e. is it related) to the achievements on the right side of the form?

[REAPER_110]

The goals/achievements and experience still need to be structured. For the goals/achievements do the job goal stuff on the left side of the form correlate (i.e. is it related) to the achievements on the right side of the form?

No, they are 3 job goals the client chooses and 3 achievements they have done. It is entered by the advisor as free text. The goals are not related to the achievements.

What is my next step?? The database looks much more structured, thank you again for your help.

Kind Regards,
Andrew.

[jzwp11]

Since a client can have many goals and I assume these goals are set forth during the interview, so you will need a table related to that. The same would be true for the achievements. I'll let you try to work out the table structure for these two additional tables.

I think the only think left is the experience aspect. It looks like you had 2 types Direct and Transferrable. Can a direct experience also be a transferrable experience. How do you define these types?

[REAPER_110]

OK,

Thought I would check with you before I set them up.

I think I would do it as below?

tblgoals
-pkClientGoalID
-fk......
-Goal

tblAchieve
-pkClientAchieID
-fk......
-Achievement

Is that anywhere near right?? I am trying to think of how you would have done it. Only thing I wasn't sure of was where you would link the tables to?? I would link via the fk correct? I was not sure where to link it to.

As for the direct and transferable skills, they could be duplicated I suppose although I was thinking of leaving this as free text so that the advisor could discuss these with the client.

Once this is set up, is there anyway I can use the form I originally set up and change the source for each thing or will I have to start my form design again?

Kind Regards,
Andrew.

ps would i just need goal or goal1, goal2, goal3?

[jzwp11]

In general your structure is OK; in terms of the foreign key field, I assume that these are things recorded during the interview so you would link back to the client-interview table. In terms of the goal number (goal 1 , goal 2 etc.), each goal entered will be a record. If you want to use the goal number to rank the goals then you would just add a sequence number field where you can assign the rank (1, 2, 3 etc.)

tblgoals
-pkClientGoalID
-fkClientInterviewID
-txtGoal
-GoalSeqNo


tblAchieve
-pkClientAchieID
-fkClientInterviewID
-Achievement

As for the direct and transferable skills, they could be duplicated I suppose although I was thinking of leaving this as free text so that the advisor could discuss these with the client.

Another option is to just list each experience item (as a record) and then have a field that has the type, but if an experience item can be of multiple types (direct, transferable etc.) that would be a one(experience item)-to-many(types) relationship which would require an additional table.

After the table structure is complete, then you would start working on forms. Your current forms will not work with the new structure; I have found that it is best to get rid of the old forms and start from scratch.

[REAPER_110]

Hi,

I have set up the last 2 tables as advised. Do you think there is anymore tables that we need to add before I start work on the actual forms?? I should be able to use a tabbed form still shouldn't I?? I will just need to link corresponding tables to the forms correct??

Thanks so much for all your help, Kind Regards,

Andrew.

[jzwp11]

How did you work the structure for the experience items?

Whether additional tables are needed is more up to you. If everything that you want to capture has a table then you are probably done with the tables and can move onto forms. With regard to forms, it is generally best to base a form on a single table. Additionally, when you have a one-to-many relationship, the main form is typically bound to the table on the one side of the relationship. You would use a subform that is based on the table that makes up the many side of the relationship. You can use a tabbed control and place each subform in its own tab as long as the data in the subform is on the many side of the relationship relative to the data in the main form. In some cases you may need a main form/subform/subsubform design.

[REAPER_110]

Hi

I had a meeting with the boss today and he is happy with the progression of the database and agreed it was much easier to adapt in the future, so thanks again for all your help. One of the first things he wants me to before I set up the forms is set up some form of login for the advisor to ensure that only people set up can access it.

Could I do this by creating a table of userid's and passwords and then create a form which did a query based on userid and password fields and would only load the main form if they matched??

Kind Regards,
Andrew.

[jzwp11]

Could I do this by creating a table of userid's and passwords and then create a form which did a query based on userid and password fields and would only load the main form if they matched??

Yes that is a common approach.

I found a demo database on another Access forum. It may be a little more than you need, but you should find it helpful. You might have to join the forum before you can download the demo database. It is an excellent forum as well!

Now that we have worked out your design, it might be time to mark this thread as solved...