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

Posted on 2004-11-21
Last Modified: 2012-06-27
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
Question by:Blue_Rishi
    LVL 48

    Accepted Solution

    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:

      SELECT TOP n
        tblPick.Picked = False
      ORDER BY
        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.

    LVL 5

    Author Comment

    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.

    LVL 48

    Expert Comment

    by:Gustav Brock
    OK, good luck!

    LVL 5

    Author Comment

    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.

    LVL 48

    Expert Comment

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

      SELECT TOP n

    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.

    LVL 5

    Author Comment

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

    LVL 48

    Expert Comment

    by:Gustav Brock
    You are welcome. Glad I could help.


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now