Organising your research

Getting organised with Microsoft Access

For many years now I have been trying to work out a system of organising all my family history research.  My subscription to Ancestry is due for renewal at the end of March, and I’m not sure whether I will be able to afford to renew it.  I therefore want to find as many records as I can for my family before then. What I needed to do was to determine which documents I already have, and which records I still need.

My early research was from Victoria, Australia, and because it was easier that way, I would research a number of families at the same time.  This meant that I couldn’t organise my research by surname or location, so I just left the research in the order in which I obtained it.  When I later scanned the original documents, and saved downloaded images, I therefore used the date I obtained the file, and a description of the document as the file name, i.e. 1993-01-15 Harry Docwra birth.

These files are in a folder on my computer called “Original images”, in a main folder “Family history research”.  Since the files are organised in date order, I have a subfolder for each year. I have an index of these files in an Excel spreadsheet, which helps me to find the document I want.  I generated this index by using the instructions in the article How to copy a list of files in a Windows Folders into an Excel list.

Although I have an index of the files, because the files are organised by date, it makes it hard to work out which documents I have for each person, so I needed to set up an index that linked each person to each document they appear in.

I first tried using Clooz, but I wanted to be able to sort all the events by date, no matter what type of record it is, as that would make it easier to determine which records I am missing, and Clooz organises by event, and then date, which wasn’t really what I wanted.

I then tried Microsoft Excel, but found I was losing track of where I was up to all the time, and it doesn’t really allow you to cross reference the files with the individuals.

I finally was able to do what I wanted using Microsoft Access.  First of all, I created a CSV file of my name list in Legacy Family Tree.


After a few attempts, I found it easier to distinguish between individuals with the same name in the Access database if the date of birth was listed in the same column as the surname and given name, so I used the concatenate function in Excel to combine these two columns.


After setting up the formula for the first person in the list, it was then just a matter of copying the formula for everyone else in the list.

I then used copy, paste values into the Surname, name column, and deleted the born column, and the column with the concatenate function.


I then used Save As to save the file as an Excel workbook.

The next step was to set up the Access Database.  I set up a blank database, and then used External Data > Excel, and then followed the wizard to import my spreadsheet as a new table.


I then repeated the process to set up a table for my file list


Since one image file might contain two “documents” – for example, the banns record for my 3x great grandfather also has the banns record for his brother, I then create a new table, documents, where I linked the document to the file.


I used the file name field to look up the file name from the files table.  I used Short Text for the Document date because sometimes I would only have a year (i.e. for Electoral rolls), so I couldn’t use the Date data type.  Although the file name would generally indicate what type of document it was, I added this column so that I could easily determine what type of event the document was for.  To make data entry easier, I set up a table for all the Document types, and set up this field to look up the Document types table.


I used the Not yet linked entries field, so that I could refer back to these in case at a later date I find a connection to my tree.  Over the years, I have ended up with double ups of documents, as well as there are documents I need to replace because I didn’t keep track of where I found the information.  The discard and discard reason fields are to show that I have put these documents aside, and haven’t indexed them.  I have the Added to index field to keep track of the documents I have indexed.  Since, particularly with downloaded images, I might have multiple images for the same file (i.e. a will or military record), I would index the whole file under the first image, so I would write this in the Reason not added to index field.

The next thing I had to do was to set up a table for the index that links the documents to the individuals.


The document files looks up the document name from the Documents table.  The individual looks up the individual from the complete individuals table.  In the relation field, I’ll indicate if the individual is the primary individual in the document, or another individual. For example, Harry Docwra’s birth certificate would have Harry as the Primary individual, and his father, mother and siblings as Other.  I decided to just use Other, rather than create a table for all the possible relationships.  The last two columns, Ancestor group and Generations, are for my own personal use.  I intend to use this index to check my research, and then set up a new family tree, and when I set up my new family tree, I’ll start by entering my ancestors, then their children, and then all their descendants, so the ancestor groups are number 1 for my ex-husband and I, 2 for my parents, 3 for his, and so on.  In the generations column, I have Ancestor for an ancestor, and any other spouses they may have had, Child for the child of an ancestor and their spouse(s), Descendant for all the other descendants, and Linked for anyone not in my direct line, but I have been able to link to my family.  Since I plan to enter all the information directly into Legacy Family Tree, or into Clooz and then transfer into Legacy, I have only set up the database to be a basic index.

The next thing to do was to set up the Form (and subform) to make setting up the index easier.


First, I selected all the fields from the Documents table, except the ID, and then all the fields from the Index table, except the Document, and ID.  I then went into Design View to modify the form to make data entry easier.

I then added a field to the individuals table to index whether I had finished checking for all the possible records on Ancestry.


And finally, I set up a Query. To generate the query, I used Query Design, and selected the Documents, Index and complete individuals tables, and then chose file name, document, document date and document type from the Documents table, Individual, relation, ancestor group and generations from the Index table, and Checked field from the complete individuals table.


The query allows me to filter the results to a particular individual, and I can then sort the results by Document date to see what documents I might be missing, or to filter out the people I’ve already checked so I know who to check next.

My process is to first copy all the files into a folder “Genealogy Do-over”.  I then search my Files list for any files I already have for a person.  For example, the first document was the obituary for Thomas Smith, so I’d then check for any other documents I have for him.

To do this, I open the Files table, and highlight the File name column, and then select Find from the Ribbon, and then Find What: Thomas Smith, Match Any Part of Field.


Since I want to keep my original file organisation intact, I copied all the files into a new folder “Genealogy Do-over”, and I would then go through and pull out all the files for a person from the yearly folder they’re in, and put them into the main “Genealogy D0-over” folder.  I then go through each file and enter the details into my Documents form.


Once I enter all the information from all the documents for the person, I then go to the Index query, filter the query to records just for that person, and then sort the query by Document date.


This helps me to see which records I still need.

The collection at Ancestry that I expect to use the most is the Australian Electoral rolls collection.  Although Ancestry has a lot of the Electoral rolls, in their collection, they don’t have all of them, so I also have extra tables in my database, to keep track of the “missing” electoral rolls, so I know which ones I don’t need to look for.

One table is the index of the missing rolls, and I have 3 other tables: one table for the States, another for the districts and the third for the subdistricts.



1 thought on “Getting organised with Microsoft Access”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s