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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OK, good luck!
/gustav
/gustav
ASKER
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
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
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("YourQ
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
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("YourQ
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
ASKER
Thnx again, I will post a new question if I run into problem with this project...
Dimitri
Dimitri
You are welcome. Glad I could help.
/gustav
/gustav
ASKER
Dimitri