Searching the Ledgers
Using the Database
Introduction
Each ledger is saved in its own searchable database at this time. Ideally we would eventually like to have a master database that contained all of the entries from Coldspring House and all of the entries from Cottonwood House. What we have, however, is a searchable set of records that can provide the student and the researcher with a valuable insight into life along the Cariboo Wagon road. This document hopes to point out some of the features of the database and provide instruction on how the search page works.Some terminology
These databases consist of records of each transaction that took place. A “record” is all the information about a single transaction: the person’s last name, first name, the date, the item that was purchased, and so on. In the case of these ledgers each ledger has between a thousand and five thousands “records” covering the business that took place on a day to day basis at either of the road houses. Within each record each of the individual pieces of information are defined as “fields”. So the person’s last name is stored in a field called “LastName”, and whether the transaction was a debit or credit is stored in the “Type” field. This information is important because the searching that we do in a database is focused on either one field or a combination of fields and the result we get back is the set of records that match that search request (“query” is the technical name for the search request).For each database you can search on any combination of the following
fields:
D
ate:
It is entered in mm/dd/yyyy format, you might want to search a specific
date but more often you will want to search a range which we will discuss
below. A caution with the date field and “sorting”, you can set up
the fields you want to search for and then select a single field to sort
on. It seems natural to sort on the date field but our search system
is a bit too literal at this point, since it will return all the records
that have a January date (the “01” month) regardless of year, then go on
to return all records for February. In our case this is not a major
problem, since all entries were entered in date order. Any search done without
a sort will automatically return the records in the order in which they
were entered.
LastName: The last name of the person needs to be entered and the spelling needs to be accurate. You can compensate by using either the “starts with” or “contains” operator rather than the “equals” operator to extend the range of the search. If you use the “equals” operator it will only return those records that have a last name exactly like the search term. In addition we have used the LastName field to store the “descriptions” John Boyd used for people who were not regular customers amongst them terms like “passengers”, “China travellers” or “Indian”.
Company: Often the roadhouse business was done with companies rather than individuals. These were accounts that people used without the need for immediate payment. Often, then, the “First” and “LastName” fields are empty if there is a company name used instead.
Item: This is the key search field for the database and can contain a single word like “oats” or may contain several sentences describing an event or larger transaction. This will also be the field where you enter the Boyd children’s names if you wish to go searching for information on them. They were not “customers” so they never show up in the LastName or Company field but they do show up in the item field often along with the reason they are being paid.
Page: This is the page number in the original journal where
the entry came from. It gives an idea of where in the journal the
entry was made and if you want to see all the entries on a specific page
this will get at it for you, again automatically in the order that they
were entered.
Search Option
For each field there is a wide range of search options that you select from the drop down list by pressing the arrow to the side of the “begins with” phrase. For most of your searches you will choose either “equals” or “contains” as your operator. You might choose “begins with” if you want to get all possible spelling of a name so instead of entering “Atkinson” or “Atchinson” you could enter “At” and select the “begins with” which would give you all last names starting with “At”, you would of course get the Atwoods and others but it could help to narrow your search rather quickly.
Most of the other operators only make sense if you are dealing with
numeric data. Using the search function for getting a range of dates will be tricky,
as mentioned above, since the search function doesn’t like our date format
(we will work on this for later versions of the search).
Matching with AND or OR
If you are only searching on one field you can then move on to the “Start Search” button at the bottom of the screen. However if you are filling in more than one field you need to consider which “matching” criteria you want to choose. By default the “AND” operator is active which means that the search will only return those records that have the target words you have placed into each of the fields. This is normally why you place a search term in more than one field. However if you wanted the search to return any record that had ANY of your possible search terms you would choose the “OR” option. Even on a dial up connection the search seems to be reasonably fast so you can experiment with this fairly easily.Sorting By
The most common methods of sorting would be by date or by customer. As we have mentioned don’t bother with date since it doesn’t work properly and all results will automatically be given to you in date order. Quite often however you may want to sort by LastName or Company in order to organize the information.
Number of Returns
The final option is the number of returns you will have shown at one time. This can range from 10 to 50 or all of the matches. What will be best for your searches will depend on your computer system and the speed that the search returns are up on the screen. Operating somewhere in the middle, say at 30 returns, has been reasonably quick on the tested dial up lines, however if you want to do some data analysis of a topic you may want all the returns at once in order to group them for analysis in a spreadsheet program.
Search Results / Details pages
The search results page shows a table. Each line across the table represents one entry in the Boyd ledger or one record. Depending on how your browser is set up you many need to scroll in order to see the full table or cells may be elongated to hold more text.The date entry on the left most side of the screen is a clickable link to a screen that shows just that single record. This can be quite useful if you find a record with a great deal of writing (usually the item field) since the table view can be quite hard to read with only 4 or 5 words to a line. As a detail page the same information will show up as a more or less normal paragraph.
In addition the detail page shows two fields that are not shown in the table, the “ACNum” and “Type” fields. The “ACNum” stands for “account number” but is not entirely accurate since a very active customer can have several “ACNum” numbers. The “ACNum” represents the page in the “Accounts Receivable” ledger where this particular record was posted at the end of the day. In the “Accounts Receivable” ledger each customer has at least one page, their name is at the top along with other details at times, and each of their transactions is recorded as it takes place. Transactions are added up from time to time to determine what the company or person owes John Boyd at that point in time.
The “Type” field holds the abbreviation CR for credit or DR for debit.
If the person pays John Boyd cash for either that specific transaction
or for accumulated purchases it is a CR or credit. If they “put it
on the bill”, as most of the customers do, then it is recorded as a DR
and goes into the Accounts Receivable ledgers.
A note on accuracy
Please have some patience with our ledgers. For a 6 week assault on a huge mass of information it was a good first start, however the students did make some mistakes. They would also place a “?” if they weren’t quite sure of spelling or wording. You will find errors throughout the ledgers and we do have a program underway where we are checking all entries and correcting them.
