Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    3 Star Lounger
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •