How do I get copied information saved within another table?

[REAPER_110]

Hi,

PROBLEM: I have a table linked to another table. I have created a form which basically identifies training issues which can be selected from a drop down. Once a training issue has been selected an "add task" button can be clicked bringing up another form which automatically copies the selected training issue from the previous form and allows the user to complete the task details. My problem is that the information copied from the previous form is not saved to the 2nd table which records all added tasks. I want to be able to use this to eventually build an action plan for each person. What am I doing wrong?? The 2nd form stores all other information that is entered into it within the 2nd table except for the fields copied from the 1st form drop downs etc.



Any help greatly appreciated.

[jzwp11]

Can you provide more details of the tables involved (fields and relationships to each other) and specifically to which tables the forms in question are bound.

[REAPER_110]

How do I upload the database I have created so you can see what I mean??

[jzwp11]

I pulled this from the FAQ. I generally would use the compact and repair utility on the database and then zip the file and post that. I do not know if there are any restrictions to attaching anything for new forum users. I guess you will find out if the FAQ suggesting does not work. You could also just type in the table and field names like this

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-fkTitleID foreign key to tblTitle

tblTitles
-pkTitleID primary key, autonumber
-txtTitle

Attachments and ImagesHow do I attach a file to a post?
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.
On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.
To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.
Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.
What files types can I use? How large can attachments be?
In the attachment window you will find a list of the allowed file types and their maximum sizes. Files that are larger than these sizes will be rejected. There may also be an overall quota limit to the number of attachments you can post to the board.
How do I add an image to a post?
If you have uploaded an image as an attachment, you can click the arrow next to the 'Attachment Icon' and select it from the list. This will be inserted into your post and can be located where you want it displayed.
To include an image that is not uploaded as an attachment and is located on another website, you can do so by copying the full URL to the image, (not the page on which the image is located), and either pressing the 'Insert Image' icon or by typing [img] before the URL and [/img] after it, ensuring that you do not have any spaces before or after the URL of the image. You can insert pictures from your albums (?) in this way too.

[REAPER_110]

I just compacted and uploaded the file for you to see what I mean.

The client details are entered and this is limited to one entry per person, when the advisor gets to the circumstances tab and chooses which card a client selects, at the end of each line is an add task button. The form that then opens automatically copies some of the information from the previous form and the advisor completes the rest. It does not, however save all the information into the action plan table as I want it to do. I want to be able to add multiple tasks for each person and then once this is working I will set up an action plan report which the advisor can then print off for each client.

I hope what I have written is understandable and thank you in advance for your help.

Kind Regards,

REAPER_110

[jzwp11]

First and foremost, you have several structural problems with your tables. In the action plan table you have a series of questions; technically speaking the questions should each be a record in table that holds only the questions and you would relate them to the client. What would happen if you had to add a new question at some point in the future? You would have to redesign your table and all related forms, reports and queries--not something you want to do. The IA CCM Data also has major issues. You have various series of fields that are sequentially numbered. These fields are a prime example of repeating groups which indicates that your tables are not normalized. For more on normalization, you will want to start with this site. Essentially if you have a series of related items, they should be records in a table not fields.

Before you spend any more time working on your forms, I strongly suggest you focus on getting the table structure fixed up. It will be well worth the effort & save you from a lot of headaches later on.


Just a couple of other things, it is best not to have spaces or special characters in your table and field names. Additionally, there are several reserved words in Access such as date, time, name, value (and many more) that should not be used as table or field names.

[REAPER_110]

Hi,

I work for a national charity who help people who are on health related benefits back towards work. The database is a trial on a new technique of interview with the clients. It needs to be able to hold client details and also next steps set via an action plan etc so progress can be tracked. Eventually the first form loaded will allow the advisor to select either a search to find existing client data or new client to add another client to the database. Did I explain enough on what the database is needed for??

Thanks for all your help.

[jzwp11]

Let's try to sort out your table structure.

First we need a table that holds the basic info about each client


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 regarding the action plan, are the items of an action plan directly related to a question/response that came out of the interview or is it developed indirectly from the interview?

If a client solicits your organization's help multiple times, I assume that you could conceivablely have multiple action plans so it might be best to tie the action plan to the "interview event".

Could you provide more detail on how the process of developing an action plan works?

[REAPER_110]

Hi,

I have followed your advice and have set up tables as stated unless I have done anything wrong?? The only question I have is how I should set up the tblQuestions as most of the questions rely on answers selected from a drop down of set choices. We have, for example a set of 18 cards, the client can choose upto 6 of those to work on at any time. I have not deleted the old files just in case I need any info from them but have set up the new one as you stated

Also, once the client has selected those cards, this is where the action plan comes in. On my old form I had an add task button which loaded a second form. It copied information about the card selected and then allowed the advisor to agree a next step with the client on how they were going to move things forward to try and overcome the barrier the client had chosen.

I hope what I have written makes sense??

What do I need to do next??

Thanks again for your help,

Reaper_110

[jzwp11]

...as most of the questions rely on answers selected from a drop down of set choices

Do all of the questions rely on answers selected from a drop down. If not, can all questions rely on answers selected from a drop down? Based on your answers we will have to tweak the structure a little.

I'm a little unclear are the cards you refer to the questions? Or are there a group of cards related to each question?

[REAPER_110]

Hi,

Ok, sorry, I will try to give a better explanation. The client comes to an appointment. If you have a look at my old database the questions from each tab that need to be drop downs are as follows:-

Aspirations tab
Industry
Category

Cicumstances tab
Chosen cards (numbered 1-6, - each one has a drop down of 18 possible choices)
Support level
Support need

Capability
All "how good am I at" questions
Work experience level

Motivation tab
All questions have 2 drop downs that need completing

The results all need to be recorded within relevant tables. Would I need to create tables for each tab in effect??

Regarding the cards, we have a set of 18 cards all with clients possible barriers to work. The client chooses 6 of the 18 possible barriers to work on at a time.

All other areas are free text entries.

I hope this helps explain in some kind of way?? Also, I set the tables up as you suggested, a quick question I had was if for example the primary key on one table is set to autonumber do I set the foreign key within another table to autonumber also??

Thanks for your help

[jzwp11]

I hope this helps explain in some kind of way?? Also, I set the tables up as you suggested, a quick question I had was if for example the primary key on one table is set to autonumber do I set the foreign key within another table to autonumber also??

The foreign keys should be long integer number datatypes (you cannot have 2 autonumber fields within the same table)

Aspirations tab
Industry
Category

Cicumstances tab
Chosen cards (numbered 1-6, - each one has a drop down of 18 possible choices)
Support level
Support need

Capability
All "how good am I at" questions
Work experience level

Motivation tab
All questions have 2 drop downs that need completing

Are all of the above essentially various "questions" but in different categories (capability, motivation etc.)?

Regarding the cards, we have a set of 18 cards all with clients possible barriers to work.

It looks like the 18 cards fall under the circumstances category, correct? Regarding the support level/need do these two fields relate to the particular card chosen?

[REAPER_110]

The foreign keys should be long integer number datatypes

Ok, have sorted the foreign key datatypes.

Are all of the above essentially various "questions" but in different categories (capability, motivation etc.)?

Yes, they are the questions within each category that require drop down answers. There are other questions within each category that just require free text entry.

It looks like the 18 cards fall under the circumstances category, correct? Regarding the support level/need do these two fields relate to the particular card chosen?

Yes, correct. The client chooses 6 of the available choices then the advisor selects from drop downs the support level and support needed to overcome the barriers selected.

Regards,
Andrew.

[jzwp11]

I am thinking that it might be better to separate the 18 card stuff (Circumstances) from the other questions since the action plans are related to the 6 cards that are selected, correct?

[REAPER_110]

Yes, this is correct, the action plan is made up of the 6 cards from the 18 that are chosen. The advisor then has other sections to complete free text that are also added to complete the action plan. If you look at my old file on the circumstances tab I believe, after the 6 cards are chosen the advisor can click on "add task" and that brings up another form which has all the information required for the action plan. What would be my next step to getting this database set up correctly??

Kind Regards,
Andrew.