May as well make RaceID a TinyInt :)
"This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -----Original Message----- From: Richard Dillman To: SQL Sent: Tue Aug 01 23:53:32 2006 Subject: Selecting a List from a List Evening/Morning everyone (depending where you are.) :-) Im a bit stumped. This is my delima My client wants to have a form with a Checkbox list for race. They should be able to check more than one IE: Caucasian and Native American. Easy to do, *Create a table with ID's and titles for the races.* CREATE TABLE [dbo].[tbl_Race]( [Race_ID] [smallint] NOT NULL, [Race_type] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] *RaceID RaceTitle* 0 Decline/Unknown 1 Caucasian 2 African American/Caribbean/African 3 Arab/Middle Eastern 4 Asian 5 Hawaiian & Pacific Islander 6 Hispanic 7 Jewish 8 Native People (American Indian or Alaska Native) 9 Bi-Racial 10 Multi-Racial Store the item in a single delimited field in the Contact table using matching numbers 0 - 10 for easy repopulation to the page. *Create a Form Field to pass the data.* <cfoutput query="races"> <input name="DESIRED_RACE" type="checkbox" id="#RACE_TYPE#" value= "#RACE_ID#" <cfif GetContactDetails.Race EQ RACE_ID>checked</cfif>> <label for="#RACE_TYPE#">#RACE_TYPE#</label><br> </cfoutput> Now I have to create a view that will join those values to their titles in the original table. If there had been only 1 value in the field Contact table that would be easy. ** *Create a query to join the data.* ** SELECT C.RecID, C.Fname, C.Lname, C.Race, C.Address1, C.Address2, Z.City, Z.State, Z.County, C.Zip, C.Phone, C.Email, R.RaceType FROM Contact AS C LEFT JOIN Zipcode AS Z ON C.Zip = Z.Zip LEFT JOIN Race AS R ON C.race = R.RaceID (yea im actually dong 9 left joins in this view but only 2 showing here.) The snag comes in with multiple values in the Contact table. I'm a bit lost. -- Richard Dillman [EMAIL PROTECTED] (317) 916-8341 "IF-THEN-ELSE its a way of life!" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2545 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
