Yes/No fields question

G

Guest

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.
 
D

Duane Hookom

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;
 
G

Guest

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!

Duane Hookom said:
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;


--
Duane Hookom
MS Access MVP

smk said:
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.
Click to expand...
Click to expand...
 
G

Guest

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.


smk said:
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!

Duane Hookom said:
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;


--
Duane Hookom
MS Access MVP

smk said:
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.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

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;


--
Duane Hookom
MS Access MVP


smk said:
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.


smk said:
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!

Duane Hookom said:
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;


--
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
"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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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.

Duane Hookom said:
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;


--
Duane Hookom
MS Access MVP


smk said:
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.


smk said:
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:

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;


--
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
"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.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

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#DatabaseDesign101.

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.


--
Duane Hookom
MS Access MVP


smk said:
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.

Duane Hookom said:
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;


--
Duane Hookom
MS Access MVP


smk said:
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
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:

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;


--
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
"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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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!

Duane Hookom said:
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#DatabaseDesign101.

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.


--
Duane Hookom
MS Access MVP


smk said:
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.

Duane Hookom said:
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;


--
Duane Hookom
MS Access MVP


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
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:

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;


--
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
"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.
Click to expand...
Click to expand...
Click to expand...
 
J

John Vinson

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

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
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;
Click to expand...

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]
 
D

Duane Hookom

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.

--
Duane Hookom
MS Access MVP

John Vinson said:
i tried with the combo box and is it limited to max 20 entries only.
Click to expand...

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
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;
Click to expand...

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]
Click to expand...
 
J

John Vinson

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.
Click to expand...

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

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access how do you set a yes/no data type as required? 0
Evaluate Yes/No Field Based on User Input 7
IIF Function Within A Query 6
counting yes/no fields 3
Serious Problem with a Date count fields & YES/No field 4
Missing records on a CrossTab 1
Using a Yes/No field to trigger a calculation in another field - J 6
yes or no fields 3

Top