[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2917
  • Last Modified:

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)

Thnx,

Blue Rishi
0
Blue_Rishi
Asked:
Blue_Rishi
  • 4
  • 3
1 Solution
 
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.
    Randomize
    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
    *
  FROM
    tblPick
  WHERE
    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.

/gustav
0
 
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.

Dimitri
0
 
Gustav BrockCIOCommented:
OK, good luck!

/gustav
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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.

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

  SELECT TOP n

is absolute but can be relative as well:

  SELECT TOP n PERCENT

so, to retrieve 15% of the records:

  SELECT TOP 15 PERCENT

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.

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

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

/gustav
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now