Compiling Data, Not Replacing

[helpaccess]

I have a database of clients. We have various outcomes we measure monthly for each client. I have created a form for each outcome which includes a number of questions. We need to be able to save all the previous outcome answers in addition to new ones for each client. I'm not sure how to compile this data using the same form without it saving over the previous outcome information. Please let me know if this doesn't make sense. I'm using Access 2007. Thank you!

[orange]

Add a table(s) to your database to keep outcome info.
Make sure you include a datefield to identify when the outcome "happened".
You could add a unique id for an outcome (primary key)
You'll have to decide how outcome relates to Patients/Categories/etc/etc

Record individual outcomes as records in the outcome table.

Use queries to get info by patient, by date etc

[helpaccess]

Thank you for your response. I have a table with the outcome information and am doing a lookup to pull the client name and am using that as the primary key. That's why the data just saves over instead of creating a new line. Could I use the date as a primary key also? Would that eliminate the problem?

[orange]

No.

If there are multiple outcomes with different dates for different clients then NO.

Post a picture of your tables and relationships.

[helpaccess]

Screen shot attached. Thank you.

[orange]

A major problem is your table structure and relationships.

Put your separate things in separate tables
eg IMR get rid of the Client Name
add an autonumber field called IMR_Id
that should be the PK of that table.
Same for many other tables.

You will need junction tables to Relate the Client to other tables.
see http://www.databaseanswers.org/data_...care/index.htm
for data model - this may not reflect your situation, but you'll see how tables are related (I hope)

Good luck

[helpaccess]

So how are you able to know what client you are entering an outcome for if the client name is not in that table and it is just associated with a randomly generated number? Thank you.

[orange]

That's what relational data base is all about.

Suggest you look at these links
http://www.allenbrowne.com/casu-22.html Crystal's stuff

These are videos and text. You really have to spend some time with these.

Other stuff that you'll need

http://www.databaseanswers.org/approach2db_design.htm

http://www.rogersaccesslibrary.com/forum/topic238.html

[helpaccess]

You are correct. I didn't understand the relationships. Thank you so much for your help. Please see the adjustments I've made below. This makes a lot more sense, but I'm still confused on how I'll be able to have multiple RMQ outcome numbers for the same client if only one ID from RMQ relates to one Client Name. Again thank you for all your help. I'm trying to teach myself Access and it's not exactly intuitive!

[orange]

Now a few questions to help with Table structures.

Let's work on Table IMR

What does IMR mean? What would one find in table IMR?
Try writing a 2 line description of the table.

Now each of those things you show in the table
Progress
Knowledge
Involvement....

I believe these are different "types" of IMR a client could have. Is that close?
If so , we have another structure change coming.
But first, I'll get your response.

[helpaccess]

IMR is the name of one of our Outcomes. These are similar to questionaires and the headings within that table represent the questions asked. Some of the responses would be a rating on a likert scale (very involved, involved, not involved, etc). Some are open fields to be insert. Some are just yes/no answers. All of these questions are asked every 6 months. Does this help clarify?

[orange]

Do you have some dummy test data? Just data that is typical of each of the tables. No confidential info is needed. Some data may help us understand your situation.

I'm not sure I follow what IMR means. What about each of the other tables?

In a few lines in plain english what does the data represent?

[helpaccess]

Instead of IMR, lets call it Survey 1. The data in Survey 1 might be yes, most of the time, high school, $500/week, and needs assistance. Survey 1 would be asked again in 6 months and the answers might be the same or they might change.