Combine columns (2000)

Topic

New Reply

I inherited a database design that has a table with 4 columns at the end labeled Sales, Service, Technology, HR
The table is class information with those 4 columns indicating the type of class for each row. The data in those 4 columns is
either a 1 or a zero. To make matters worse, there are some classes that have a 1 in Service and in technology. Poor design, I know.
I can’t change it because I will be getting a download every month to do reports off of.
What I’m looking to do is create an additional column that has the actual type of class in it. Sales, Service, Technology, HR.
I’m OK with having a class type of “ServiceTechnology” for the rows that have a 1 on both columns.
I don’t know if down the line there will be other classes with multiple 1’s for the value.
I tried using multiple update queries but didn’t know how to handle the issue of multiple 1’s.
Any help would be appreciated,
Thanks,
Scott

Reply | Quote

Replies

You could create a new table tblDescription like this:

Sales	Service	Technology	HR	Description
1	0	0	0	Sales
0	1	0	0	Service
0	0	1	0	Technology
0	0	0	1	HR
1	1	0	0	Sales Service
1	0	1	0	Sales Technology
1	0	0	1	Sales HR
0	1	1	0	Service Technology
0	1	0	1	Service HR
0	0	1	1	Technology HR
1	1	1	0	Sales Service Technology
1	1	0	1	Sales Service HR
1	0	1	1	Sales Technology HR
0	1	1	1	Service Technology HR
1	1	1	1	Sales Service Technology HR

You can, of course, modify the descriptions. You probably won’t need the last categories (three or four fields with a 1) but you never know…
Create a query based on your table and on tblDescription. Join the tables on Sales, Service, Technology and HR (i.e. 4 lines joining the tables).
Add all fields from your table plus Description from tblDescription to the query grid.

Reply | Quote

Hans,
Thanks, I never thought to use a binary type approach.
Works for me.

Reply | Quote