Surrey Recruitment Registers

[Chris_B]

You may be aware that at long last the “Surrey Recruitment Registers” CD-Rom has become available. See here:

If you are considering buying this, it is a bargain at £16 (in p&p). I would say it’s an ESSENTIAL purchase for anyone interested in their family’s history, researching names on a War Memorial, is a medal collector, follows a given regiment or has an interest in social history.

I understand the data it contains is perhaps unique within the UK as a record of recruitment during the Great War. The Surrey History Centre, and its volunteers, is to be congratulated on the mammoth task of documenting the 84,907 entries it contains.

Unlike the SDGW and other similar resources, the SHC/Trust was made the raw data available on the disc. It is in the form of a DBF file (created by dBase software) which can be read by a variety of other database software including MS ACCESS.

Of course, you will need both the software and the skills to make full use of this. The SHC/Trust makes no apology for the first, after all most folk with MS Office will have MS ACCESS. If not, the “Open Office version 2” suite, now includes an ACCESS like program and is virtually free, you just pay the cost of CD’s , typically a few pounds.

Having the skills is a learning opportunity, I wouldn’t class myself as a beginner, but my database skills are very very rusty, and I have forgotten most things I ever knew.

I should preface the following contents by saying I am lucky enough to have Windows XP pro and MS Office 2003 on my PC, but I am certain the same scenarios will apply to other versions of this software.

MS ACCESS opens and reads the DBF file on the SHC/Trust disk without a problem. But whether by design or accident the data in ALL the fields of each database row is stored as text. This of course preserves the form of the original information with the entries made in the exact order they appear in the 44 Volumes of the Registers. The file/table has no primary index as such.

But it is easy to see this presents a number of difficulties and limitations which potentially severely limits the usefulness of the data.

Let’s take one example, that of the attestation date (the ATTESTDATE field). The dates are stored as text with a two year digit date (dd/mm/yy) in the vast majority of cases, with the exception of 7 records which have a four digit year date (dd/mm/yyyy). Additionally there are 15 records where the full date was unknown and has been entered with a question mark, for example “1?/08/17” or “14/??/17”. There are appear to be only four records where there is some kind of typo in the date, e.g. “22/O2/16” or “02/07.15”, but again these may be exactly as they appear in the register. This is a testimony to the skill and diligence of those who entered the data. There are also some interesting entries with dates like “29/2/17” or “31/11/17” to consider – people who attested on dates which did not exist on the calendar.

Storing dates in this way might preserve the original form of the data, but it doesn’t allow any date analysis to be done. You cannot use any of the powerful in-built date functions of your database software, you cannot even sort or select on date, unless you try to use some sort of convoluted text manipulation. I wouldn’t waste my time trying.

But what is worse is that the dates have only a two digit year. If you try to make an automatic change from a date stored as text to a date stored as a date, the poor old PC is so stupid it cannot tell which century you are talking about! The years mostly turn into 2015, 2016 etc., while the others after 1929 turn into twentieth cnetury dates, correct as it happens.

Entries that relate to a man’s vital statistics were of course recorded in Imperial measurements (Ah, the good old days when people could do a bit of arithmetic, and new something other than counting in tens ). You are not going to have much chance of finding answers to those favourite questions like tallest, shortest, thinnest, fattest, average height by age etc. etc by using the software’s in-built maths functions (MAX( ), MIN (), AVG( ) etc.) because they only work on numbers.

Thankfully software like MS ACCESS provides the tools to fix these problems.

Again, let’s take the example of the ATTESTDATE, to my mind the one to start with.

This is an outline of my proposed solution:

1. Do not attempt to change the data stored in the ATTESTDATE field as you will loose the original form of this data.

2. Create a new field in the data table to store what I will call “FIXEDDATE”.

3. Sift out all dates with formats that would be considered as invalid for automatic conversion. Invalid formats include those where there is no attestation date (it is NULL), have a typo, or have a question mark.

4. Of those that are left (the vast majority) sift out the very few that already have a four digit date. On the remaining dates, apply automatic text manipulation to convert them to a four digit year data and then convert them to be stored as proper dates.

So what, you might say, that’s obvious, which is true. But perhaps how to do it in MS- Access is not so obvious. Here’s an outline

1. Copy the REGISTER.DBF file to your hard drive.

2. Build a select query that effectively adds a new column to your table and uses an “expression” to create its contents. Something like:

DateValue((IIf(IsNull([ATTESTDATE]),Null,IIf([ATTESTDATE] Like "*[?]*",Null,Left$([ATTESTDATE],Len([ATTESTDATE])-2) & "19" & Right$([ATTESTDATE],2))))

3. Test it works as you expect.

4. Turn the select query into a “make table query”, run it and the jobs done.

By the way, my expression only works because I manually amended the few records where there was a typo in ATTESTDATE, or it already had a four digit year (back to two). Of course I noted which these where and reverted back to the original state after I had finished. How did I find these records? I built select queries with pattern matching in the select criteria –

Like “*[?]*” - for records where the date has a question mark in it.

Like “*/[0-9][0-9][0-9][0-9]” - for records ending in a four digit year.

Like “*[!0-9/?]*” - for typos.

After creating a new version of the REGISTER.DBF you can select those records where the ATTESTDATE is not Null and the FIXEDDATE is Null to look for those strange dates like “29/02/1917” ….

As there’s always more than one way of skinning a cat, others may have a better solution to this. But I hope this might help those with the data and who are not sure how to sort out the dates.

The problem of vital stats in Imperial measurements is a bit more complicated. I’ll leave that to another post…

Chris.

[borstal]

Chris

I read your post on using the raw database with interest.

Can I as the compositor of the CD answer some of the matters you raised

Firstly my apologies for the few dates that slipped through the checking we used, my fault for not spotting them after the Inputters & Checkers had been through them first

Secondly, the project originally started life as a Family History type project with the emphasis on producing a name list. Under these circumstances it was possible to leave every field as a character field as the intention was to produce a report and convert this with Acrobat.

The decision to include the raw database came about after people had asked for the data to be presented in so many ways, that to include all the query results would have needed a DVD rather than a CD.

In addition to the above the use of character fields allowed the use of fractions as per the original registers rather than converting to decimals.

Once again my apologies for the errors

[Chris_B]

Chris

I read your post on using the raw database with interest.

Can I as the compositor of the CD answer some of the matters you raised

Firstly my apologies for the few dates that slipped through the checking we used, my fault for not spotting them after the Inputters & Checkers had been through them first

Secondly, the project originally started life as a Family History type project with the emphasis on producing a name list. Under these circumstances it was possible to leave every field as a character field as the intention was to produce a report and convert this with Acrobat.

The decision to include the raw database came about after people had asked for the data to be presented in so many ways, that to include all the query results would have needed a DVD rather than a CD.

In addition to the above the use of character fields allowed the use of fractions as per the original registers rather than converting to decimals.

Once again my apologies for the errors

No apology is necessary, nor did I intend any criticism of the work done on the project. If you ask me just a few typos in the attestation date amongst nearly 85,000 records is exceptionally good. They are easy to spot anyway in SQL . What is more intriguing are entries for dates which never existed.

I can fully understand the conflicting requirements from different interested parties and, as you say, it would have been impossible to include all sorts of queries, and to mimic the functionality of something like SDGW with a professionally written database front-end would have pushed the cost sky-high.

I'm impressed with the solution using PDF documents and having the raw data is a great great bonus. I would suggest you had no option but to present the data in the REGISTER.DBF file in the way you have in order to preserve the nature of the data in the register, but the decision does have consequences.

Those who want to query the data in a variety of ways will have a little bit of work to do.

I’m hoping that this forum will provided a means for interested folk to exchange ideas about this.

[borstal]

Chris

I did not take your comments as criticism; I have been at the transcription game for so long now that I recognise that the odd error does get through.

Regarding the non existent dates I suspect b****y minded clerks who didn't like doing the job as one possible reason, the other could be a misreading of the original entries, the writing varied from reasonable to diabolical.

I don't have the original sheets in front of me but whilst some sheets had about 25 entries per page (see the images on the disk, those covering the Guildford volume had 40-50 entries per page and were a real pain.

I look forward to following the discussion on these pages as an interested observer, at least until the next SHC project starts, which is not War related

[Chris_B]

Chris

I did not take your comments as criticism; I have been at the transcription game for so long now that I recognise that the odd error does get through.

Regarding the non existent dates I suspect b****y minded clerks who didn't like doing the job as one possible reason, the other could be a misreading of the original entries, the writing varied from reasonable to diabolical.

I don't have the original sheets in front of me but whilst some sheets had about 25 entries per page (see the images on the disk, those covering the Guildford volume had 40-50 entries per page and were a real pain.

I look forward to following the discussion on these pages as an interested observer, at least until the next SHC project starts, which is not War related

Well, I certainly cannot thank the project enough for all the hard work that went into it. Having looked at a fair number of census images myself I can imagine just how hard it must have been to decipher some of the entries.

I have found entries for several family members, even one in 1927 that shed some interesting light on a family puzzle, and I have 500 plus other names I'm interested in. The data really is a goldmine .. Having the raw data in a table provides fast results to things like simple name searches. If you have a list of names to search for just load them into a separate table and perform a join query and bingo!

I really hope it generates interest, discussion and an interchange of ideas.

Chris.

[Charles Fair]

QUOTE (Chris_B @ Dec 4 2005, 03:56 PM) <{POST_SNAPBACK}>

If you are considering buying this, it is a bargain at £16 (in p&p). I would say it’s an ESSENTIAL purchase for anyone interested in their family’s history, researching names on a War Memorial, is a medal collector, follows a given regiment or has an interest in social history.

I understand the data it contains is perhaps unique within the UK as a record of recruitment during the Great War. The Surrey History Centre, and its volunteers, is to be congratulated on the mammoth task of documenting the 84,907 entries it contains.

Hear hear, I second the above. Borstal and all other contributors deserve our congratulations for making this data available. It is a master stroke to make the raw data available.

QUOTE (Chris_B @ Dec 4 2005, 03:56 PM) <{POST_SNAPBACK}>

Having the skills is a learning opportunity, I wouldn’t class myself as a beginner, but my database skills are very very rusty, and I have forgotten most things I ever knew.

I do class myself as a beginner (last having used Access 10 years ago) and you are a long way up the learning curve compared to me. Many thanks for the tutorial above, and I for one will gladly soak up any tipe.

Charles

[Charles Fair]

I look forward to following the discussion on these pages as an interested observer, at least until the next SHC project starts, which is not War related

I hope that there will be plenty of discussion of the data and findings on the forum.

Its a bit of a long shot this one - but has the SHC thought of putting the Absent Voters Lists for Surrey and the relevant London Boroughs into a searchable database? The AVLs are a very useful source and would complement the recruiting registers nicely. In some respects this should be an easier job as AVLs were printed and ought to be scannable. I would be happy to help as I have experience of digging through various London AVLs and know the problems.

regards

Charles

[Chris_B]

Hear hear, I second the above. Borstal and all other contributors deserve our congratulations for making this data available. It is a master stroke to make the raw data available.

I do class myself as a beginner (last having used Access 10 years ago) and you are a long way up the learning curve compared to me. Many thanks for the tutorial above, and I for one will gladly soak up any tipe.

Charles

I'm glad I wasn't just have a conversation with myself about MS ACCESS yesterday Some folk looking at this thread might have been aghast to see it full of computer gibberish.

You might find this handy...

A simple quick and reasonably flexible way to search for names is to use a PARAMETER QUERY with PATTERN MATCHING critieria on both surname and forename. In plain English this simply means you write the query once and every time you run it against the REGISTER table you just enter the surname your are looking for and optionally the initial, or part of a firstname. You can also widen the name search by entering parts of a surname ( a bit like wild card searches on Census databases).

The MS-ACCESS SQL is

PARAMETERS [search Name] Text ( 255 ),[search Initial] Text ( 255 );

SELECT REGISTER.*

FROM REGISTER

WHERE (((REGISTER.SURNAME) Like [search Name] ) AND ((REGISTER.FORENAMES) Like [search Initial] & "*"))

ORDER BY REGISTER.SURNAME, REGISTER.FORENAMES;

You can use the above directly by cutting and pasting it into the SQL View when creating a new query.

1. Open the database in MS-Access

2. Click on Queries in the object list of the database window

3. Click on Create query in design view

4. Close the Show table window without adding any tables

5. Click on View and then click on SQL View on the top menu bar

6. Cut and past the bold text above into the SQL View window

7. Click on Query and Run on the top menu bar

8. Test it works and save as with name of your choice

When using the query as written, entering only a surname like "COLE" would give all matches on "COLE" - entering "COLE*" will give matches on "COLE, COLES, COLEMAN etc." regardless of firstname. Entering surname "COLE" and just "L" for initial, give all matches for "COLE, L" etc.

I'm no expert, but I'm happy to help whenever I can with ideas about using MS-ACCESS. I was hoping we might get a collection of hints and tips together for analysing the data, aside from any discussion that might arise about its actual content.

Chris.

[Charles Fair]

QUOTE (Chris_B @ Dec 5 2005, 09:58 PM) <{POST_SNAPBACK}>

I'm glad I wasn't just have a conversation with myself about MS ACCESS yesterday Some folk looking at this thread might have been aghast to see it full of computer gibberish.

.....

I'm no expert, but I'm happy to help whenever I can with ideas about using MS-ACCESS. I was hoping we might get a collection of hints and tips together for analysing the data, aside from any discussion that might arise about its actual content.

Chris, thanks for the tips, your expertise is way way ahead of mine. I will try these out when I have a little more time. I will put findings and content comments on the other thread.

Charles

[borstal]

Charles

I have checked with SHC regarding the AVL's they have some for the country areas, but none (as far as they are aware)for the boroughs.

Any possibility of converting these to a database must be considered as remote, the original records have never been filmed, an initial prerequisite if you are going to make copies to send to volunteers.

What will happen in the future is anybodies guess, but requests for look ups rather than purchasing what is a cheap CD (for what it contains) does not help.

See my other post under the Recruitment Registers (Indexing topic)

[Charles Fair]

I have checked with SHC regarding the AVL's they have some for the country areas, but none (as far as they are aware) for the boroughs.

Any possibility of converting these to a database must be considered as remote, the original records have never been filmed, an initial prerequisite if you are going to make copies to send to volunteers.

Thanks for asking, I will make it along to the SHC soon to have a look. I know that a copy of the AVL for Wandsworth is held in the London Metropolitan Archives. I have been through this and it contains 36,000 names. I am trying to find if the other Boroughs exist.

The value of the AVL is that it has the regimental numbers - which the recruitment registers do not. By using a combination of name, initials and often the street it should be possible to get matches for many of the enlistments in WW1. I have certainly found this for many of the 19th London men in the Wandsworth AVL.

I would be quite happy to make a digital copy of the county AVL. I have a hi-spec digital SLR camera and would be able to photo the whole thing given a few hours and then burn it onto DVD. This process is less damaging to the AVL than photocopying. I would be delighted to come over to the SHC and talk about what is possible with the AVL.

What will happen in the future is anybodies guess, but requests for look ups rather than purchasing what is a cheap CD (for what it contains) does not help.

quite agree, its exceptional value

Charles