Yes/No fields question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

smk - 02 Oct 2006 03:09 GMT

I have about 20 fields of Yes/No data type. I would like to do a parameter
query where I type in the name of a field, and it will display all the "Yes"
of the field that I typed.

Example:-
FieldName
Science - Yes/No
Math - Yes/No
Chemistry - Yes/No
Biology - Yes/No

When queried, a window box pops up and ask for the the name of the field.
When I keyed in Math, it will display all the Math records with a Yes.

Please help.

Reply to this Message

Duane Hookom - 02 Oct 2006 03:19 GMT

I expect you want to view more than just the field. You should normalize
your table structure with a union query:

SELECT FieldA, FieldB, "Science" as ClassType, [Science] as TF
FROM tblNeedToBeNormalized
UNION ALL
SELECT FieldA, FieldB, "Math", [Math]
FROM tblNeedToBeNormalized
UNION ALL
SELECT FieldA, FieldB, "Chemistry", [Chemistry]
FROM tblNeedToBeNormalized
UNION ALL
SELECT FieldA, FieldB, "Biology", [Biology]
FROM tblNeedToBeNormalized
UNION ALL
--etc---
FROM tblNeedToBeNormalized;

You can then use a form with a combo box to filter your query:

SELECT *
FROM quniMyUnionQuery
WHERE ClassType = Forms!frmYourForm!cboClassType
AND TF=True;

Signature

Duane Hookom
MS Access MVP

>I have about 20 fields of Yes/No data type. I would like to do a parameter
> query where I type in the name of a field, and it will display all the
[quoted text clipped - 12 lines]
>
> Please help.

Reply to this Message

smk - 02 Oct 2006 06:22 GMT

I'm not really an advance user in Access. I hope you don't mind explaining
why I need to declare Science as ClassType and TF. Do i need to declare for
all other fields as well? I tried to do a search in the internet but couldn't
find the answer.

Also, what if I decided not to use the Combo box to filter the query? I
would just like to have a common paramter query box to appear.

Thank you!

> I expect you want to view more than just the field. You should normalize
> your table structure with a union query:
[quoted text clipped - 37 lines]
> >
> > Please help.

Reply to this Message

smk - 03 Oct 2006 00:58 GMT

Hi,

Can somebody pls help me on my questions below?

What if I do not want to use form with combo box to filter my query? I just
wanted a usual paramter query box to appear when i run the query design. How
would the sql be?

Thanks.

> I'm not really an advance user in Access. I hope you don't mind explaining
> why I need to declare Science as ClassType and TF. Do i need to declare for
[quoted text clipped - 47 lines]
> > >
> > > Please help.

Reply to this Message

Duane Hookom - 03 Oct 2006 02:52 GMT

Why not create a more professional user interface by using a control on a
form rather than expecting the user to type a value in by memory. I suppose
it makes sense to you and your users but a "usual paramter query box" would
not be acceptable in my office.

You can't prompt for the user to enter a field name (at least not without
code which would add complexity).

Create the union query as I suggested. You can add any number of other
fields you want. Your original post didn't list any other fields so I
guessed. Your original post also didn't provide any display of what you want
to see in the query output.

If you really want to use a parameter prompt, try:

SELECT *
FROM quniMyUnionQuery
WHERE ClassType = [Enter a class type]
AND TF=True;

Signature

Duane Hookom
MS Access MVP

> Hi,
>
[quoted text clipped - 67 lines]
>> > >
>> > > Please help.

Reply to this Message

smk - 03 Oct 2006 03:29 GMT

i tried with the combo box and is it limited to max 20 entries only. I had
about 30 Yes/No data type that the users would have to select. Kindly advice
on this.

Also, hope you don't mind telling me where do i key in the SQL of
SELECT *
FROM quniMyUnionQuery
WHERE ClassType = [Enter a class type]
AND TF=True;

Thanks.

> Why not create a more professional user interface by using a control on a
> form rather than expecting the user to type a value in by memory. I suppose
[quoted text clipped - 87 lines]
> >> > >
> >> > > Please help.

Reply to this Message

Duane Hookom - 03 Oct 2006 03:57 GMT

Are you suggesting you have about 30 fields that have names like Science,
Math, Chemistry, Biology,...? In addition, you want users to be able to
specify one of these about 30 fields?

If this is your situation, you should seriously rethink your table
structure. There are some great links at regarding table design and
normalization at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Database
Design101.

Once you create your union query (or normalize your data) you create a new
query based on the union query. You can then set the criteria under the
ClassType field to:
[Enter a Class Type]

You may want to set the criteria under the TF field to True.

Signature

Duane Hookom
MS Access MVP

>i tried with the combo box and is it limited to max 20 entries only. I had
> about 30 Yes/No data type that the users would have to select. Kindly
[quoted text clipped - 106 lines]
>> >> > >
>> >> > > Please help.

Reply to this Message

smk - 03 Oct 2006 04:29 GMT

Thank you Duane.
I didn't realise that until you brought it up. However, i'm facing another
issue now. Will open a new thread.

Thank you!

> Are you suggesting you have about 30 fields that have names like Science,
> Math, Chemistry, Biology,...? In addition, you want users to be able to
[quoted text clipped - 122 lines]
> >> >> > >
> >> >> > > Please help.

Reply to this Message

John Vinson - 03 Oct 2006 05:08 GMT

>i tried with the combo box and is it limited to max 20 entries only.

Ummm...

I'd never use it, but a combo box is limited to 65536 entries (if it's
based on a Table or a Query). Where did this 20 row limit idea come
from!?

> I had
>about 30 Yes/No data type that the users would have to select. Kindly advice
[quoted text clipped - 5 lines]
>WHERE ClassType = [Enter a class type]
>AND TF=True;

In the SQL window of a new Query. Use the leftmost icon in the query
design toolbar; there's a dropdown which lets you select SQL.

John W. Vinson[MVP]

Reply to this Message

Duane Hookom - 03 Oct 2006 05:31 GMT

I expect the "limit" referred to the number of union statements SMK could
get into a query. SMK has 30 yes/no fields in a spreadsheet like table.

Signature

Duane Hookom
MS Access MVP

>>i tried with the combo box and is it limited to max 20 entries only.
>
[quoted text clipped - 19 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Vinson - 03 Oct 2006 06:25 GMT

>I expect the "limit" referred to the number of union statements SMK could
>get into a query. SMK has 30 yes/no fields in a spreadsheet like table.

ah... got it. Thanks Duane, and apologies for jumping in without
getting the background!

John W. Vinson[MVP]

Reply to this Message

Duane Hookom - 04 Oct 2006 01:45 GMT

No problem John. Jump in any time, the water's fine!

Signature

Duane Hookom
MS Access MVP

>>I expect the "limit" referred to the number of union statements SMK could
>>get into a query. SMK has 30 yes/no fields in a spreadsheet like table.
[quoted text clipped - 3 lines]
>
> John W. Vinson[MVP]

Reply to this Message