If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
using field name as value in query
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 |
#3
|
|||
|
|||
using field name as value in query
On Tue, 24 Nov 2009 20:08:03 -0800, 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 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] |
Thread Tools | |
Display Modes | |
|
|