Results 1 to 3 of 3
Thread: Combine columns (2000)

20061130, 19:36 #1
 Join Date
 Nov 2001
 Location
 Atlanta, Georgia, USA
 Posts
 274
 Thanks
 0
 Thanked 0 Times in 0 Posts
Combine columns (2000)
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

20061130, 20:10 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Combine columns (2000)
You could create a new table tblDescription like this:
<table border=1><td align=center>Sales</td><td align=center>Service</td><td align=center>Technology</td><td align=center>HR</td><td align=center>Description</td><td align=right>1</td><td align=right>0</td><td align=right>0</td><td align=right>0</td><td>Sales</td><td align=right>0</td><td align=right>1</td><td align=right>0</td><td align=right>0</td><td>Service</td><td align=right>0</td><td align=right>0</td><td align=right>1</td><td align=right>0</td><td>Technology</td><td align=right>0</td><td align=right>0</td><td align=right>0</td><td align=right>1</td><td>HR</td><td align=right>1</td><td align=right>1</td><td align=right>0</td><td align=right>0</td><td>Sales Service</td><td align=right>1</td><td align=right>0</td><td align=right>1</td><td align=right>0</td><td>Sales Technology</td><td align=right>1</td><td align=right>0</td><td align=right>0</td><td align=right>1</td><td>Sales HR</td><td align=right>0</td><td align=right>1</td><td align=right>1</td><td align=right>0</td><td>Service Technology</td><td align=right>0</td><td align=right>1</td><td align=right>0</td><td align=right>1</td><td>Service HR</td><td align=right>0</td><td align=right>0</td><td align=right>1</td><td align=right>1</td><td>Technology HR</td><td align=right>1</td><td align=right>1</td><td align=right>1</td><td align=right>0</td><td>Sales Service Technology</td><td align=right>1</td><td align=right>1</td><td align=right>0</td><td align=right>1</td><td>Sales Service HR</td><td align=right>1</td><td align=right>0</td><td align=right>1</td><td align=right>1</td><td>Sales Technology HR</td><td align=right>0</td><td align=right>1</td><td align=right>1</td><td align=right>1</td><td>Service Technology HR</td><td align=right>1</td><td align=right>1</td><td align=right>1</td><td align=right>1</td><td>Sales Service Technology HR</td></table>
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.

20061130, 20:43 #3
 Join Date
 Nov 2001
 Location
 Atlanta, Georgia, USA
 Posts
 274
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Combine columns (2000)
Hans,
Thanks, I never thought to use a binary type approach.
Works for me.