using field name as value in query

[Titlepusher]

I have a "contractor" table with names and phone numbers and .. many yes/no
fields which bear the names of Counties. I want to be able to search for
contractors who work in those counties checked yes. I know the value in the
field will be -1. I want to be able to select a field name (such as Boone
county) from a list and query for contractors who work in "Boone county" as
result.

I know this is not really the best way to use a rdb.. and I could simply
have a "County field" with multiple records with cifferent county names
attached to each contractor. However having the form with 90 y/n check boxes
is an easy one for people to use. So if it is possible to do this type of
query I would appreciate any help on this. Thank You, tp

 

[Duane Hookom]

You are right regarding the poor table structure. You can possibly normalize
with a union query but 90 counties might be too complex. The union query
might look something like:
SELECT CountyID, "Boone" as County
FROM tblUnNormalizedContractors
WHERE [Boone] =-1
UNION ALL
SELECT CountyID, "Wichita"
FROM tblUnNormalizedContractors
WHERE [Wichita] =-1
UNION ALL
SELECT CountyID, "Hazard"
FROM tblUnNormalizedContractors
WHERE [Hazard] =-1
UNION ALL
-- etc --;

Otherwise, you may need to use code to create the SQL property of a saved
query.

 

[John W. Vinson]

I have a "contractor" table with names and phone numbers and .. many yes/no
fields which bear the names of Counties. I want to be able to search for
contractors who work in those counties checked yes. I know the value in the
field will be -1. I want to be able to select a field name (such as Boone
county) from a list and query for contractors who work in "Boone county" as
result.

I know this is not really the best way to use a rdb.. and I could simply
have a "County field" with multiple records with cifferent county names
attached to each contractor. However having the form with 90 y/n check boxes
is an easy one for people to use. So if it is possible to do this type of
query I would appreciate any help on this. Thank You, tp

I'd really, really recommend normalizing the data into a tall/thin table as
you describe. Don't confuse data storage with data presentation! You can still
(with a little code) give the users the checkbox interface; you could even
have a map of the state with an unbound checkbox in each county. You could
then use the BeforeUpdate event of the form to poll through the checkboxes and
appropriately populate the related table.