CODEWITHSUNDEEP
sqlms-accessms-access-2007
morganpdx
morganpdx

Reputation: 1876

Combine multiple columns into a single column in new table

I have an Access 2007 table called Master which contains, among many many others, the following columns:

Master.UID, 
Master.[Team Comments], 
Master.[Judy Comments], 
Master.[Sue Comments], 
Master.[Paul Comments], 
Master.[CSE Comments], 
Master.[CSE Name], 
Master.[Sue Comments1], 
Master.[David Comments]

I would like to create a new table called Comments which looks like this:

CommentID
UID
Name
Comment

There are several issues here:

  1. There are two Sue comment columns; these should not be concatenated, but a seperate row put in for each entry.
  2. With one exception, the value of the Name column is in the name of the Master column
  3. The exception is the CSE comment and CSE name
  4. The UID is the foreign key to the Master table
  5. Skip any blank comment fields, of which there are many.

As you can see, I'm trying to apply just a touch of normalization to one craaaaaazy big table.

TIA!

Upvotes: 1

Views: 1909

Answers (1)

Christian Specht
Christian Specht

Reputation: 36421

This is apparently a one-time action, so there is no need to do this in one single query.
This means that it's actually very easy, because you can build a separate query for each value and just execute one after another.

The basic pattern looks like this (for the Team Comments column). 

insert into comments (UID, Name, Comment)
select UID, 'Team', [Team Comments] from master where [Team Comments] is not null

(Note that I assume that the CommentID is an auto value field, so I don't have to deal with it at all)

For the two Sue Comments columns, you just execute two similar queries in a row:

insert into comments (UID, Name, Comment)
select UID, 'Sue', [Sue Comments] from master where [Sue Comments] is not null

insert into comments (UID, Name, Comment)
select UID, 'Sue', [Sue Comments1] from master where [Sue Comments1] is not null

And for the "CSE" columns...I understood that the Name for the CSE Comments column is not fixed as in the other examples, but it should be taken from the CSE Name column instead.
Correct? If yes, then the query would need to look like this:

insert into comments (UID, Name, Comment)
select UID, [CSE Name], [CSE Comments] from master where [CSE Comments] is not null

Upvotes: 2

Related Questions