Using Microsoft Access with Census Disks
Submitted by: David Hunter
Importing and Using P.E.I.G.S. Census index and Lot Databases into Microsoft Access
At times, I have been asked various questions about using the PEIGS census indexes and individual lot databases which are available on diskette. I will try to answer some of the more common questions below, and give some guidance on how to use them.
- Why use the diskette, when I can order the census in paper format for the same price?
Well, there are many reasons. When you order the data on diskette, not only are the listings contiguous, and instantly accessable, but they can be searched, and search results can be cut and pasted into your genealogical program, word processor, or spread sheet. They can be easily manipulated, put in order of any field within, don’t take up shelp space or collect dust, and they never get dog-eared or lose pages 🙂
- The census indexes are now online, and I can search them online, so why purchase the diskette census indexes?
Well, let me tell you, as a person who uses these indexes on a regular basis, that while the online versions are useful for occasional use, anyone who is using extensive census searches is far better off using the indexes on diskette. I honestly don’t know what I would do without them on disk, and have found they have paid off many times for me….
Ok, so I have a computer, and am sold on the diskette versions. How do I use them – I only have Microsoft Access, and the diskettes are in dBASE (dbf) format?
Importing these databases into MS Access, one of the most popular database programs on the market couldn’t be simpler.
First of all, if using the census indexes on disk, you will find that they are stored in zipped files on the diskette. Generally the lot censuses are not zipped, and may merely be copied over to your hard disk. If zipped, you can run the install.bat file on the disk to install the dbf file on your hard disk, or simply unzip the file using WinZip and install it on a temporary directory on the hard drive. Once converted to MS Access (mdf format) the dbf version you temporarily installed will not be needed again unless you have to reinstall, so the copy of the dbf file on your hard disk can then be deleted to save space. Keep the copy on the install disk as a back up file.
Now, open MS Access. Go to File/New Database, select under general, blank database, name it (e.g. PEI1881), then select create. Now, you have a blank database in which you can import the dbf file…. Now, go to File/Import Data, select the directory you have installed the dbf file to, then select import dBASE III under Files of Type, click on the filename to highlight it, then click on Import.
Once this is done, you will see the imported database as a table in your new database….
Double click on the name of the newly created table, and you will begin to see the benefits of having the database version of the census. Once imported, you can go to Records/Filter/Filter by Form, and search the database for any records you desire.
For an example, suppose you wanted to search the database for any record matching the Mutch Surname. Go to Records/Filter/Filter by Form, then enter Mutch in the surname column. Press your enter key. Now, go to Records/Apply Filter/Sort. All Mutch records in the database will now appear by themselves. Now, suppose you wish to now sort them by Lot, as well. Click on the word Lot. Go to Records/Sort/Sort Ascending. Voila, the selected records are now in order by lot (Of course this example applies only to the census indexes, but the same technique may be used in the Lot censuses, for example, to order the results by Given name, etc.) You can’t do this with a paper based census! Still, if you wish a printed report, of course you can produce one using the print routines in the database.
Hint: If you are searching for a name such as Adam/Adams, you can get MS Access to select both variations, by entering Adam* as the search term. It will then select all names starting with Adam, and any name starting with Adam and ending with anything else. It will show Adam/Adams/Adamson, and any other variation. Of course this is an example only. This technique of using a wild card character before or after the search term can be very helpful.
Additionally, you may browse the database – something you cannot do on the on-line versions. By scrolling down the right hand scroll bars, you can view every name in the database, and by doing this may find information you might have missed completely if you were using the online Census Index searches.
You are beginning to appreciate the advantages of the disk based products. Now, suppose you are writing a family history and wish to produce a listing in your word processor of the selected people. If you go to Tools/Office Links, you may merge the selected data with other Microsoft programs, including MS Word and Excel Spreadsheets. No typing, just a simple click of the mouse. It couldn’t be simpler.
From here, you will continue to find new ways to process and use the data, and will truly become a database guru – you will be very glad to have these records on disk, and they can be accessed in a few seconds without having to be connected to the Internet, and without the eyestrain of searching through endless books of records.
Note: If you have a number of the 1881, 1891, or 1901 census lot database disks, you can also import these into the same database where you keep your census indexes.. Just open your Census file in MS Access, but don’t open the table, then go to File/Import Data, and import each individual dbf file into your Access database one at a time. They will show as separate tables within the one database. Then when you open the database, you will see one called PEI1881 (the index) for example, then also Lot 1, Lot 2 , Lot 3 etc. depending upon which lots you have purchased and installed. To view any of them simply double click on whichever table you wish to view after opening the database.
I would recommend that if you have portions of different censuses, i.e. 1881, 1891, and 1901, that you create a separate database for each. Each file could theoretically hold the index, and all the individual lot censuses for that census, depending, of course upon which lots you have purchased for each census.
Use with other database programs is quite similar – however, as MS Access is one of the most popular, and the one I am most familiar with, it is the one I am using as an example. The main thing I hope to accomplish with this little tutorial is to let you see some of the advantages of ordering both census indexes and individual Lot Censuses on diskette. Once you have done so, and used them for a while you will be very glad you decided to go that route.