If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Combine many columns/fields into 1 column

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2007
Posts: 5
Combine many columns/fields into 1 column

Hi,
This looks like a uphill task for me. may be I could get some help in resolving or finding a work around.

The task is :

I have a table - Table with 100+ columns.
column1 column2 column3 ............... column100
A B C ............... R

I have to create 1 record by concatenating all the columns togather.
This merged record needs to be written to a file.

O/P

A B C ............... R

VBA is one option or write a select query. But it will very tiresome and time consuming to write up all the columns.
I will need to write a query or VBA because, I will taking a field from the user based on which the data will be pulled and written to the output file.
Is there some macro/module option to handle this.

I need all the values in the columns and columns should be in the same sequence and with their sizes as they are in the table.

Can anyone suggest any easy way to merge the columns into 1.
I cannot use 3rd party s/w.

Thanks in advance.
Reply With Quote
  #2 (permalink)  
Old
Moderator
 
Join Date: Dec 2004
Location: Madison, WI
Posts: 3,925
Create a "Combined" field in the table and then in an update query, update it with the fields you want (ie: [FirstName] & " " & [LastName]).
__________________
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Aug 2007
Posts: 5
Hi,
Thanks for the reply.
But writing it in the form of ie: [FirstName] & " " & [LastName], for 100+ fields seems very tedious.

Any other suggestions.
Reply With Quote
  #4 (permalink)  
Old
L33t Helpa Munky
 
Join Date: Nov 2007
Location: Adelaide, South Australia
Posts: 4,049
Meh. So it will take you 5 or 10 minutes. Big deal. Tediousness is often part of coding!

It will be long done by the time any easier solution is figured out.
__________________
Owner and Manager of
CypherBYTE, Microsoft Access Development Specialists.
Microsoft Access MCP.
And all around nice guy!


"Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
"...teach a man to code and he'll be frustrated for life! " -- georgev
Reply With Quote
  #5 (permalink)  
Old
Computer Monkey
 
Join Date: May 2005
Posts: 1,191
As Trekker said, just writing the "tedious" query may take a few minutes, but it'll probably be the quickest method.

The only other way around it that I see would be to loop through the Table's Field Names and create the query with code and doing a debug.print with a ctrl+c. But I'm quite sure that would be a whole lot more time consuming with all the coding and debugging you'd have to do than just creating the query by hand once.
__________________
Me.Geek = True
Reply With Quote
  #6 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,215
actually it isn't that tedious, unless you have defines columns with spaces in between

open the query designer
drag every column into the boxes (NOT the *, EVERY column)
save the query
switch to SQL view

replace the commas in the query with amerpsands
eg
Code:
select my, comma, separated, list from mytable
becomes

Code:
select my & comma & separated & list from mytable
then add a place to store the resultant mishmash
Code:
select my & comma & seperated & list as myconcatenatedcolumn from mytable

Code:
select my & comma & seperated & list & [my space separated column] as myconcatenatedcolumn from mytable
you could then modify that query so it beomes in insert query into another table, and export that table to a text file

otherwise you coudl do the whole thing using VBA to write to a file. Persoanlly I'd use a SQL query, but each to their own
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #7 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Are the values of all these columns the same width?
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Aug 2007
Posts: 5
Thank you for the replies.
I have started working in the direction to use the wizard and drag-drop the fields.

pootle_flump : No, the fields are of different size - right from text size 1 to memo field.
Reply With Quote
  #9 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Quote:
Originally Posted by Akanchu
pootle_flump : No, the fields are of different size - right from text size 1 to memo field.
In that case, how are you ever going to read this file? You either need to delimit values, or use fixed width, to allow the file to be parsed.
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Aug 2007
Posts: 5
I will need fixed size file. I will be ftping the file to m/f for further processing.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Apr 2009
Posts: 14
Akanchu,

There is a simple solution to your problem. Export the whole table to a text file. You can use what you like as delimiters for the fields but make sure it’s something that you can recognize if you need to.

Next –import the text file into a new table as a single text column.

And there you have it.

MIKE
Reply With Quote
  #12 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Quote:
Originally Posted by Akanchu
I will need fixed size file.
What is fixed file size? Do you mean fixed width?
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On