[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 729
  • Last Modified:

Split Table into four smaller tables

Hi Experts

How would i split a table in MS Access with 143700 row of data into five (5) smaller tables.

Table 1. 30000 rows
Table 2. 30000 rows
Table 3. 30000 rows
Table 4. 30000 rows and
Table 5. 23700 rows

cannot upload the data as the information is confidential.
0
keilah
Asked:
keilah
  • 4
  • 3
  • 2
  • +2
3 Solutions
 
ragoranCommented:
I will make these assumptions:

TableName is the name of the table containing all the records
IDFieldName is the name of the primary key of that table.

To create the first small table:

Select top 30000 * into SmallTable1 from TableName

To create the second small table:

Select top 30000 * into SmallTable2 from TableName
where IDFieldName > (select max(IDFieldName) from SmallTable1)

To create third small table:

Select top 30000 * into SmallTable3 from TableName
where IDFieldName > (select max(IDFieldName) from SmallTable2)


and so on...
0
 
jmoss111Commented:
My question to you is why would you want to split a table into 5 tables?

Regards,

Jim
0
 
keilahAuthor Commented:
Cannot copy it into excel............easier to split the data and work on it.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
keilahAuthor Commented:
Hi ragoran

Silly question i am guess in order to split the information i'll have to do a select query?
0
 
jmoss111Commented:
Tell me more about "copying it" into Excel.

regards,

Jim
0
 
jmoss111Commented:
I'm not ragoran, but using a query would be the easiest. But you may not have to unless you just wanted to.
0
 
ragoranCommented:
Select ... into query is a make table query.  Access will take the result of the select and copy it to a new table (SmallTableN in my example).
0
 
j8eydCommented:

Hi,

I'm assuming you have a unique id column in your table. So I would do something like this...

Create your 5 new tables with the same structure as your source table.

insert into table1 select top 30000 * from mysourcetable order by id
insert into table2 select top 30000 * from mysourcetable where id > (select max(id) from table1) order by id
insert into table3 select top 30000 * from mysourcetable where id > (select max(id) from table2) order by id
insert into table4 select top 30000 * from mysourcetable where id > (select max(id) from table3) order by id
insert into table5 select top 23700 * from mysourcetable where id > (select max(id) from table4) order by id

You may have some issues reapplying any constraints, auto numbering, etc. but this script will do the split for you.

If you need more info let me know.

Joe

0
 
ragoranCommented:
To be safer, I should have ordered the record as well:

To create the first small table:

Select top 30000 * into SmallTable1 from TableName
order by IDFieldName

To create the second small table:

Select top 30000 * into SmallTable2 from TableName
where IDFieldName > (select max(IDFieldName) from SmallTable1)
order by IDFieldName

To create third small table:

Select top 30000 * into SmallTable3 from TableName
where IDFieldName > (select max(IDFieldName) from SmallTable2)
order by IDFieldName

0
 
jmoss111Commented:
You can use this to move the entire table to multiple tabs in an Excel file without splitting into multiple tables:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21727080.html?sfQueryTermInfo=1+access+excel+export+larg+tabl
0
 
SharathData EngineerCommented:

Do you have an ID field in your table? Is it incrementing in sequential order?
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now