Need to pull out every third record

  • Thread starter Thread starter Brian Langley
  • Start date Start date
B

Brian Langley

I hate posting what basically amounts to a, "Do this for me" request, but
I'm a little jammed up at the moment and the last 300 posts don't seem to
address this (if I'm wrong, direct me and I'll try it myself first) and I
have no SQL knowledge which I think is what kind of expression I need.

Basically, I mail merged ~3,300 records for some mailing labels.
Wonderfully, Word somehow got an Avery template margin wrong and nicked off
the first column's first few characters. Which we didn't notice till after
the printing was complete and we wanted to begin using them.

So- I want to go back and select the first and every third record thereafter
and reprint only those labels we need (mostly because I know it's possible
and don't want to waste another box of labels reprinting the whole job,
secondly because I don't have the labels to waste :p).

Any help/guidance is greatly appreciated!

Cheers
 
Dump your data into a new table that has an AutoNumber field (ID) in
it. Now make a query that queries your address data and in another
column type ID MOD 3. In the criteria of that column, put =1. This
should give you records 1,4,7,10,13, etc.
Hope that helps!
 
Actually...I may have just made this impossible...I might have to redo the
merge and cut/paste the first column 100+ times :P

I found this function on the MVP website:

Function PlusOne (var As Variant)
Static i As Integer
i = i + 1
PlusOne = i
End Function

To be used with these column properties:

Field Expr1: PlusOne([MyField]) Mod 5
Show No
Criteria 0

But the original merge was sorted by Zip then Name so my first record for
these purposes is #1575, not 1...Light bulb- I have my query sorted now, I
can make a table off of that and try it until it works.

My question was going to be how to make the function start with the first
record, but probability tells me it'll work within 3 tries :P
 
Awesome- disregard that other post...I held it back earlier and it jsut sent
before I could catch it :P

That worked perfectly!
 
Hi Brian,

If the table has a primary key, you can use a subquery to generate a
cyclic number and select every third record. Here's an example that
picks the third, sixth, ninth etc. customers from the Northwind
Customers table.

SELECT A.*
FROM Customers AS A
WHERE ((SELECT COUNT(CustomerID) FROM [Customers] AS C
WHERE C.CustomerID < A.CustomerID) Mod 3 = 2)
ORDER BY A.CustomerID;
 
Back
Top