A form for selecting tables

I currently have a group of about 20 tables; each table is essentially a
question bank for different vehicle parts, and there is no relationship to
one another. One table would be brakes; the other would be suspension and so
on. I would like to create a form that shows the different categories so the
user can select the appropriate vehicle part, this would then pull in the
relevant questions from that table. Is it possible to do this so the user
only has to check boxes and then create a report listing the relevant
questions? I’m also using access 2002.

Thanks in advance

what is the structure of your 20 tables? table names, field names...?

hth


"Ben" Ben @discussions.microsoft.com wrote in message
...
I currently have a group of about 20 tables; each table is essentially a
question bank for different vehicle parts, and there is no relationship to
one another. One table would be brakes; the other would be suspension and
so
on. I would like to create a form that shows the different categories so
the
user can select the appropriate vehicle part, this would then pull in the
relevant questions from that table. Is it possible to do this so the user
only has to check boxes and then create a report listing the relevant
questions? I'm also using access 2002.

Thanks in advance

Whilst i have a large number of tables, they all came form one large table,
i just copied the original and split it up, thinking it may be easier.

The original table has some 750 questions in it relating to vehicle parts.
Each of the questions are as a record down the left hand side, whilst along
the top there are about 40 different fields. The table is then made up of
either a Y for yes or N for no. The point being you can look across the top
of the table to the field "hydraulics" then read down for the Y's telling you
which questions are required. I can do that with a query simple enough, by
putting Y as a critera in the query. The questions are used to build vehicle
maintenance plans.

Heres where I lose the plot.

I would like to construct a form that when its opened gives the user the
option of selecting which fields they would like to search. It may be the
case that only 8 fields are needed. By selecting the appropriate fields via a
check box or similar, the user would be be presented with the questions
marked Y for the fields they selected in a report. This would then be a
maintenance plan for that particular vehicle, as opposed to having to
manually query each field.

Help would be greatly appreciated.

okay, thanks for the additional info. Access is a relational database
management system. the original table you describe is not normalized (does
not adhere to relational design principles), but 20 different tables doesn't
solve that problem. before you go any further, you need to normalize your
tables design. i'm guessing that some questions may apply to more than one
vehicle part, just in different contexts. based on that supposition, suggest
the following tables, as

tblParts
PartID (primary key)
PartName
this is basically a list of every one of those 40 or so parts, one record
for each part.

tblQuestions
QuestionID (primary key)
QuestionText
this is a list of every one of those 750 or so questions, again, one record
for each question.

tblPartQuestions
PartQuestionID (primary key)
PartID (foreign key from tblParts)
QuestionID (foreign key from tblQuestions)
this is "linking" table - a list of every question that applies to every
part. if the "hydraulics" part has 35 applicable questions, there will be 35
"hydraulic question" records in this table; if "brakes" has 23 applicable
questions, there will be 23 "brake question" records in this table, etc. if
one question applies to 11 different parts, there will be 11 records for
that question, one for each part it applies to.

the relationships are
tblParts.PartID 1:n tblPartQuestions.PartID
tblQuestions.QuestionID 1:n tblPartQuestions.QuestionID

once you've entered the parts and questions in their respective tables, and
linked parts and questions in the linking table, then you can use a form,
query, and report to allow your users to easily select specific parts and
print all the questions for each selected part. get the initial
tables/relationships and data set up, then come back for specific help with
the user interface. also, recommend you read up/more on relational design
principles, so you'll understand how to harness the power of Access in your
database. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth


"Ben" wrote in message
...

Whilst i have a large number of tables, they all came form one large
table,
i just copied the original and split it up, thinking it may be easier.

The original table has some 750 questions in it relating to vehicle parts.
Each of the questions are as a record down the left hand side, whilst
along
the top there are about 40 different fields. The table is then made up of
either a Y for yes or N for no. The point being you can look across the
top
of the table to the field "hydraulics" then read down for the Y's telling
you
which questions are required. I can do that with a query simple enough, by
putting Y as a critera in the query. The questions are used to build
vehicle
maintenance plans.

Heres where I lose the plot.

I would like to construct a form that when its opened gives the user the
option of selecting which fields they would like to search. It may be the
case that only 8 fields are needed. By selecting the appropriate fields
via a
check box or similar, the user would be be presented with the questions
marked Y for the fields they selected in a report. This would then be a
maintenance plan for that particular vehicle, as opposed to having to
manually query each field.

Help would be greatly appreciated.