MS ACCESS database General

[Guest]

Hi All,

I have 600 records in a msaccess table and want to select 1000 records into
new tables ie, want to make 6 tables 1-1000, 1001-2000, 2001-3000 etc. For
the first one I could use select top 1000 * into table1 from maintable6000.
How do I select the next 1000 into table 2 and next 1000 into table 3 etc.

 

[Rick B]

Again, WHY?

You can use wueries to break up the data. Why make new tables?

If this is a one-time thing, then why not open the table and use
select/copy/paste to move them?

Or, do your query and move 1,000 records, then delete them. Then run the
query again to move the top 1,000 records, then delete them. Then do your
query again....


BUT, you seem to have ignored the fact that you are most likely getting them
in no particular order and what you call the "first" 1,000 records is NOT
necesarily what you want to get.

 

[Guest]

I do not have any specific criteria to break up the data for first and next
1000 records. We just have to do this to send data in 6 different files
having 1000 unique records to different locations (no criteria specified). I
can move one set of data and delete it and do the next 1000 but is there any
other alternative ?

Thanks
Madhuri

 

[Brendan Reynolds]

If you don't already have one, add an AutoNumber field to the table. You can
then do something like the following. You'll need to adjust the numbers - my
test table had 30 records that I divided into three tables of ten records,
so my 1 to 3 needs to become your 1 to 6, my TOP 10 needs to become your TOP
1000 and my * 10 needs to become your * 1000 ...

Public Sub PartitionData()

Dim strBase As String
Dim strSQL As String
Dim lngLoop As Long

strBase = "INSERT INTO tblNew%1 ( TestText ) " & _
"SELECT TOP 10 tblOriginal.TestText " & _
"FROM tblOriginal " & _
"WHERE (((tblOriginal.TestID) > %2)) " & _
"ORDER BY tblOriginal.TestID;"

For lngLoop = 1 To 3
strSQL = Replace(strBase, "%1", CStr(lngLoop))
strSQL = Replace(strSQL, "%2", CStr((lngLoop - 1) * 10))
CurrentProject.Connection.Execute strSQL
Next lngLoop

End Sub

 

[Brendan Reynolds]

BTW: My suggestion depends on the value of the AutoNumber field being
sequential. If you just added it, it will be. If it already existed, and may
have gaps, you could make a copy of the table, delete the AutoNumber field
from the copy, and add a new one.

 

[John Vinson]

I do not have any specific criteria to break up the data for first and next
1000 records. We just have to do this to send data in 6 different files
having 1000 unique records to different locations (no criteria specified). I
can move one set of data and delete it and do the next 1000 but is there any
other alternative ?

I guess I'd suggest creating a new table with all the fields of this
one, and adding an Autonumber field. Run an Append query to migrate
the data into this new table.

Then you can use criteria

BETWEEN 1 AND 1000
BETWEEN 1001 AND 2000

etc. to create subsets; you can export to an external file from these
queries, without the need to create a new table.

John W. Vinson[MVP]

 

[Guest]

Thanks Rick.. I think there couldnt be any better alternative than what you
suggested. Thanks for support

 

[Guest]

Thank you Brendan
Your code seems to be helpful

If you don't already have one, add an AutoNumber field to the table. You can
then do something like the following. You'll need to adjust the numbers - my
test table had 30 records that I divided into three tables of ten records,
so my 1 to 3 needs to become your 1 to 6, my TOP 10 needs to become your TOP
1000 and my * 10 needs to become your * 1000 ...

Public Sub PartitionData()

Dim strBase As String
Dim strSQL As String
Dim lngLoop As Long

strBase = "INSERT INTO tblNew%1 ( TestText ) " & _
"SELECT TOP 10 tblOriginal.TestText " & _
"FROM tblOriginal " & _
"WHERE (((tblOriginal.TestID) > %2)) " & _
"ORDER BY tblOriginal.TestID;"

For lngLoop = 1 To 3
strSQL = Replace(strBase, "%1", CStr(lngLoop))
strSQL = Replace(strSQL, "%2", CStr((lngLoop - 1) * 10))
CurrentProject.Connection.Execute strSQL
Next lngLoop

End Sub

 

[Guest]

Thanks John
That could be the shortest route to do.

Madhuri