Random selection sample from phonebook-database and export to adresslabels in word

Hi DB Experts! I'm a total newbie to databases and I could really use some help on this one...

A small department of our university frequently needs random samples from the general population for research purposes. Until now, this is done by manually select and export random addresses from the national phonebook cd-rom to address labels in ms-word. I've decided to try to automate this time-consuming work. For this, I've exported the national phonebook to a Mysql database (v.4.1). So now I have about 6M names and addresses (and of course, phonenumbers!) in a database. I’ve installed the Mysql ODBC driver and have ms access 2003. Only problem is, I don’t have any experience with access…  
Here's what I'm trying to do: design a nice interface to get a (proportional) random sample from selected cities with a given sample size, and next export/convert this sample to address labels.

Required functionality: 1) selection of multiple cities 2) set sample size 3) option to make sample-parts proportional to city size 4) export to / make address labels (ms-word?)

I would appreciate any help to get me started on this. I don’t need a ‘cut and paste here’ solution (would be accepted though ;-), I’m willing to learn and experiment. So my question is: Where do I begin? How to get from this database to a working solution? Just the basic steps of the whole process… (I will post new questions if I can’t get a part this project to work)


Blue Rishi
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
With that many records you would probably need to filter what you can (city etc.) with a select query and then pick the random selection from this.

I had to deal with something similar recently and found out that the common method spread around the web doesn't work.
The problem is that calling Randomize outside the query (by an external function) takes place in a different scope than that of the query, thus Rnd(..) is run in the query with the same seed initially which, of course, will return the same sample or sequence of samples.

The solution is to generate the random number in the external function:

Public Function RandomNumber( _
  Optional ByVal booRandomize As Boolean) _
  As Single

  Static booRandomized  As Boolean
  If booRandomize = True Or booRandomized = False Then
    ' A new seed is requested or this is the first run.
    booRandomized = True
  End If
  ' Generate and return a random number.
  RandomNumber = Rnd()

End Function

Now the query would look something like this:

    tblPick.Picked = False
    RandomNumber([ID] Is Null);

where n is the count of records in the requested sample, and ID is a field that is never Null like the primary key.
The use of ID in the parameter is needed to call RandomNumber not once but for every record.
Picked is a field to mark already selected samples if you need that.

I think this is the main part. I have no links on "how to get familiar with Access in 10 hours" (I have worked with Access since version 1.0) but I'm sure others can chime in.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Blue_RishiAuthor Commented:
Thnx for your help gustav! Can't say I totally understand the code (yet), but I understand what you're doing...didn't even think about marking the picked records (very basic statistics). I'll get some books on VB and Access 2k from my local library tomorrow.

Gustav BrockCIOCommented:
OK, good luck!

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Blue_RishiAuthor Commented:
Ok, I've red several 'get familiar with access' books. Most of em just cover the basics from planning a database to making simple forms for retrieving and/or editing records.  However, I've found some usefull info in them. The VBA essentials for Access 2k was more usefull, but still didn't give me all the info I needed. Seems like I'm going to have to learn VB(A) for this project.
From what I understand now, I'll have to make/edit the queries in vba too, and run/modify it for each selected city. The thing that complicates things is the fact that the subsamples from each city have to be proportional to city size...(size selected place 1 /  total size of all selected places) * sample size ...
What I have working so far:
- query to count city size
- query to count size of all cities
- form with list of places (from seperate table), I'm still working on how to get the selected place(s) to a second list 'selected' (through buttons, for select and deselect).

What I still need to get to work:
- the second list (move items from first list and back with buttons)
- modify / create the queries above to calculate the proportions (if a 'proportional option is selected in form)
- create queries for each selected place with SELECT TOP n, where n is the calculated proportion for each selected place
- export function to excel (or, SPSS format if possible)
- create adress labels

I think I'll need to find more/better VBA book(s)...

I'll give you the points anyway for your help with the randomize function Gustav,  but feedback / suggestions on the other stuff would be greatly appreciated.

Gustav BrockCIOCommented:
Seems like you are on the right track.
Have in mind, that:


is absolute but can be relative as well:


so, to retrieve 15% of the records:


However, n can not be an expression - SELECT TOP 15/100 won't do.
Thus, if you need to run the query with different values of n, you'll need to either need multiple queries, build the complete query in VBA and run it, or retrieve the SQL from the query:

  Set qdy =  CurrentDb.QueryDefs("YourQuery")
  strSQL = qdy.SQL

adjust strSQL, and write it back:

  qdy.SQL = strSQL

Export to Excel is easy - numerous examples are available in the on-line help, here or around the web.
How to export to SPSS I don't know, but again, look around here or search Google.

Blue_RishiAuthor Commented:
Thnx again, I will post a new question if I run into problem with this project...

Gustav BrockCIOCommented:
You are welcome. Glad I could help.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.