Ok so I'm making a survey for a website and I have a series of questions where the user can rate it from a list of choices and I have these questions in a table in access and the rating options in a table. I want to have the questions display with the options in a dropdown box next to each question, I can't seem to get this to work. Here is my code:

<!--- Get ratings --->
<cfquery datasource="PFB" name="RatingsQ">
SELECT *
FROM Ratings, Questions
ORDER BY RatingID, QuestionID
</cfquery>
<!--- Get genders --->
<cfquery datasource="PFB" name="Gender">
SELECT *
FROM Gender
ORDER BY ID
</cfquery>
<!--- Get states --->
<cfquery datasource="PFB" name="State">
SELECT *
FROM State
ORDER BY StateID
</cfquery>

<!--- Get questions2 --->
<cfquery datasource="PFB" name="Questions2">
SELECT *
FROM Questions2
ORDER BY Question_ID
</cfquery>
<!----get ages----->
<cfquery datasource="PFB" name="Age">
SELECT ID, Age
FROM Age
ORDER BY ID
</cfquery>
<html>
<body>
<cfform action="insertnewreview.cfm">
<table>
	<tr>
		<td>Age:</td>
        <td> <select name="AgeID">
            <cfoutput query="Age">
            <option value="#ID#">#Age#</option></cfoutput>
            </select>
		</td>
    </tr>
    <tr>
    	<td>
        	 Gender:
         </td>
         <td> 
           <select name="Gender">
            <cfoutput query="Gender">
            <option value="#Gender#">#Gender#</option></cfoutput>
            </select>
		</td>
	</tr>
    <tr>
    	<td>
        	State:
         </td>
         <td>
          <select name="State">
            <cfoutput query="State">
            <option value="#State#">#State#</option></cfoutput>
            </select>
		</td>
    </tr>
    <tr>
<td><cfoutput query="RatingsQ"><br><br>#Question#</td><td><select name="RatingID"><option value="#RatingID#">#Rating#</option>
            </select></cfoutput></td></tr>
		

    	<tr>
    	<td><cfoutput query="Questions2">#Q#:</cfoutput></td>
        <td>Answer: <textarea></textarea></td>
    </tr>
    <tr>
  <td colspan="2" align="center">
  <input type="submit" value="Insert"></td>
</tr>
</table>
</cfform>
</body>
</html>

The group attribute of cfquery is one of the most unknown and under-appreciated pieces of the entire language. It's so incredibly simple to use it's a wonder more people aren't told about it.

I modified your output to use grouping and it should make your life a little easier (programming life at least, I'm not a messiah :D )...

Take a look at lines 67-83

<!--- Get ratings --->
<cfquery datasource="PFB" name="RatingsQ">
SELECT *
FROM Ratings, Questions
ORDER BY RatingID, QuestionID
</cfquery>
<!--- Get genders --->
<cfquery datasource="PFB" name="Gender">
SELECT *
FROM Gender
ORDER BY ID
</cfquery>
<!--- Get states --->
<cfquery datasource="PFB" name="State">
SELECT *
FROM State
ORDER BY StateID
</cfquery>
<!--- Get questions2 --->
<cfquery datasource="PFB" name="Questions2">
SELECT *
FROM Questions2
ORDER BY Question_ID
</cfquery>
<!----get ages----->
<cfquery datasource="PFB" name="Age">
SELECT ID, Age
FROM Age
ORDER BY ID
</cfquery>
<html>
<body>
<cfform action="insertnewreview.cfm">
	<table>
		<tr>
			<td>Age:</td>
			<td>
				<select name="AgeID">
					<cfoutput query="Age">
						<option value="#ID#">#Age#</option>
					</cfoutput>
				</select>
			</td>
		</tr>
		<tr>
			<td> Gender: </td>
			<td>
				<select name="Gender">
					<cfoutput query="Gender">
						<option value="#Gender#">#Gender#</option>
					</cfoutput>
				</select>
			</td>
		</tr>
		<tr>
			<td> State: </td>
			<td>
				<select name="State">
					<cfoutput query="State">
						<option value="#State#">#State#</option>
					</cfoutput>
				</select>
			</td>
		</tr>
		
		<!--- Updated grouping here --->
		<cfoutput query="RatingsQ" group="QuestionID">
			<tr>
				<td>
				<br>
					<br>
					#Question#
					</td>
					<td>
						<cfoutput>
							<select name="RatingID">
								<option value="#RatingID#">#Rating#</option>
							</select>
						</cfoutput>
				
				</td>
			</tr>
		</cfoutput>
		
		<!--- Changed this cfoutput --->
		<cfoutput query="Questions2">
			<tr>
				<td>#Q#:</td>
				<td>
					Answer:
					<textarea></textarea>
				</td>
			</tr>
		</cfoutput>
		<tr>
			<td colspan="2" align="center"><input type="submit" value="Insert"></td>
		</tr>
	</table>
</cfform>
</body>
</html>

I also changed lines 85-94 a little to make the output of the Question2 section a little cleaner.

This article has been dead for over six months. Start a new discussion instead.