query criteria from form

[Jake]

Hi,
I have a table that contains many yes/no fields. "Attribute 1",
"Attribute 2", "Attribute 3", etc. I would like to set up a query whose
criteria for these fields comes from a form with a check box for each field.
I tried using a reference to a check box control on a form as the criteria in
the query. For example, the criteria for "Attribute 1" in the query would be
[forms]![formname].[Attribute 1] I named the control after the field. The
problem with this is, since every field has this criteria, if Attribute 1 is
checked on the form the query will return all records with a "yes" value in
this field, but if any other attribute fields are also "yes", that record is
not included. I want those records included as well. Can someone tell me
how to construct a query that will do this? I also need to be able to check
multiple boxes on the form and return records that have a "yes" for all those
boxes, but also not exclude any records that may have additional fields that
contain a "yes".

thanks

 

[John W. Vinson]

Hi,
I have a table that contains many yes/no fields. "Attribute 1",
"Attribute 2", "Attribute 3", etc.

Then you have an incorrectly designed table. "Fields are expensive, records
are cheap"! What if you need to add a new attribute? Redesign your table,
redesign all your queries that use the table, redesign all your forms and
reports? OUCH!

I would like to set up a query whose
criteria for these fields comes from a form with a check box for each field.
I tried using a reference to a check box control on a form as the criteria in
the query. For example, the criteria for "Attribute 1" in the query would be
[forms]![formname].[Attribute 1] I named the control after the field. The
problem with this is, since every field has this criteria, if Attribute 1 is
checked on the form the query will return all records with a "yes" value in
this field, but if any other attribute fields are also "yes", that record is
not included. I want those records included as well. Can someone tell me
how to construct a query that will do this? I also need to be able to check
multiple boxes on the form and return records that have a "yes" for all those
boxes, but also not exclude any records that may have additional fields that
contain a "yes".

If you insist on using this flawed, wide-flat design, then you'll need a
criterion on each field of

=Forms!yourform!Attribute3 OR Forms!yourform!Attribute3 = False

The query grid will get REALLY REALLY UGLY (with as many calculated fields as
you have checkboxes, and many, many OR lines).

A normalized design will have three tables to model the many to many
relationship. Let's say you have a table Entities, a table Attributes (with
one record for each of your AttributeN fields), and a tall thin table
EntityAttributes with fields EntityID, AttributeNo, and AttributeValue (a
yes/no field) - or you could simply take the existance or nonexistance of a
record as the value. Your query then becomes trivial - find all records in
EntityAttributes with the desired AttributeNo values.

 

[Jake]

Thanks for the reply. I did not set up this table the way it is. It is
Medicare member information given to us by the government. There are
actually 75 different "attribute" fields, and just over 60,000 records. Is
there any efficient way to take the information from this table and construct
tables as you have described?

thanks

Hi,
I have a table that contains many yes/no fields. "Attribute 1",
"Attribute 2", "Attribute 3", etc.

Then you have an incorrectly designed table. "Fields are expensive, records
are cheap"! What if you need to add a new attribute? Redesign your table,
redesign all your queries that use the table, redesign all your forms and
reports? OUCH!

I would like to set up a query whose
criteria for these fields comes from a form with a check box for each field.
I tried using a reference to a check box control on a form as the criteria in
the query. For example, the criteria for "Attribute 1" in the query would be
[forms]![formname].[Attribute 1] I named the control after the field. The
problem with this is, since every field has this criteria, if Attribute 1 is
checked on the form the query will return all records with a "yes" value in
this field, but if any other attribute fields are also "yes", that record is
not included. I want those records included as well. Can someone tell me
how to construct a query that will do this? I also need to be able to check
multiple boxes on the form and return records that have a "yes" for all those
boxes, but also not exclude any records that may have additional fields that
contain a "yes".

If you insist on using this flawed, wide-flat design, then you'll need a
criterion on each field of

=Forms!yourform!Attribute3 OR Forms!yourform!Attribute3 = False

The query grid will get REALLY REALLY UGLY (with as many calculated fields as
you have checkboxes, and many, many OR lines).

A normalized design will have three tables to model the many to many
relationship. Let's say you have a table Entities, a table Attributes (with
one record for each of your AttributeN fields), and a tall thin table
EntityAttributes with fields EntityID, AttributeNo, and AttributeValue (a
yes/no field) - or you could simply take the existance or nonexistance of a
record as the value. Your query then becomes trivial - find all records in
EntityAttributes with the desired AttributeNo values.

 

[Peter Hibbs]

Jake,

You may be able to do that with the Excel-Access Converter Utility.
See this site :-
http://www.rogersaccesslibrary.com/...me='Excel to Access Converter Utility program'
There is a version for Access 2000/2 and Access 2003.

HTH

Peter Hibbs.

Thanks for the reply. I did not set up this table the way it is. It is
Medicare member information given to us by the government. There are
actually 75 different "attribute" fields, and just over 60,000 records. Is
there any efficient way to take the information from this table and construct
tables as you have described?

thanks

Hi,
I have a table that contains many yes/no fields. "Attribute 1",
"Attribute 2", "Attribute 3", etc.

Then you have an incorrectly designed table. "Fields are expensive, records
are cheap"! What if you need to add a new attribute? Redesign your table,
redesign all your queries that use the table, redesign all your forms and
reports? OUCH!

I would like to set up a query whose
criteria for these fields comes from a form with a check box for each field.
I tried using a reference to a check box control on a form as the criteria in
the query. For example, the criteria for "Attribute 1" in the query would be
[forms]![formname].[Attribute 1] I named the control after the field. The
problem with this is, since every field has this criteria, if Attribute 1 is
checked on the form the query will return all records with a "yes" value in
this field, but if any other attribute fields are also "yes", that record is
not included. I want those records included as well. Can someone tell me
how to construct a query that will do this? I also need to be able to check
multiple boxes on the form and return records that have a "yes" for all those
boxes, but also not exclude any records that may have additional fields that
contain a "yes".

If you insist on using this flawed, wide-flat design, then you'll need a
criterion on each field of

=Forms!yourform!Attribute3 OR Forms!yourform!Attribute3 = False

The query grid will get REALLY REALLY UGLY (with as many calculated fields as
you have checkboxes, and many, many OR lines).

A normalized design will have three tables to model the many to many
relationship. Let's say you have a table Entities, a table Attributes (with
one record for each of your AttributeN fields), and a tall thin table
EntityAttributes with fields EntityID, AttributeNo, and AttributeValue (a
yes/no field) - or you could simply take the existance or nonexistance of a
record as the value. Your query then becomes trivial - find all records in
EntityAttributes with the desired AttributeNo values.

 

[John W. Vinson]

Thanks for the reply. I did not set up this table the way it is. It is
Medicare member information given to us by the government. There are
actually 75 different "attribute" fields, and just over 60,000 records. Is
there any efficient way to take the information from this table and construct
tables as you have described?

Yes, a Normalizing Union Query. The details would depend on the actual
structure of your table. Could you post the names and datatypes of the
non-attribute fields and a couple of representative fieldnames? The query
isn't that hard to set up but I hesitate to give an example while I'm in the
dark about your actual structure.

 

[Jake]

Thanks again for the reply and your offer of help. We have decided to go in
a different direction for searching/analyzing this data which is not ideal,
but will work O.K.

 

[John W. Vinson]

Thanks again for the reply and your offer of help. We have decided to go in
a different direction for searching/analyzing this data which is not ideal,
but will work O.K.

Cool. Sometimes you have to choose your battles...

 

[noreply@noreply]

Add to that the expenditure of thinking upcoming features; meetings where zero is actually completed as far as billable product but that work to conclude the rising employment of specified items, Cheap Louis Vuitton[/url:12atskkx] are a obligation and one which staleness be tune of t (http://www.favorluxury.com/articles/Cheap-Louis-Vuitton.html:12atskkx)t.