Microsoft Access · Organising your research

Getting organised with Microsoft Access – an update

Back in February I wrote the post Getting Organised with Microsoft Access, where I shared how I set up a database to keep track of the documents I had, and to see what records I still needed to find, as I wanted to download as many records as I could before my subscription to Ancestry expired.

My database worked reasonably well for what I wanted it to do, but I found a few problems with it.  I found that I was better off leaving my file list in my Excel spreadsheet, and then deleting the entries in the spreadsheet as I set up the document in my database, because the way I had originally set up the database meant I had to enter the new file names into the Files table before I could add the document to the documents table.

I also found when I was working on the electoral rolls and directories that sometimes there were more than one entry per year.  I therefore added fields to my documents table. For the Electoral rolls I added the state, division, subdivision and page.  I can now tell which entry I have already done, and which one I still need to do.  For the directories, I have the fields: directory section and page (I use the page field for both the electoral rolls and directories).  The directories have 3 sections: alphabetical, locality and occupation.  Now I can see which directory(ies) I’ve already done.

This is my modified documents/index form

Documents index form

As you can see, I’ve also add some colours to the form. I also have the sourcetype field.  I have three source types: Official, Family and Indexes.  The official documents are documents that were created at or close to the time of the event – birth, death and marriage certificates, baptism records, censuses, electoral rolls, directories, immigration records etc.  Family documents include letters, emails, family trees, etc. And the indexes type is for transcriptions, and indexes, which I use until I can access the original document.

I also found working directly in the query view wasn’t the best way to see all the records for the person, so I created some forms to make this process easier.  I have an index of all my “primary” people – a “primary” person is who the document was mainly for – for a birth certificate, the child, for a death certificate, the deceased, for a marriage certificate, the bride and groom etc.

I also don’t index everyone listed on a document, I only index the people that the document provides some information for, i.e. an age, that the person was deceased, their residence, or occupation.’

I then created a navigation form, so that I can switch backwards and forwards between forms.

Navigation form.PNG

Clicking on the Primary Individual Index will bring up the Check for missing documents form

check for missing documents

I use this to determine which records I still need to find.

I then have an index of all the individuals I have added to the database.

individual index

Which is then linked to the Document index, which brings up a list of all the documents I have for the person, in file name order.

document index

I have tried over the last couple of months to see about adding additional fields, so that I could record the basic information provided in the documents: at document level, the event, event date, and place, and at the individual level, the relationship to the primary person, age, birthplace, occupation and residence, and comments.  I decided to leave them off, because I have 27 years of research to index.  It will take long enough just to add the documents I already have to the database, do extra research for each person, and then add that research to the database.

I created a second database, because it is easier to alternate between databases, than to exit the Check for missing documents form, and go into my Missing Electoral rolls form, to see which years aren’t available online, and then go back into the Check for missing documents form.

missing rolls form

I also added forms to this database, to act as a research log/to-do list.  I have a navigation form for this database as well.

navigation form 2

I have the entry form, where I add the person and comments.

Then there is the individuals index, which is a list of all the people in my to-do list

I’m not sure that I actually need both the entry form and the individuals with comments form, but clicking on a person on the individuals index will bring up the to-do list for that person.

To-do list

I may modify these forms, so that I can have a list of to-do items for each repository as well, although I may end up adding these to the to-do section in Legacy Family Tree.

My next project is to set up a checklist, as it is hard to remember all the collections and websites that might have information for a person.

Would you be interested in having a database to help you organize your genealogy research?  Database set up is one of the services I provide as a virtual assistant.  More details about the services I offer can be found on my business website