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.