RootsChat.Com
General => Technical Help => Topic started by: Doughty on Tuesday 27 February 07 10:44 GMT (UK)
-
I spend alot of time trawlling the bmd records for individuals and often come across other relatives that i have already searched and found. So to save constantly searching the same records over and over I want to transcribe the bmd for my surname interests in a full, searchable database. Once i'm done i'd like to be able to share the info with others so whats the best format to use and which software, i have microsoft access and excel but i've not used access before and it looks complicated.
-
Excel although primarily a spreadsheet for working with figures can also be used as a database and setting it up is relatively easy.
Access is a database and of the two programs is probably far more suited to what you want to do.
A tutorial for access can be found here
http://cisnet.baruch.cuny.edu/holowczak/classes/2200/access/accessall.html
or
http://www.fgcu.edu/support/office2000/access/
http://www.courses.psu.edu/infsy/infsy540_gjy1/access_tutorial_2000.html
For a database in excel look at
PC Answers Magazine Tutorial (http://www.pcanswers.co.uk/tutorials/default.asp?pagetypeid=2&articleid=7542&subsectionid=782&subsubsectionid=0)
-
I've got a genealogy cabfile for Microsoft Access that I found a few years ago on their website (01076524.cab (741k)) but can't find it there now.
It's not very sophisticated but it's a good starting point.
pm me if you want a copy.
Peter
-
Hi Doughty
If you feel comfortable about using Excel, then bear in mind that you can import worksheets into Access. Each datafield has to be unique in Access so if you want to have a combined database for births, marriages and deaths you have to define some datafilelds in certain ways, so the design is important and has to reflect the way the registers have changed. As you are aware the registers changed certain columns:
1860 death registered index incorporated: age.
1911 3rd quarter: marriage register index incorporated: spouses surname.
1911 3rd quarter: birth register index incorporated: mother's maiden surname.
So I combine the spouse surname and the mother's maiden surname into one column rather than have two separate columns.
1969 death register index incorporated the DOB of the deceased. In my table design I have separate columns for dd mm yyyy (day, month and year).
1984 stopped using quarters and had one register for the whole year and used month+year of the event instead.
So sit and work out of paper all the datafields you are going to need for a combined database that reflects the changes the registers have undertaken from inception in 1837 to the current ones.
downside
-
Hi Doughty,
I also use excel to keep track of people and certificates
you'll find some ideas on using excel here:
Topic: Spreadsheets help track Surnames
http://www.rootschat.com/forum/index.php/topic,31779.0.html
Bob
-
There's an example spreadsheet here http://www.rootschat.com/forum/index.php/topic,199033.msg998017.html#msg998017, blush, blush. ;)
JULIAN
-
ps. for comparison and more ideas, I've added an example to the thread that Julian mentions.
I find that one column per "event" makes more lines, but you can add more details about each event.
For instance, sometimes it's interesting to look at the ages given on various census or marriage certs or baptisms etc
Or the addresses at different times,
or ... etc.
-
You mentioned BMD specifically, so I assumed you were talking about the GRO index? The main point about Access is that you have a unique identifier. This can be the same ID that is used in a GEDCOM file or that is used in your genealogy programe e.g. I123 = Individual 123. In my one name Access database for BMD records I use these fields:
Year
Quarter
Name (or as it is sometimes called, given name - first and or middlename)
Spouse or Mother's Maiden Name
Event (B or M or D)
Age (death)
YYYY (death)
MM (death)
DD (death)
ID
Reg Dist
Volume
Page No
Remarks
The fields above are in a table called Main. I don't have a surname field because this is a one name study.
The object of a database is to avoid using the repetition of data. So the Reg Dist is a lookup field. I have created a separate table called Registration Districts. So when I am entering data in Main and I get to the Reg Dist field a 2 column combo box list appears and I select the Reg Dist from the drop down list.
The table Registration Districts contains a number (autonumber) and the actual district. The number is inserted into the Main.Reg Dist datafield.
The thing about Access is that you can create lots of different tables and join them together in a query providing that there is a unique ID.
For instance you could create a Baptisms, Weddings and Burials table providing each record contains a lookup field such as ID. The same is true of census records, again you link the tables by the ID field.
downside
-
Thank you for all the ideas. I've looked at the access tutorial link and I think i'm going to struggle to understand all the features, I can see it would be the better choice but think i'd need to go back to college before i could use it. :-\
Which leaves me with excel. I've been having a mess around with it and think i can make it do all that i need but i'm going to contact freebmd first and ask what format would be best for them, as then i can upload the details there making them available to everyone searching the same name. (plus it would be a back up for me)
-
Hi Doughty
If you are using freeBMD then you can save your search as a text file:
Enter the search criteria.
After it displays the results click on the Download button, by default it save the file as search.txt.
It then asks if you want to open search.txt.
Basically it is a column separated values file (csv although csv can mean comma separated values). Remove all the non-data rows at the top and bottom of the file but leave the column headers:
Event Quarter Year Surname Given Name Age at Death District Flag Volume Page
Then save the file.
If you open Access and select blank database and save it as freeBMD.mdb
Then click on File and Import from External File. There is a drop downlist of file types, so select the .txt or text files - it should see the search.txt file.
You are then taken through a series of import data screens.
Select the default Separated by tabs.
On the next screen check the box Has Column Headers.
On the next screen it asks you whether the fields are text or numbers. Make sure you change the Volume and Page Number fields from Long Integer to Text otherwise it will produce import errors. Basically some of this data has underscores and letters in it so it is not always numeric data.
That's it - fairly simple really. You will have a worksheet style database. You can append more data to this file if you have several different searches to complete.
downside