Query or script to sort data needed.

[RonVACA]

Access 2003, Windows XP Pro

Trying to take one field of values in a table select a range of records and then skip a range of records and then select a new range of records to append into a new table.

For example, if my inital field has 100,000 records in it and I want to select the first 1,000 records then skip the next 3,000 records, then select the next 1,000 records and continue this until the end of the initial field appending these 1,000 record sets into a new table.

Thanks for any help
Ron

[Rabbit]

What is the rhyme and reason for this?

[RonVACA]

we need to set up the data in a specific order for a printing process and the primary field has millions of records

[Rabbit]

What I meant is if there's some sort of process you use to determine which ones need to be printed. Preferably something that relates to what's actually in the tables. Or if these numbers are constant and will never change.

[RonVACA]

The data is a fixed set of 5 million records. we have three fields in the data an ID, Serial, AlphaAlias. We are printing labels each one unique with a serial number and an alpha numeric alias each roll of printed labels is 6500 long. However we are printing four labels at a time. the printer driver needs the data in a flat file that mimicks the four across label configuration. So we need to take the single long list of records and break it into four sets of Serial, AlphaAlias pairs. The end result will be a flat table that has 9 fields with 1.25 million records. The fields will be- ID, Serial1, AlphaAlias1, Serial2, AlphaAlias2, Serial3, AlphaAlias3, Serial4, AlphaAlias4. The serial numbers in the initial data are sequential so after running the process the Serial1 column will have serial numbers 1-6500,26001-32500,52001-58500 and so on. Serial2 will have serial numbers 6501-13000, 32501-39000, 58501-65000 and so on.
Sorry for the long winded explanation.

[Rabbit]

The data is a fixed set of 5 million records. we have three fields in the data an ID, Serial, AlphaAlias. We are printing labels each one unique with a serial number and an alpha numeric alias each roll of printed labels is 6500 long. However we are printing four labels at a time. the printer driver needs the data in a flat file that mimicks the four across label configuration. So we need to take the single long list of records and break it into four sets of Serial, AlphaAlias pairs. The end result will be a flat table that has 9 fields with 1.25 million records. The fields will be- ID, Serial1, AlphaAlias1, Serial2, AlphaAlias2, Serial3, AlphaAlias3, Serial4, AlphaAlias4. The serial numbers in the initial data are sequential so after running the process the Serial1 column will have serial numbers 1-6500,26001-32500,52001-58500 and so on. Serial2 will have serial numbers 6501-13000, 32501-39000, 58501-65000 and so on.
Sorry for the long winded explanation.

Not at all, it was necessary to clarify the situation to better understand what you are looking for. I'll get back to you unless some one beats me to the punch

[RonVACA]

Thanks in advance for the help

[Rabbit]

Need one more piece of information, is the table sorted on one of the fields? Or does it have a field that get's larger with each succeeding record? Like an Autonumber field?

[RonVACA]

the ID field is an autonumber field and the serial field increments by one for each record.

[Rabbit]

So the serial field starts at 1 and then increases by 1 until you get too the 100,00th record? And there are no gaps in between?

If so, then you made it a lot easier because I won't have to go through all that.

[RonVACA]

the ID field starts at 1 and goes to 5,000,000 the Serial field starts at 20,000,000 and goes to 24,999,999 and yes they increment by 1 in both fields

[Rabbit]

The data is a fixed set of 5 million records. we have three fields in the data an ID, Serial, AlphaAlias. We are printing labels each one unique with a serial number and an alpha numeric alias each roll of printed labels is 6500 long. However we are printing four labels at a time. the printer driver needs the data in a flat file that mimicks the four across label configuration. So we need to take the single long list of records and break it into four sets of Serial, AlphaAlias pairs. The end result will be a flat table that has 9 fields with 1.25 million records. The fields will be- ID, Serial1, AlphaAlias1, Serial2, AlphaAlias2, Serial3, AlphaAlias3, Serial4, AlphaAlias4. The serial numbers in the initial data are sequential so after running the process the Serial1 column will have serial numbers 1-6500,26001-32500,52001-58500 and so on. Serial2 will have serial numbers 6501-13000, 32501-39000, 58501-65000 and so on.
Sorry for the long winded explanation.

Makes things easier, you'll need 4 different append queries for those 8 fields that you have.

Basically the SQL for the first append will look like:

INSERT INTO [Table Name] (Serial1, AlphaAlias1)
SELECT Serial, AlphaAlias
FROM [Original Table]
WHERE ([ID] Mod 4000 Between 1 And 1000);

The next 2 fields will be Mod 4000 Between 1001 And 2000.
Then 2001 And 3000.
And the last will be:

WHERE (([ID] Mod 4000 = 0) Or ([ID] Mod 4000 Between 3001 And 3999))

The Mod operator divides the first number by the second number and returns the remainder.

So 1 - 1000, 4001 - 5000, 8001 - 9000, etc. will return the numbers 1-1000.

[RonVACA]

the first three queries work great. the last one returns records 3000 to 3998 subsequent sets are off by one more record incrementally. I tried the first format and it returned every 4000th record. I also set the Mod parameter to 4001 Between 3001 and 4000. This returns records 3000 to 3999 but the second set starts at 7001 to 7999 the third 8000 then 1102 to 1201 etc. Any idea how to remedy this?

[Rabbit]

the first three queries work great. the last one returns records 3000 to 3998 subsequent sets are off by one more record incrementally. I tried the first format and it returned every 4000th record. I also set the Mod parameter to 4001 Between 3001 and 4000. This returns records 3000 to 3999 but the second set starts at 7001 to 7999 the third 8000 then 1102 to 1201 etc. Any idea how to remedy this?

Hmm, the only reason I see for this to happen is if some of the numbers in the id are being skipped. Check the numbers and see if that's the case.

[RonVACA]

I thougth that might be the case as well but they are all there.

[Rabbit]

Hmm I'm stumped, I just tested this with 20,000 records and it works just fine.

Let's see if someone else can figure out why it's doing this. As far as I can tell, the theory is sound and it worked for the 20,000 records I tested.

[Rabbit]

I also noticed that the records don't get sorted by the ID so perhaps the records are all there but just in a different place? Sort by ID and see if that's the case.

[Denburt]

For me I think the easiest way would be to create a function with a loop and let it run.

Cnt=1
Cnt1=1000
Do until rs.eof
INSERT INTO [Table Name] (Serial1, AlphaAlias1)
SELECT Serial, AlphaAlias
FROM [Original Table]
WHERE ([ID] Between " & Cnt & " And " &  Cnt1);
Cnt = Cnt1 + 1000
Cnt1 = Cnt+cnt1
Loop
 

This code is incomplete and not tested.
It may need some toying with but something like this should do the trick. if you need any help with the code let me know (you didn't state wether you had any VBA abilities).

[RonVACA]

I also noticed that the records don't get sorted by the ID so perhaps the records are all there but just in a different place? Sort by ID and see if that's the case.

The data is sorted on the ID field, and there are no gaps in the incremental listing.

[RonVACA]

For me I think the easiest way would be to create a function with a loop and let it run.

Cnt=1
Cnt1=1000
Do until rs.eof
INSERT INTO [Table Name] (Serial1, AlphaAlias1)
SELECT Serial, AlphaAlias
FROM [Original Table]
WHERE ([ID] Between " & Cnt & " And " &  Cnt1);
Cnt = Cnt1 + 1000
Cnt1 = Cnt+cnt1
Loop
 

This code is incomplete and not tested.
It may need some toying with but something like this should do the trick. if you need any help with the code let me know (you didn't state wether you had any VBA abilities).

This is what i was originally thinking of doing but did not know how to write it. I have very limited VB skills but lets see if I get what to do.
I created the following function:
Public Function ParseData()
Cnt = 1
Cnt1 = 1000
Do Until rs.EOF
SELECT AANAAANA.Serial_Number, AANAAANA.Alph_Alias INTO printdatatest
FROM AANAAANA
WHERE ([ID] Between " & Cnt & " And " & Cnt1)
Cnt = Cnt + 1000
Cnt1 = Cnt + Cnt1
Loop
End Function
and then call the function in a macro in Access. when i do i get a parse error on the select and where statements. Am I missing something.

[Denburt]

This is what i was originally thinking of doing but did not know how to write it. I have very limited VB skills but lets see if I get what to do.
I created the following function:

Public Function ParseData()
Cnt = 1
Cnt1 = 1000
Do Until rs.EOF
SELECT AANAAANA.Serial_Number, AANAAANA.Alph_Alias INTO printdatatest
FROM AANAAANA
WHERE ([ID] Between " & Cnt & " And " & Cnt1)
Cnt = Cnt + 1000
Cnt1 = Cnt + Cnt1
Loop
End Function

and then call the function in a macro in Access. when i do i get a parse error on the select and where statements. Am I missing something.

Your on the right track.
This one is a little better and more complete:

'Use a sub unless you want to return recors to the procedure that calls this one
Public Sub ParseData()
'Declare your variables
Dim Cnt As Long
Dim Cnt1 As Long
Dim DB As Database
Dim rs As Recordset
Set DB = CurrentDb
Dim MaxNumb As Long
Cnt = 1
Cnt1 = 1000
Set rs = DB.OpenRecordset("SELECT Id as MaxofID FROM [Original Table] ")
If Not rs.EOF Then
rs.MoveFirst
MaxNumb = rs!MaxOfID
Do Until Cnt1 >= MaxNumb
DoCmd.RunSQL ("INSERT INTO [Table Name] (Serial1, AlphaAlias1) SELECT Serial, AlphaAlias FROM [Original Table] WHERE ([ID] Between " & Cnt & " And " & Cnt1 & ")")
 
Cnt = Cnt + 1000
Cnt1 = Cnt + Cnt1
Loop
End If
'Close your recorsets or they will continue to consume resources and make your database expand way more than it should.
rs.Close
Set rs = Nothing
Set DB = Nothing
End Sub
 

Does that give you a little more understanding?

[RonVACA]

Your on the right track.
This one is a little better and more complete:

'Use a sub unless you want to return recors to the procedure that calls this one
Public Sub ParseData()
'Declare your variables
Dim Cnt As Long
Dim Cnt1 As Long
Dim DB As Database
Dim rs As Recordset
Set DB = CurrentDb
Dim MaxNumb As Long
Cnt = 1
Cnt1 = 1000
Set rs = DB.OpenRecordset("SELECT Id as MaxofID FROM [Original Table] ")
If Not rs.EOF Then
rs.MoveFirst
MaxNumb = rs!MaxOfID
Do Until Cnt1 >= MaxNumb
DoCmd.RunSQL ("INSERT INTO [Table Name] (Serial1, AlphaAlias1) SELECT Serial, AlphaAlias FROM [Original Table] WHERE ([ID] Between " & Cnt & " And " & Cnt1 & ")")
 
Cnt = Cnt + 1000
Cnt1 = Cnt + Cnt1
Loop
End If
'Close your recorsets or they will continue to consume resources and make your database expand way more than it should.
rs.Close
Set rs = Nothing
Set DB = Nothing
End Sub
 

Does that give you a little more understanding?

how do i call a sub from Access. i see how to call a function.

[Denburt]

You got me there maybe Function would be better for your needs. I never use Macros so.... I didn't realize that there would be such a delima. :)

[RonVACA]

is there a better way to run the sub than to use a macro?

[Denburt]

It really depends on what you are using it for. Most of my commands are run from a button on a form or something to that effect. Like the Main Menu for Example, I have a db that needs the contracts to rollover when they get close to expiring so I placed a button on the main menu so it would be easy for them to open update close out and move on.

If this is just a one time shot then you can always place your code in a standalone module and place your pointer in the VBA sub or function and press F5 and it should run.