form that stores client info and other info in different tables

[Guest]

What I have is a table called clients, one called
ISC (independent Service Coordinators), Counties,
Provider Agencies, Domain F, Domain M, and Domain P.
The Clients table stores basic information on a client
such as F and L Name, has a Prim. Key for a Client ID,
and has a field of County, Provider, and ISC which are
linked to the corresponding ID of the table. The
Domain F, M, and P are where I want to store a set of
responses based on a form that is answered. I have the
client ID field of each domain linked back to the
ClientID of the clients table. So if someone ask
questions from form F I want them to be
stored in Domain F. There are cases where all 3 sets
of questions will be asked so I want the data to be
stored in the correct table based on the question.
Each question has a choice of 3 possible answers. Yes
No, and N/A. The layout of my Domain F table is
Client ID - relationship to main client ID
F1Y - Respresents question 1 if they answer Yes, this
is a checkbox
F1N - I want data stored here if they answer No, this
is a check box
F1NA- I want data stored here if they check N/A.
F2Y, F2N, F2NA, and so on. The M and P are the same
except it is M1Y, M1N, etc.

(The reason I don't just store a single answer into
one field is because I have to count the number of
NAs, the number of N, and the number of Y and do a
percentages based on what was answered and not
answered. In otherwords I will have a report that
shows each provider agency and out to the side it will
list the number of F1 that were answered Yes, No, and
NA, the number of F2 answered Yes, No, and NA, etc and
the final will be an overall percentage of Yes, No,
NAs. I believe I can get this part to work in a query
and then do a report based off of each query that
represents each domain.)

What I want is a form where they select the client
from the last name and it will autopopulate the client
information in the form such as First, Last Name, ISC
assigned to them, the provider agency, and the county.


Then below that I want the checkboxes listed similar
to this (the 0 represent a check box):

Y N NA
F1 0 0 0
f2 0 0 0
f3 0 0 0
etc

P1 0 0 0
p2 0 0 0
p3 0 0 0
p4 0 0 0
etc

M1 0 0 0
m2 0 0 0
m3 0 0 0
m4 0 0 0
etc

When they select a client and go through the check
boxes I want it to store the responses to the proper
tables along with the client information. So if they
answer the set of questions in F and P then the client
data should be stored in F and P and the corresponding
answers to the proper table. I have tried several
different things but I cant get anything to work. I
first tried to create an unbound form that has the
client info at the top but this will not populate like
I want it to. I also tried to use each Domain in a
subform but somehow I cant get the client data to go
over with it.

Thanks,
D

 

[Steve Schapel]

Dale,

Based on your existing table design, you should be able to have 3
subforms for each of the 3 Domain tables, with the Link Child Fields and
Link Master Fields properties of all subforms set to Client ID, and I
would expect it to "work".

However...

The reason I don't just store a single answer into
one field is because I have to count the number of
NAs, the number of N, and the number of Y and do a
percentages based on what was answered and not
answered.

This is not correct reasoning. You should have only one field for each
answer. Deriving the statistics you want will be very easy, in fact
much easier than the way you are proposing to do it!

What I want is a form where they select the client
from the last name and it will autopopulate the client
information in the form such as First, Last Name, ISC
assigned to them, the provider agency, and the county.

Various approaches to this type of functionality are discussed in this
article...
http://accesstips.datamanagementsolutions.biz/lookup.htm

When they select a client and go through the check
boxes I want it to store the responses to the proper
tables along with the client information. So if they
answer the set of questions in F and P then the client
data should be stored in F and P

The only client data that should be included in the responses tables in
the Client ID.

... and the corresponding
answers to the proper table.

I would recommend that you consider re-designing this aspect as well.
There is no reason to have the 3 Domain tables. This will give you
unnecessary headaches. All of this data should be in one table,
absolutely, with an extra field added to designate whether it is F or M
or P.

 

[Guest]

Thanks Steve,

After making the design changes and creating a response table with just a
single answer field, following the suggestions of creating a query seemed to
work. The only thing I am running into now is that I am creating a subform
for each question in order to get the answer associated with that row to be
placed on a line. Is there an easier way?

It works what I am having to do and to make it a little easier I just copy
and paste what was in the previous subform and the only thing I have to do is
to update the default value of the questionID to represent the question it
should correspond to but it seems there may be a better way to do this?

Thanks,
Dale

 

[Steve Schapel]

Dale,

There should be no reason to create a separate subform for each
question. There should just be one subform for all questions. I can't
quite envisage what you are doing. Can you post back with the details
of what you've got now as the tables and fields.