List box of 5 fields in one record

Hi experts,

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:

Finance
Marketing
Sales
IT
Admin


In the list box for Gerald I would like to see:

Marketing
Finance
Admin

and for Terry I would like to see in the list box:

Finance.

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.
Regards
Terry
TerenceHewettAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Try out this query:

Revert out with issues specifying the expected result set so that I can help you out further
SELECT Category from (
SELECT Cat1 as Category FROM urtable
UNION
SELECT Cat2 FROM urtable
UNION
SELECT Cat3 FROM urtable
UNION
SELECT Cat4 FROM urtable
UNION
SELECT Cat5 FROM urtable ) as temp
WHERE Category IS NOT NULL

Open in new window

TerenceHewettAuthor Commented:
Hi rrjegan17,

SELECT temp.Category AS Expr1
FROM [SELECT Category1 as Category FROM tblDetails
UNION
SELECT Category2 FROM tblDetails
UNION
SELECT Category3 FROM tblDetails
UNION
SELECT Category4 FROM tblDetails
UNION
SELECT Category5 FROM tblDetails ]. AS temp
WHERE (((temp.Category) Is Not Null));

I think this will work, but how would I limit this list to the items per record ID? The record ID is simply "ID" and is an autonumber.  To clarify, I am looking to show only those categories in the list box that relate to the ID criteria.

Thank you again.

Terry
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Missed that one and this will do for ID= 1:
SELECT Category from (
SELECT Cat1 as Category FROM urtable
UNION
SELECT Cat2 FROM urtable
UNION
SELECT Cat3 FROM urtable
UNION
SELECT Cat4 FROM urtable
UNION
SELECT Cat5 FROM urtable ) as temp
WHERE Category IS NOT NULL
AND ID = 1

Open in new window

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
A small mistake and Ignore the previous one:
SELECT Category from (
SELECT ID,Cat1 as Category FROM urtable
UNION
SELECT ID,Cat2 FROM urtable
UNION
SELECT ID,Cat3 FROM urtable
UNION
SELECT ID,Cat4 FROM urtable
UNION
SELECT ID,Cat5 FROM urtable ) as temp
WHERE Category IS NOT NULL
AND ID = 1
 
Or for Better Performance
 
SELECT Category from (
SELECT ID,Cat1 as Category FROM urtable
WHERE ID = 1
UNION
SELECT ID,Cat2 FROM urtable
WHERE ID = 1
UNION
SELECT ID,Cat3 FROM urtable
WHERE ID = 1
UNION
SELECT ID,Cat4 FROM urtable
WHERE ID = 1
UNION
SELECT ID,Cat5 FROM urtable
WHERE ID = 1 ) as temp
WHERE Category IS NOT NULL

Open in new window

TerenceHewettAuthor Commented:
Hi rrjegan17,

Your last post does work but I have a slight issue.  In my table I have

Cat1
Cat2
Cat3
Cat4
Cat5

I need the list box to always be displayed in this manner.  For example, I have a record that has a 6 in Cat1 and a 1 in Cat2.  When the list box displays the results, it is displaying it in ascending order (i.e. 1, 6).  I need the list box to always display the information in field order (i.e. Cat1, Cat2, Cat3 etc.).

Any ideas on how I would achieve this.  Your help on this has been invaluable.

Regards
Terry
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
OK.. This one should help you out
SELECT Category from (
SELECT ID,Cat1 as Category,1 as order_field FROM urtable
UNION
SELECT ID,Cat2,2 FROM urtable
UNION
SELECT ID,Cat3,3 FROM urtable
UNION
SELECT ID,Cat4,4 FROM urtable
UNION
SELECT ID,Cat5,5 FROM urtable ) as temp
WHERE Category IS NOT NULL
AND ID = 1
ORDER BY order_field

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TerenceHewettAuthor Commented:
This worked perfectly well.  I am happy to start a new Q if required, but the Cat1 to 5 fields are stored as numbers.  The numbers relate to another table.  The other table, called tblLookupCat has an ID field and a Description field.  

The ID field matches the same number as in tblDetails and the Description field shows what that number means.

For example, I currently have 2 records displayed in my list box.  A 6 and a 1.

The 6 is a representation of Admin
The 1 is a representation of Marketing.  

In the list box, although the ID's are correct, they mean nothing to the user.  Is there anyway that I can add tblLookupCat to the SQL you have created and link the ID's together so that I can display the description in the list box also?

Thank you very much again and again.

Terry
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Hope Joining with a new table will not be a concern.

If you specify the other table structure, I can help you out in the join too.
TerenceHewettAuthor Commented:
Thanks - will do that now.
TerenceHewettAuthor Commented:
No worries - I have been able to join the table with no problems. I tried this earlier and got an error.  working now, so no worries.

Thank you very much for all your help. You have saved my bacon - and lots of time trying to create the SQL myself.
Regards
Terry
TerenceHewettAuthor Commented:
many many thanks - your help has been very much appreciated.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Glad to help you out Terry
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.