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

[Maggie Barr]

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

 

[Norie]

Maggie

How would you want to split the tables?

 

[Maggie Barr]

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

 

[Maggie Barr]

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

 

[Maggie Barr]

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