Trying to split Access table into three tables, please help.

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
188
Hello and thank you in advance if you can help,
I am trying to split a table in Access with 4,139,844 records into three tables of 1,379,984 records each. I need three tables because I have to bring them into ArcMap as a coverage for a spatial join, and though I can bring the large file in, and create shapefiles of the locations, when I have to do the spatial join to another shapefile, it cannot create the output of all the records. I think I am maxing it out. I already split the table, or so I thought, but when I got the output from ArcMap, there were duplicates in the Global Unique Identifier. I went back to Access to check my files. What I had done was create copies of the full table, deleted the records within each copy that weren't relevant to what was supposed to be in the table. I have done this three times now, being very careful, but when I create a table and append them back together to run a duplicates query, there are duplicates. I have run a duplicates query on the full table, and there are no duplicates. It is very confusing as to how this is happening. I only have three columns, Global Unique Identifier, Latitude, Longitude, so I have no variable that makes it easy to split the data up through a query, I need to be able to do it through record number.
Any advice would be appreciated. I am new to Access, so the solution is eluding me.
Thank you,
Maggie
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Maggie

How would you want to split the tables?
 
Upvote 0
Hello Norie,
I need to split the table with 4,139,844 records in it by number of records, so the first table is 1-1,379,948, the second is 1,379,949-2,759,896, and the third is 2,759,897-4,139,844.
Thank you for your help,
Maggie
 
Upvote 0
Part of it is that I am linked to the table because the database will exceed capacity if I import it as a physical table and try to create all three in there, so what I had done before when it was imported as a physical table was created the two copies, deleted the records needed in each, and then modified the full table to be the third by deleting records. I still can't fathom why it is creating duplicates with this method.
thanks,
Maggie
 
Upvote 0
Norie,
I got it! I had to clear the DB of all data, brought the full txt file in as a physical copy again, and in a query brought all the variables in and used the auto created ID in the criteria section for ID with Between 1 And 1379948 and so forth. I then couldn't do any append queries on the results to check because of the capacity limitations, so I exported them from each query to a txt file, started a new DB, created a table with the Global Unique Identifier as the only column, and did a series of appends to re-merge the files and ran a duplicates query check, and all was good. It still baffles me that by selecting and deleting records in copies of a table, something was going VERY wrong, but I know the right way to do it now. Just in case anyone is curious I got the information (with some modifications to my situation) for this from:
https://superuser.com/questions/567136/export-only-specific-range-of-rows-in-access/567137
I want to thank you for reaching out to help, Norie, I appreciate it, and always appreciate the wonderful people here on this forum!
Thanks,
Maggie
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top