Labels to print every 5th customer 1

[natesin]

I need a little help. I am certain a query is needed here. I have a list of cutomers. I need to print sheets of labels, but only every 5th customer.

Any help greatly appreciated!

Nate

 

[scriverb]

This requires a little VBA code prior to printing the labels job. Create a new field in your table called Select and make it a Yes/No field type. We will use this to flag your records.

Now run this code prior to running your labels print job:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = CurrentDB
MyDB.Execute("UPDATE tblYourTable SET tblYourTable.Select = False WHERE (((tblYourTable.Select)= True));&quot
Set rs = db.OpenRecordset("tblYourTable", dbOpenDynaset)
rs.movefirst
do
For i = 1 to 4
rs.movenext
loop
rs.Edit
rs("Select&quot = true
rs.Update
rs.movenext
loop until rs.eof
rs.close
db.close

Now run your report with a query as the RecordSource for the print job that is selects records based upon the Select field equaling True. You can create that query. Just make sure you select all of the fields that you need for your labels print.

Let me know how this works out for you. Remember to change tblYourTable to the name of your table through the code.

 

[cmmrfrds]

You can use a query such as below. It is using the Customer table in the Northwind mdb. Paste this into a query in the Northwind database to test out and modify as you need.

SELECT c1.CustomerID AS ID, c1.ContactName AS Name, c1.Address AS Address, c1.City AS City, c1.PostalCode AS Zip
FROM dbo_Customers AS C1, dbo_Customers AS C2
WHERE (((c1.CustomerID)>=c2.customerid) )
GROUP BY c1.customerid, c1.contactname, c1.address, c1.city, c1.postalcode
having count(*) mod 5 = 0

 

[scriverb]

cmmrfrds: Great idea!!! Missed that one completely. Really like the c1.customerID >= c2.customerID idea along with Grouping and Count(*) to generate a sequential list of numbers from 1 to number of records in the table to perform the Mod function on.

Great idea. Thanks. Will store that one up for the future.

Have a star.
Bob Scriver

 

[scriverb]

cmmrfrds: I have run and tested cmmrfrds's query on a very large database and found that it takes a considerable amount of time because it creates multiple records for each each record prior to grouping and this a huge number of records being created in the process. The Where statement:

WHERE (((c1.CustomerID)>=c2.customerid) )

actually is creating multiple rows for each record depending upon which record in the database the customerID is.
Example: 1000 records
Record 1 1 record
Record 2 2 records
Record 3 3 records
etc.
Record 1000 1000 records etc.

Well, when you have 100,000 records this query creates an enormous number of rows prior to grouping on the fields. Actually my system was still processing and froze up on a table that had just 13,000 records.

Unless I am missing something here this technique should not be used on very large data files.

An alternative would be to use the AutoIncrement field to perform the Mod 5 = 0 process and the query is much faster. Then there is no need to artifically create the unique number sequencer that you did. Please get back to me on this and give me your thoughts on my observations and thoughts.

Thanks Bob Scriver