Merge Columns (1 Viewer)

betheball · Jul 2, 2004

In my current table structure, there is a set of four columns named, hpothetically, T1, T2, T3 and T4. For each record only one column has a value. The rest are blank. I've decided that is a bad idea. So I am creating a new column, T5. Is there a way to merge the data from the four existing columns to the new column? Can I create a query that will do something like:

If T1 <> "" Then T5=T1
ElseIf T2 <> "" Then T5=T1
etc.

Malcy · Jul 2, 2004

Hi
If the remaining three columns are genuinely null and you are doing this as a one off wouldn't it be easier to just set up a very quick update query
If T1 is not null then update T5 to T1
run it and then simply change the column headings so that
If T2 is not null then update T5 to T12
Would take two minutes where puzzling the code might take 5
HTH

KenHigg · Jul 2, 2004

You should b able to use nested iif's. Or just do a seperate update query for each t1, t2,... that updates t5 only if there is a value...

Mike375 · Jul 2, 2004

betheball said:
In my current table structure, there is a set of four columns named, hpothetically, T1, T2, T3 and T4. For each record only one column has a value. The rest are blank. I've decided that is a bad idea. So I am creating a new column, T5. Is there a way to merge the data from the four existing columns to the new column? Can I create a query that will do something like:

If T1 <> "" Then T5=T1
ElseIf T2 <> "" Then T5=T1
etc.

That is trickier than first meets the eye.

I am assuming that you want the entries transposed on the same row so that the number of records in the table is the same for "before" and "after"

I would stick the extra field in the table and then make a rough tabular form for the five field. I would then make 4 SetVale macro for T1 to t5 and T2 to T 5 etc. A run macro will run them down the column. A condition is on the macto action lime [T1] is Not Null, [T2] Is Not Null.

Actually you only need one macro with the 4 setvalue actions. In fact you don't even need the conditions.

On your macro the 5th action line is GoToRecord Next and for the Runmacro just put the number of records for the Repeat Count. Have Echo is Yes and watch them scream down the page

Mike

KenHigg · Jul 2, 2004

'make a rough tabular form' = pedomorphic

Mike375 · Jul 2, 2004

KenHigg said:
'make a rough tabular form' = pedomorphic

He only wants the form once so it does not have to win a beauty contest.

Mike375 · Jul 2, 2004

KenHigg said:
'make a rough tabular form' = pedomorphic

It does the condition on each SetValue [T1] etc Is Not Null otherwise entries will be rubbed out

Mile-O · Jul 3, 2004

betheball said:
In my current table structure, there is a set of four columns named, hpothetically, T1, T2, T3 and T4. For each record only one column has a value. The rest are blank. I've decided that is a bad idea. So I am creating a new column, T5.

I've read the replies and they are all wrong. You have a repeating group and therefore need a new table to represent it properly.

T5, as you call it, sounds like a calculated field.

Do a search on First Normal Form (1NF) here or on Google - that will benefit you rather than listen to the "advice" offered on the other posts.

Or ask about normalisation.

Or do a search on it.

Mike375 · Jul 3, 2004

Mile-O-Phile said:
I've read the replies and they are all wrong. You have a repeating group and therefore need a new table to represent it properly.

T5, as you call it, sounds like a calculated field.

Do a search on First Normal Form (1NF) here or on Google - that will benefit you rather than listen to the "advice" offered on the other posts.

Or ask about normalisation. Or do a search on it.

Mile,

I bet my suggestion will get done what he wanted and is quick and easy to do. Another way he could do that is to just copy a field and paste append to another field at the start of the table and then delete all the empty records. I thought that was far too crude to suggest

As to his data we don't know the reason for the layout. However, lists of names that we buy are quite often in that format and I stick those into a single set of fields.

Mike

Pat Hartman · Jul 3, 2004

Mile,
I got the impression that our poster was trying to clean up a bad structure. I know that he didn't specificially say so but I would hope that once t5 is populated, the first four columns will be deleted.

Malcy's suggestion should work fine.

betheball · Jul 3, 2004

Wow! Didn't expect that much of a response. Thanks. T5 is not a calculated field, simply text. I just thought it stupid to have four fields with only one of them completed per record. So, the issue is to move all the values to T5 and then delete T1-T4 after the values are moved. What was happening is each column represented a type of event. However, each event inserts a new record with the event name being inserted under whichever type it was. The goal now is to have two columns, one being Event Type and the other being Event Name. Since the event name was in one of the 4 columns, I need to move them all to the new Event Name column and then I will delete the 4 event columns.

KenHigg · Jul 3, 2004

Betheball,

'I've read the replies and they are all wrong.' - For sure

I'm sure if you look at the immediate if function you can get it to work to merge columns. Not only can you get it to work here, but it is a handy function to use in many other places. But this by no means the only way to do it, just the way I would have done it. But after reading the other posts, I would use Malcy's suggestion...

Mike375 · Jul 3, 2004

KenHigg said:
Betheball,

'I've read the replies and they are all wrong.' - For sure

I'm sure if you look at the immediate if function you can get it to work to merge columns. Not only can you get it to work here, but it is a handy function to use in many other places. But this by no means the only way to do it, just the way I would have done it. But after reading the other posts, I would use Malcy's suggestion...

But will Malcy's rub out the T1 entries placed in T5 with T2 entries and so on or does an update like that have the effect of simply moving the entries sideways.

Mike

Pat Hartman · Jul 3, 2004

If only one of the columns t1-t4 is populated for each row, then no "overlay" will occur. If more than one column is populated in a given row, then t5 will end up with the value of the last populated column that is processed. So assuming that t1 and t3 have values and the columns are process in the order t1-t4. In that case t5 will end up with the value from t3 for the row with multiples.

If this is an issue and betheball wants to find these potential problems first, then he can run a select query like the following which will return any row where no column has a value or more than 1 column has a value:

SELECT *
FROM YourTable
Where (IIf(IsNull(t1),0,1) + IIf(IsNull(t2),0,1) + IIf(IsNull(t3),0,1) + IIf(IsNull(t4),0,1)) <> 1 ;

Look Mike! No macro!

BTW, no one pointed this out so I will -
If SomeField <> ""
Is NOT equivalent to
If IsNull(SomeField)

The first If will not return true if SomeField is null, nor will "If SomeField = Null". Read about nulls in help. It is important to understand what they are and how they work.

Mike375 · Jul 3, 2004

If only one of the columns t1-t4 is populated for each row, then no "overlay" will occur. If more than one column is populated in a given row, then t5 will end up with the value of the last populated column that is processed. So assuming that t1 and t3 have values and the columns are process in the order t1-t4. In that case t5 will end up with the value from t3 for the row with multiples.

I was right

BTW, no one pointed this out so I will -
If SomeField <> ""
Is NOT equivalent to
If IsNull(SomeField)

Pat,

I do remember reading in one of my books about Null, something about being different to a zero string.

But I do know if I have a macro that Setsvalue as and runs down each record being run by another macro

T1 to T5
T2 to T5
T3 to T5
T4 to T5

Then with what he descrined he will have is result.

In practice with names I do copy and paste append to a field I make the first field then an A to Z on that field and then delete

Now Pat here is something you can help with me as I am about to do this today and it takes me a bit of mucking about.

I have a two sbook that are the Legal Directory for Australia and The Medical Directory, will all the doctors and lawyers and their details, I buy it every couple of years. I will use the Medical Directory as the case as that is the one i am doing first.

The book has the doctors names and detail on 1010 pages. What I do is to get someone to extract the names are guidelines which I give them. However, for different reasons I want them to select the pages in random.

What I normally do (which is very tedious) is to put the numbers in one column and have a random field as well. I then sort on the random field. All that is OK and easy, but now for the messy bit.

I want the numbers in lots of columns so as they are all on one page and then the person just goes across each row picking the page number

What I have to do (after deleting the random number field) is I add several fields and then copy an appropriate number from the column with the 1010 records and paste them into Field2 and repeat the process through the various fields so that I have the 1010 spread over several fields. I suppose I could make up some Setvalue macro than ran down say the first 50 and set the value of Field2 and then the next part of the macro did the next 50 and so. I getting tired thinking about

Itsa double barrel problem as I have to do the legal book and all its numbers are completely different.

Any help, ideas, sympathy or whateve

Mike

Rich · Jul 3, 2004

Use the Columns property on a Report, again no macros needed

Mike375 · Jul 3, 2004

Rich said:
Use the Columns property on a Report, again no macros needed

I have 3 simple Reports in this data base. I went to Design View. What is Columns property and what do I do with it.

raskew · Jul 4, 2004

Friends don't let friends do macros! Please pass the Tylenol!

Pat -

This apparently being a one-time cleanup, think I'd want to find those records where more than one field (T1 - T4) has data and, since I don't know programatically which field I want to return, manually resolve the discrepancies, e.g.

Code:

SELECT
    Table16.ID
  , Table16.T1
  , Table16.T2
  , Table16.T3
  , Table16.T4
  , Abs(IsNull([t1])+IsNull([T2])+IsNull([t3])+IsNull([t4])) AS Expr1
FROM
   Table16
WHERE
   (((Abs(IsNull([t1])+IsNull([T2])+IsNull([t3])+IsNull([t4])))<>3));

...then an update query to populate [T5]

Code:

UPDATE Table16 SET Table16.T5 = [t1] & [t2] & [t3] & [t4]
WHERE
   (((Abs(IsNull([t1])+IsNull([T2])+IsNull([t3])+IsNull([t4])))=3));

...then finally a manual delete (remember, this is hopefully a one-time deal) of fields [T1] - [[T4].

Note that I was able to do this with absolutely no reference to macros (or VBA for that matter). Shaazam!

Best wishes to all,

Bob

Rich · Jul 4, 2004

On the Page SetUp from the file menu, set the size and number you want etc.

Mike375 · Jul 4, 2004

Rich said:
On the Page SetUp from the file menu, set the size and number you want etc.

That worked like a charm. Got the 1010 numbers on the one page. Thanks for that, it saved me a lot mucking about.

Mike

Merge Columns (1 Viewer)

Registered User.

Registered User.

Registered User

Mike375

Guest

Registered User

Mike375

Guest

Mike375

Guest

Back once again...

Mike375

Guest

Super Moderator

Registered User.

Registered User

Mike375

Guest

Super Moderator

Mike375

Guest

Rich

Guest

Mike375

Guest

AWF VIP

Rich

Guest

Mike375

Guest

Similar threads

Users who are viewing this thread