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

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
169
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:

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Maggie

How would you want to split the tables?
 

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
169
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

Board Regular
Joined
Jan 28, 2014
Messages
169
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

Board Regular
Joined
Jan 28, 2014
Messages
169
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,366
Messages
5,528,269
Members
409,813
Latest member
robyrux

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top