473,320 Members | 1,814 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Split 1Column into Multpile Columns

XP, MS Acces2000, VBA

I have a table called IMPORT with 1 column that has 75k records. I need to split that table into another table EXPORT with 75 columns and the rows associated with each group. In IMPORT after every 75th record is another group that needs to be its own row in EXPORT. Below is a sample of the data.

IMPORT:
1
2
3
AB
12,500
?
-149
etc.

EXPORT:
1, 2, 3, AB, 12,5000, ?, -149, etc
after the 75th record another row needs to be created/appended
2, 3, 4, BC, 12,6000, ^, 167, etc

I have tried using a crosstab query but I would have to run it 1000 times per group and manually copy the result into EXPORT.

TRANSFORM Min(IMPORT.Field3) AS MinOfField3
SELECT "TEST" AS Expr1
FROM IMPORT
WHERE (((IMPORT.ID)>1 And (IMPORT.ID)<75))
GROUP BY "TEST"
PIVOT IMPORT.Field2;

Is there a way to automate this is using VBA? I am not the best programmer but I am assuming this is the only way to accomplish this task?
Thanks.
Oct 8 '06 #1
1 1488
PEB
1,418 Expert 1GB
Hi,

If you have an other column in your table Import that assigns which is the column where it should be stored your info... You don't need to use VBA!

In Excel you can generate the numbers from 1 to 75 and Paste them in each group of records...

This can be done also from Access but it's a bit complicated...

You need an other column that indicates the group of records... For exemple the first 75 records to be marked with 1, the second group with 2 etc

And when you have those columns done...So you do your crosstab... As column heading you assign the column with the numbers from 1 to 75! As value you assign the column that contains the information... Group by your column that indicates your current record..

Your crosstab is ok... See it in datasheet..

And now You can copy and Paste the records in the respective columns...

Best regards!

:)
Oct 8 '06 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Tim | last post by:
Hi I want to be able to split the contents of a text field into two or maybe three columns. The text field contains text AND HTML mark-up. My initial thought was to find the middle character...
2
by: SL_McManus | last post by:
Hi All; I am fairly new to Perl. I have a file with close to 3000 lines that I would like to split out in a certain way. I would like to put the record type starting in column 1 for 2 spaces,...
6
by: Prit | last post by:
Hi everyone I guess this should be a simple question for the gurus I have a Data in a column which is to be places in 2 columns instead of one. How do i go about doing it in MS SQL server? Could...
3
by: Jan Hanssen | last post by:
Hi! I have a list of data in a textfile which is tab delimited. Each line is seperated by a VbCrLf. I want to collect this data in a multidimensional string array. I do not wish to use a...
2
by: dineshbajaj | last post by:
Hi Everybody, I need to split an image file(*.jpeg) programmatically to nine or more equal parts. Since, the code is meant to run on Windows Pocket PC 2002, I can only use methods supported...
3
by: ashok | last post by:
Hi, I need a function that will divide text from mysql in 2 parts, so that I can display first half in one column and second half in second column. I can't find what function will do this job....
2
by: Dscar | last post by:
Hi, I am a beginner in ACCESS, I've imported data into access, and then realized that I need to split the information in one of my columns into 2 columns. the information looks like this:...
5
by: chinni0719 | last post by:
Hi I need to split the string which looks like n.col1 , b.col1 , n.col2 , b.col2, n.col3 , b.col3 , b.col4 , n.col4 , b.col5,n.col5 n.col1,n.col2 are columns these are present in...
13
by: kashif73 | last post by:
I have a texfile with hundreds of records. Each record in a line has 1250 values & values are seperated by a semi colon. IS there a way in VB.NET that i can split each line for example first 1000...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.