using field name as value in query

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     
0
Utf
11/25/2009 4:08:03 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1008 Views

Similar Articles

[PageSpeed] 41

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.
-- 
Duane Hookom
Microsoft Access MVP


"Titlepusher" wrote:

> 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     
0
Utf
11/25/2009 5:33:02 AM
On Tue, 24 Nov 2009 20:08:03 -0800, Titlepusher
<Titlepusher@discussions.microsoft.com> wrote:

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

-- 

             John W. Vinson [MVP]
0
John
11/25/2009 5:56:38 PM
Reply:

Similar Artilces: