Parameter query from mulitiple fields

  • Thread starter Thread starter Kelly
  • Start date Start date
K

Kelly

I have a query I'm trying to run from a table that has
many (over 20) Yes/No fields.
What I want to do is create a query that will ask (using a
parameter or a form?) what field I want to pull. Is this
possible?

As an idea of what my table looks like...
Field 1 Field 2 Field 3
yes/no yes/no yes/no

I want to be able to query any ONE of these at any time
(like I said, there are many - I'd hate to have to create
a query for each one) where the value is yes.

Is this possible, or maybe, is there a better set up for
the table that I'm querying from?

Thanks in advance,
Kelly
 
Kelly,

Yes, you are right, it is almost certain that there is a better set up
for the table. If you can post back with more details of the table and
the data, maybe with examples, someone will be able to advise further on
this aspect.
 
I have two tables; the first is one that lists a person's
name and an expression that they have said/wrote. The
second table (the one I referred to earliar) is an
extension of the first table (one-to-one) with yes/no
error code fields. The yes/no field is checked when the
expression from the first table contains that particular
error. Expressions from the first table can contain
multiple errors therefore many of the fileds in the second
table would be checked. I want to query one specific
error code and pull all the expressions for that code (but
also will need to do so with each of the codes at one
point or another).

Example data table 1:
Speaker (ID) | Expression |
Jane "It's symbol"

Example data table 2:
Noun Phrase | Verb Phrase | Adj | OTR
Y N N Y

Does this help at all? I would appreciate any advice,
even if it means restructuring!
Thanks,
Kelly
 
Kelly,

What I am going to suggest is a more normalised design, i.e. more in
keeping with database principles. As such, it will be simpler and give
you more flexibility in working with your data. Instead of recording
the errors in separate fields in a single record, it will be preferable
to record them in a single field in separate records. Probably this
will look something like this...
Table 1
ExpressionID
Speaker
Expression

Table 2
ErrorID
ExpressionID
Error

So your example data will look like this...
Table 1:
1 Jane "It's symbol"

Table 2:
1 1 Noun Phrase
2 1 OTR

Hope you get the idea of what I mean. With this, is then becomes a
relatively trivial task to make a query that can return, for example,
"all expressions where there is a OTR error", or "all expressions where
there is a OTR error but not a Adj error and the speaker's name begins
with J", or whatever you want.

As for the process of data entry of the errors, you will probably need a
subform to show this, with a combobox whose rowsource is a table which
lists the various possible error categories (i.e. one to represent each
of your existing yes/no fields).
 
Thank you Steve,
That make perfect sense!
~Kelly
-----Original Message-----
Kelly,

What I am going to suggest is a more normalised design, i.e. more in
keeping with database principles. As such, it will be simpler and give
you more flexibility in working with your data. Instead of recording
the errors in separate fields in a single record, it will be preferable
to record them in a single field in separate records. Probably this
will look something like this...
Table 1
ExpressionID
Speaker
Expression

Table 2
ErrorID
ExpressionID
Error

So your example data will look like this...
Table 1:
1 Jane "It's symbol"

Table 2:
1 1 Noun Phrase
2 1 OTR

Hope you get the idea of what I mean. With this, is then becomes a
relatively trivial task to make a query that can return, for example,
"all expressions where there is a OTR error", or "all expressions where
there is a OTR error but not a Adj error and the speaker's name begins
with J", or whatever you want.

As for the process of data entry of the errors, you will probably need a
subform to show this, with a combobox whose rowsource is a table which
lists the various possible error categories (i.e. one to represent each
of your existing yes/no fields).

--
Steve Schapel, Microsoft Access MVP


.
Click to expand...
 
Back
Top