column to row

hi
I have a lot of data in one column in access. I would like to have all this 
data on the one column to be on one row.  Tried to copy it to excel and 
transpose but the data is too long to transpose. Any ideas.  i have over 200k 
rows and want to list it on one line. thanks.
0
Utf
1/17/2010 4:23:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

5 Replies
809 Views

Similar Articles

[PageSpeed] 38

t wrote:
>I have a lot of data in one column in access. I would like to have all this 
>data on the one column to be on one row.  Tried to copy it to excel and 
>transpose but the data is too long to transpose. Any ideas.  i have over 200k 
>rows and want to list it on one line.


How do you expect to display "over 200k" of anything on one
line of any printer or monitor.

I have a feeling there is a lot more to whatever you are
trying to accomplish beyond what you posted.

-- 
Marsh
MVP [MS Access]
0
Marshall
1/17/2010 5:52:14 PM
On Sun, 17 Jan 2010 08:23:01 -0800, t <t@discussions.microsoft.com> wrote:

>hi
>I have a lot of data in one column in access. I would like to have all this 
>data on the one column to be on one row.  Tried to copy it to excel and 
>transpose but the data is too long to transpose. Any ideas.  i have over 200k 
>rows and want to list it on one line. thanks.

Well, you certainly can't, not in Access: a single record is limited to 4096
bytes and 255 fields.

As Marshall says, 200,000 of ANYTHING all on one row will exceed any monitor,
or any sheet of paper, or any human capacity to understand. I cannot imagine
what it is that you're getting at. 

What is this data? What will you do when you have a million-byte long text
string (if each record takes an average of five bytes)?
-- 

             John W. Vinson [MVP]
0
John
1/17/2010 9:16:16 PM
I will break it up manually to make it more manageable but still need to know 
how to save data that is in access (in a colunmn)in a row format.  I have 
coordinates that I need to display in google maps.   I figure if I have 20 or 
so coordinates per one line.  If someone could give me general instructions 
on how I can use the excel type transpose option in access or vba, That would 
be great. Thanks.  

"Marshall Barton" wrote:

> t wrote:
> >I have a lot of data in one column in access. I would like to have all this 
> >data on the one column to be on one row.  Tried to copy it to excel and 
> >transpose but the data is too long to transpose. Any ideas.  i have over 200k 
> >rows and want to list it on one line.
> 
> 
> How do you expect to display "over 200k" of anything on one
> line of any printer or monitor.
> 
> I have a feeling there is a lot more to whatever you are
> trying to accomplish beyond what you posted.
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 
0
Utf
1/17/2010 9:26:01 PM
I fail to see why you think an odd transposing will help you
solve a problem.

If you need to interact with another program with chunks of
data in some kind of list style, then you should open a
recordset on the records, loop through however many records
and build the list using concatenation.
-- 
Marsh
MVP [MS Access]


t wrote:
>I will break it up manually to make it more manageable but still need to know 
>how to save data that is in access (in a colunmn)in a row format.  I have 
>coordinates that I need to display in google maps.   I figure if I have 20 or 
>so coordinates per one line.  If someone could give me general instructions 
>on how I can use the excel type transpose option in access or vba, That would 
>be great. Thanks.  
>
>"Marshall Barton" wrote:
>
>> t wrote:
>> >I have a lot of data in one column in access. I would like to have all this 
>> >data on the one column to be on one row.  Tried to copy it to excel and 
>> >transpose but the data is too long to transpose. Any ideas.  i have over 200k 
>> >rows and want to list it on one line.
>> 
>> 
>> How do you expect to display "over 200k" of anything on one
>> line of any printer or monitor.
>> 
>> I have a feeling there is a lot more to whatever you are
>> trying to accomplish beyond what you posted.
0
Marshall
1/17/2010 10:36:20 PM
On Sun, 17 Jan 2010 13:26:01 -0800, t <t@discussions.microsoft.com> wrote:

>I will break it up manually to make it more manageable but still need to know 
>how to save data that is in access (in a colunmn)in a row format.  I have 
>coordinates that I need to display in google maps.   I figure if I have 20 or 
>so coordinates per one line.  If someone could give me general instructions 
>on how I can use the excel type transpose option in access or vba, That would 
>be great. Thanks.  

Well, if you don't want all 200,000 records in one line (as your original post
implied) it's not all that hard. An Excel TRANSPOSE applies to an Excel
spreadsheet... and an Access Table is not an Excel Spreadsheet. 

But you can apply the VBA code at
http://www.mvps.org/access/modules/mdl0004.htm
to construct a single text string from a set of related records.
-- 

             John W. Vinson [MVP]
0
John
1/18/2010 1:42:48 AM
Reply:

Similar Artilces:

Add rows to table
This is a template - I want to be able to add rows to a table (7 colums). Below is example of table: Each <> is a form field The Net1 is a formula of GAmt1 - (GAmt1 * Rate1) By clicking the macroButton field <insert row above> a new row would be added above and the form fields to be named 'Pol2' , 'Name2', etc. Net2 would be formula same as for Net1. Next added row would have 'Pol3', Name3', etc. The field after the text 'Total' be a formula of adding the Net Amounts (Net1 + Net2 + .....) for as many rows that are added. Also would...

Charting using Stacked Column Graph
Excel 2002... I think I posted this in the wrong place before. I'm trying to chart a series that is comprised of 5 - 3 number groups. I can chart the first group, but when I try to add the next series, it stacks them onto the existing columns rather than make 3 new ones. The data looks like this (simplified): A1 A2 A3 B1 B2 B3 1-10 .01 .03 .58 .023 .025 .023 11-15 .012 .025 .025 .024 .588 .280 16-20 .012 .255 .158 .024 .254 .241 I get a nice chart with A1-A3...but can't add B1-B3 next to it. Help me, please. Thanks, Gre...

Search columns on multiple worksheets
In a workbook I have a worksheet for each month of the year. How would I be able to automatically determine whether the entry currently being made has been entered in the same column previously on any of the worksheets? (Similar to “Find” under Edit) If so, would it be possible to indicate the previously entered data by highlighting each duplicate cell? I suggest you start with Chip's site www.cpearson.com which has lots about duplicates Then come back with more detailed question best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps fro...

optional column required in datasheet view
I have a document library with two content types. I have changed all columns for both content types to optional. One content type is link to a document, for which URL is required by default. I have changed it to optional. The other content type is document. URL is not used in that content type. When I bulk edit items in datasheet, though, I can't save my changes without putting data in the URL column for documents, even though the column is not used in that content type and is set as optional. I even tried making URL optional at the site content type level and the datashee...

multipule columns as on column
I need to create a make-table query containing 6 fields.. I have 16 fields in the original table. The first is a primary key, the next fifteen and actually three groups of five (a,b,c,d,e,a1,b1,c1,d1,e1,a2,b2,c2, d2,e2) the need to be output as a,a1,a2 into a field called last name (not concatenated), b,b1,b2 into a field called first name...etc... Does anyone have any ideas On Wed, 06 Jun 2007 16:07:31 GMT, "gz3d2h" <u34843@uwe> wrote: >I need to create a make-table query containing 6 fields.. > >I have 16 fields in the original table. The first is a primary ke...

Text to columns #2
Is there a way to have a worksheet automatically change to delimite text to columns when it opens? Thanks all, Vat -- Message posted from http://www.ExcelForum.com Vato If I understand you correctly, simply record yourself doing it once and then re-run the code in future -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Vato Loco >" <<Vato.Loco.182h63@excelforum-nospam.com> wrote in message news:Vato.Loco.182h63@excelforum-nospam.com... > Is there a way to have a worksheet automatically change to delimited &...

want to add all $ in column c where column A is the same
I'm very new at Excel, and a real math dummy. I've figured out how to enter a formula when all the $ I want to add are together, but I can't figure out how to do that when I want to select only the $ values for certain items listed throughout the spreadsheet. For example: I keep a running list of Architects, their projects and $ values of each project as they are assigned. I want to automatically calculate the total current $ value for each Architect without having to sort them in order, or create a separate table for each architect. Can I do that? Here's what th...

Importing email address from column in excel sheet
Wondering how to import about 60 email addresses I have in a column in an excel spreadsheet into my address book in outlook. Or even to send one email, then navigate to the email address list without actually adding them in my address book. ...

can I put a stacked column next to a solid column
I am trying to graph some information and can't figure out how to place a solid column that references one number next to a stacked column that references two numbers. Maybe a better question is can I put two stacked columns righ next to eachother? Hi, Have a look at the links on Jon Peltier's page. http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html Cheers Andy RobXCSP wrote: > I am trying to graph some information and can't figure out how to place a > solid column that references one number next to a stacked column that > references two numbers. Maybe a...

Group DB columns to be used in pivot table
Hi, I have the following problems to prepare the DB. I need to create a Pivot Table and my source DB is Excel. Headings go from Column A to AT, but only columns A to N will be used to play around with the pivot, while columns O to AT are only to input values. How can I: 1) Transform the many columns O to AT in one field called Product so that once placed into the Column part of the Pivot I can see all the headings from O to AT 2) How to make all the O to AT cells active so that the user can enter values and see the changes or analyse using the A to N fields? Examle: A B C D E F G H I...

Cutting excess rows
I have quite a complicated query: SELECT [Loans - Payment History].DateOfPayment, [Loans - Table].[In Default], [Loans - Table].AmountofLoan, [Loans - Table].[Client ID], [Loans - Table].DateofLoanAgreementSigned, [Tbl Client Information].[Last Name], [Tbl Client Information].[First Name], [Tbl Client Information].[Session Start Date 2], [Tbl Client Information].[Session Start Date], [Loans - Payment History].PaymentDue FROM ([Loans - Table] INNER JOIN [Loans - Payment History] ON [Loans - Table].ID = [Loans - Payment History].[LoanPayment ID]) INNER JOIN [Tbl Client Informatio...

how do i change column labels back to letters? currently columns.
when i opened excel this evening, i found that columns and rows are both labeled with numbers. How do I relabel the columns alphabetically so that I can use formulas? thanks Click Tools > Options > General tab Uncheck "R1C1 reference style" > OK -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "slthompson" <slthompson@discussions.microsoft.com> wrote in message news:2DF283F7-A345-46E9-AD0C-148B8BDC200B@microsoft.com... > when i opened excel this evening, i found that columns and rows are both &g...

Count Unique text in 3 different columns
Hi, I saw a few formulas that adds Unique records when is numbers, but I have a spreasheet which I need to count the Unique records (Names) taken into consideration three diffrent columns, example; Column A Column B Column C DC&D aagis Ternure DC&D aagis Ternure DC&D adamk New Hire DC&D adamk New Hire DC&D adasilva New Hire DC&D adasilva New Hire DC&D vdafons Ternure DC&D AMPATA Ternure DC&D AMPATA Ternure DC&D AMPATA Ternure DC&D AMPATA Ternure AIO Ashaw New Hire AIO Ashaw New Hire AIO Ashaw New Hire AIO Ashaw New Hire AIO A...

hide a column
hi can u help me with this what i want is that if in a column there is written "Blank" a comman that hides that particular column plz help me with thi -- Message posted from http://www.ExcelForum.com Hi try the following macro (looks in row 1 for this word 'Blank') Sub hide_columns() Dim ColNdx As Long application.screenupdating=False For ColNdx = 1 to 256 If Cells(1, ColNdx).Value = "Blank" Then Columns(ColNdx).hidden = True End If Next ColNdx application.screenupdating=True End Sub -- Regards Frank Kabel Frankfurt, Germany >...

display total in stacked column chart
Using Windows XP and Office 2007 To make it simple to explain, I'll make up a sample stacked column chart and maybe someone can help me. Let's assume I have taken attendance in a particular class over the last 5 years and I have that attendance information broken down into males and females. So I have a data table that has the year in Column A, the number of males in attendance in Column B, the number of females in attendance in Column C, and the total attendance in Column D. I created a stacked column chart with the information from Columns B and C so that my stacked column...

Moving a column
How do i move a Vertical column over to the right & have the information move with it ? -- Dell Inspiron Pentium dual-core 2.2 GHz 2 GB DDR2 SDRAM Windows Vista Home Premium SP1 One way: select the column header (usually a letter at the top) Rightclick on that selection And select insert desgnr wrote: > > How do i move a Vertical column over to the right & have the information > move with it ? > > -- > Dell Inspiron > Pentium dual-core 2.2 GHz > 2 GB DDR2 SDRAM > Windows Vista Home Premium SP1 -- Dave Peterson Clic...

Xcel or Access sort columns of names so that I remove the intersect between 2 columns from one of the columns?
Hi, I have currently in Xcel and long list of X English phrases in one column with associated numerical data about each name in adjoining columns. I have on another workbook (which could of course be pasted into this workbook)another similarly set out column of Y English phrases again with ajoining columns having numberical data row that the phrase. I would like to be able to sort the second column of phrases removing all the phrases that occured in the first column. Ideally it would be great to have the numerical data for the remaining English phrases still on the same rows (not necessary ...

Separating data in a column
the spreadsheet i am working on has 1 column with 2 possible answers, i.e. yes/no. what i need to do is separate the replies and then total the yes replies in another cell and also total no replies in a different cell. How do i do it? The following COUNTIF function can be used =COUNTIF($A$1:$A$21,"yes") The sort or data filter command can be used to separate the replies. "Gemgirl" wrote: > the spreadsheet i am working on has 1 column with 2 possible answers, i.e. > yes/no. what i need to do is separate the replies and then total the yes > replie...

Gantt Chart Import Invalid Column Error
Trying to import an Excel table into the Gantt Chart in Visio. The Finish date works fine, but every time I try to map the Start date, I get an "Invalid Column Format" error. Tried changing the format of the Start date field, and it doesn't change anything. Excel 2007 Visio 2007 SP2 and 2010 Beta, both with the same issue. Any suggestions? some people have luck with exporting a file from Visio first, then using that as a dummy file to enter data into and import back. I also found that visio chokes on blank date fields. ...

match up column A with column B
Hi, I have a column of numbers in A, and a column of numbers in B. There are 180 numbers in A (A1:A180) and 130 numbers in B (B1:B130). I need Excel to find which numbers match up between columns, e.g look down column A and find all the matching numbers in column B, then sort the columns so the matched numbers are next to each other (so I can see which numbers don't match between columns). (I have sorted the numbers from lowest to highest down the column, so they are in numerical order). I have no idea how to do this. :confused: Thanks for any assistance you can provide. :) -- ...

Can the left column panes be frozen when doing functional chart
Am doing a swim lane flow chart that is using many pages. Would be nice to freeze the functional names on the left of each page. Much like you can do in excel. Can this be done in Visio? ...

Locating Invalid rows after restoring S2K db to S2K8 (DBCC error 2
After doing a restore to migrate a SQL 2000 database to SQL 2008 we ran the DBCC CHECKDB DATA_PURITY command which identified Invalid Rows in several tables. The data type of many of the identified columns was numeric(16,2), and we have been attempting to follow the directions for locating invalid rows with T-SQL as detailed in http://support.microsoft.com/default.aspx/kb/923247?p=1. So for a numeric(16,2), we used the following T-SQL statement: select * FROM OPPORTUNITY_FACT where RECURRING_REVENUE_AMOUNT > 99999999999999.99 -- for numeric(16,2) or RECURRING_REVENUE_AMO...

columns within columns
I'd like to make a single page spreadsheet with the twelve months across the top. within each, i would like 4 or 5 columns representing the weeks.this will be an actual 2010 calendar. the titles for the inner columns will be the date of the first monday of the week. vertical grid lines separating the months are a little darker than the grid lines separating the weeks. the y axis will be the values to be entered any suggestiions? Before you start have a look at John Walkenback's calender @ http://spreadsheetpage.com/index.php/files Look for Array Formula ...

TDD
I need to run a single test multiple times based on SETS of rows from an Excel spreadsheet - not just one row at a time. I have looked at the "data driven" test example for adding two numbers and verifying the result. The problem is that this example applies the test to only ONE row at a time and I need to apply a single test multiple times using a varying set of rows during each run. As far as I can tell a TestMethod is run only once with the exception of the "data driven" type where the method is decorated with the DataSource attribute. I need the meth...

Remove extra rows in Spreadsheet
I have an 2003 excel spreadsheet. I would like to reduce the number of rows which my stroll bar would see. For a next sheet, I can get this. Once I sroll for more row, they remain. When I do a delete, the number of row down, I would like to reduce all of the extra rows which my stroll bar will see. Thank You, Gary The number of rows is fixed (on XL2003 this is 65536), and you can't remove unused ones. However, you can hide them - click on the row identifier for the first unused row to highlight it, then hold down the <shift> key and press <end> once followed by <...