I have a table that contains five fields. Each field is a category. The categories in each field can change for each record in the table.
Lets say that I have the following in my table:
ID Name Cat1 Cat2 Cat3 Cat4 Cat5
1 Amy Finance Marketing Sales IT Admin
2 Gerald Marketing Finance <Blank> Admin <Blank>
3 Terry Finance <Blank> <Blank> <Blank> <Blank>
In the list box, for Amy, I would like to see:
In the list box for Gerald I would like to see:
and for Terry I would like to see in the list box:
I need the list box to recognise that there will only ever be 5 items in the list because there are only 5 categories. I also need the list box to know that the top item is always category 1, second is always category 2 etc...
Can anybody help with the SQL as I am stuck on where to start on this one.
Any help is always appreciated.