TechMan
Apr 20 2005, 01:00 PM
I want to periodically produce a list of customers from my database (Name, Street, City, State, Zip) and have access randomly select a fixed number of the entire pool. Let's say I want a list of customer that have purchsed from me in the past 6 months. I run a query and produce that list. Then I want to select (at random) say 20% of these customers and send them a survey letter. That is the step I don't know how to do. Can that be done ?
Secondly, I'm guessing that I will move the final list into an "address book" format in eith word or excel and print my mailing labels from that list. I've never doen this before. Can this be done ? First, the random selction, and two, the address lable printouts from Word ?
fkegley
Apr 20 2005, 01:17 PM
I would do this with code.
Develop your query as described above (the one that fetches last 6 months). Put one extra field in to hold the flag whether or not to send survey letter. Something like SendLetter:False.
Then via code open the recordset, determine how many records you want to produce, and determine the number of records in the recordset. Then using a loop, for the required number of letters, generate a random integer between 1 and the number of records. This will be the record to move to in the recordset. Set its SendLetter to True.
The Access Help files on the RND function will be valuable here.
I would use Word's Mail Merge and bind the document directly to this query.
fkegley
Apr 20 2005, 01:26 PM
This isn't what I meant to say:
I would use Word's Mail Merge and bind the document directly to this query.
TechMan
Apr 21 2005, 07:30 AM
Do you have a SAMPLE of the loop that will produce the random dataset? Conceptually, I am still a little unclear how this would work. Are there any RANDOM SAMPLE examples out there ? I have to believe this has come up before.
fkegley
Apr 21 2005, 10:20 AM
Yes, see the attached. To Table3 is the table that has the Send To records in it. The code is in Module3. To duplicate what I did, delete the RECORDS ONLY from Table3, open Module3, click inside the RandomSelection procedure and click the blue triangle on the toolbar--the Run button. It will only take a few seconds. When it is finished, close the module and open Table3. I used the CustomerID field of the Orders table of Northwind as my sample data. That might not have been a good choice as there are duplicate CustomerIDs in the table.
Adjust the code as needed.
NoahP
Apr 21 2005, 10:38 AM
Much easier way to do this using the TOP method of a query. See attached.
HTH
Noah
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please
click here.