I am looking for a way to split a table either before or during an export to a text file. If possible I would like Access to find the mid way point of the file based off a specific field. The field I would like to use is actually generated by a mailing verification software, so it might have a couple thousand entries that say "1" or "2". So in a 100,000 record table I might only have up to number "33" or so. Does anybody know where I would even start to try and accomplish this???

- A form Developer who got roped into Access and VB.

What is the value that will determine the midpoint? You say 1 or 2 or up to 33, I'm not sure what you mean.

I want the table split as close to in half as possible. I need to automate this split process to find the total number of "Trays" and split half the trays to one table and half to another. The trays must stay togather. So if a file has 31 trays out of 100,000 records I would like two tables to be generated, Table one would be tray 1 to 15 and Table two would be 16 to 31.

An example of a table I would want to split would be...
records 1 to 5,432 will have "1" in the tray field
records 5,433 to 9,012 will have "2" in the tray field
records 9,013 to 10,698 will have "3" in the tray field
records 10,699 to 15,641 will have "4" in the tray field

The first split table would have records 1 to 9,012 in it.
The second split table would have 9,013 to 15,461 in it.

The Trays need to be kept whole. I can have any number of Trays from 2 and up. The highest I usually deal with is around 600 trays.

Try this select query with a subquery. The subquery gets the max tray number does integer division "\" then uses the primary key as the criteria in the main query.



Convert it to a make table query and to get the other half change in In () criteria to Not In ()

I reread your post and saw where you were exporting to a text file, so you don't need to convert to a make table query. Just use the SELECT statements.

Thank you very much. Everything worked perfect.