Best Approach 1

[pgferro]

Hi guys,

I have a form where the user can multiple check various options (medical specialities) dynamically created from a table, and eventually in a later moment edit the choices adding or unchecking options.
These options are not fixed, meaninig that the number of possible choices can grow or shrink depending on necessities (let's say a new department has been added or cancelled).
For this reason I cannot define some logical fields in the 'main' table, but I have to create a separate table to store the choices.

Now my question:

when the user edit his choices, what is the best approach to show the ones already chosen checked and the other unchecked ?
My first impulse was to display the one checked first, selecting from the 'child' table, and then using a "SELECT NOT IN (SELECT)" to display the others unchecked from the specialties table. Let's say, for the sake of clarity :

fisrtSQL="SELECT specialtyID FROM Choices WHERE id=" & id
WHILE ....
[i] create the checked checkboxes[/i]
WEND
secondSQL="SELECT * FROM Specialties WHERE specialtyID NOT IN ("& firstSQL& ")"
WHILE ....
[i] create the unchecked checkboxes[/i]
WEND

Any idea of a better way ??

Thanks !!

 

[Tarwn]

I would display them in the same order (alphabetically) and do some sort of Left join to get thedata, something like:
SELECT Specialties.specialtyID, Specialties.specialtyText, Choices.id FROM Specialties LEFT JOIN Choices ON Specialties.specialtyID = Choices.id ORDER BY Specialties.specialtyText

Then when you loop through the recordset to output the checkboxes you will have all of the specialty id's and text fields (I made the name up but assume you have something similar). Records that have a Choice id associated with them will have a value (so check that checkbox), Specialty records that didn't have an associated Choice record should have a null in the third field (Choice.id).

-T


_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.

 

[pgferro]

Thanks Tawrn, very nice !

--
PG

 

[Tarwn]

Glad to be of assistance, keep 'em coming, I love the "find an approach" questions

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.