Restricting data from tables while in forms

Difficult problem to explain, and probably overly-simply
to fix - but I'm new at this, so bear with me...

Database is a listing of "contact made" with regards to
certain companies and questions they have been asked.

I have two tables - a table of company names and a table
of questions (54 questions). Problem - each company has
been asked only certain questions - ie questions 1, 4 and
36-39.

When filling in forms (filled in by many users unfamiliar
w/ access), how can I force when user pulls up company
ABC, it only shows the questions pertaining to that
company, instead of the entire list of questions? They
have to choose the question their "contact" pertains to
and I want to limit the list so as to reduce errors.

I am a little lost, but it sounds like you want the users to pull up only
questions that have been asked of a particular company? If so, then what?
Do they enter answers? Who decides which questions go with which companies?

This sounds liek a case where a thrid table would be needed. In this table,
you would add records to indicate which questions apply to each company.
Then, the form you mention earlier would simply use this new table as it's
source.

Post more details if you need further assistance.

Rick B


"BJClaywell" wrote in message
...
Difficult problem to explain, and probably overly-simply
to fix - but I'm new at this, so bear with me...

Database is a listing of "contact made" with regards to
certain companies and questions they have been asked.

I have two tables - a table of company names and a table
of questions (54 questions). Problem - each company has
been asked only certain questions - ie questions 1, 4 and
36-39.

When filling in forms (filled in by many users unfamiliar
w/ access), how can I force when user pulls up company
ABC, it only shows the questions pertaining to that
company, instead of the entire list of questions? They
have to choose the question their "contact" pertains to
and I want to limit the list so as to reduce errors.

On Mon, 7 Jun 2004 10:35:42 -0700, "BJClaywell"
wrote:

Difficult problem to explain, and probably overly-simply
to fix - but I'm new at this, so bear with me...

Database is a listing of "contact made" with regards to
certain companies and questions they have been asked.

I have two tables - a table of company names and a table
of questions (54 questions). Problem - each company has
been asked only certain questions - ie questions 1, 4 and
36-39.

I'm guessing you've made the very common and reasonable error of
having a table with 54 fields for the 54 questions. If so, what you
ask is rather difficult to manage!

Instead, consider a three-table solution. You have a many to many
relationship between Companies and Questions. Try using:

Companies
CompanyID
info about the company

Questions
QuestionNo
Question text

QuestionsAsked
CompanyID link to Companies
QuestionNo link to Questions
Answer text or whatever is appropriate

If you have a Subform based on QuestionsAsked it will show only those
records that you have specifically asked that company.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Thank you both for the information! John - luckily, I
learned the hard way quite some time ago not to do 54
fields for 54 questions... I only have the two fields
you suggested, with 54 records - each containing a
question, so that makes all this much, much easier! I
understand what needs to be done now, and it makes perfect
sense to me! Thanks so much.
-----Original Message-----
On Mon, 7 Jun 2004 10:35:42 -0700, "BJClaywell"

wrote:

Difficult problem to explain, and probably overly-simply
to fix - but I'm new at this, so bear with me...

Database is a listing of "contact made" with regards to
certain companies and questions they have been asked.

I have two tables - a table of company names and a table
of questions (54 questions). Problem - each company has
been asked only certain questions - ie questions 1, 4
and
36-39.

I'm guessing you've made the very common and reasonable
error of
having a table with 54 fields for the 54 questions. If
so, what you
ask is rather difficult to manage!

Instead, consider a three-table solution. You have a many
to many
relationship between Companies and Questions. Try using:

Companies
CompanyID
info about the company

Questions
QuestionNo
Question text

QuestionsAsked
CompanyID link to Companies
QuestionNo link to Questions
Answer text or whatever is appropriate

If you have a Subform based on QuestionsAsked it will
show only those
records that you have specifically asked that company.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
.