= F.[Layer]
By experimenting I have discovered that it is also possible to select the families on one specific layer, in the following example layer number 1.
SELECT F.[Description], L.[Description] Layer
FROM Layers L, Families F
WHERE L.[Code] = F.[Layer] AND L.[Code] = 1
I hope other users find this useful. Could some of these unusual query syntax be added to the help file, or should we have a post section where users can post any unusual query syntax they have found useful?
John Wood

To give quick access to all references to a particular source document use the following query. The document code will be the one found under the "Code" column of the "Source documents" window and will be unique to your database. It could be a particular church baptism records, or a census year, or what your great aunt told you. Simply replace the number 16 with your source document code. Copy the example below to a notepad and save with a .sql extension, then load it into the SQL Query by clicking the "Load from file" icon under File on the SQL Query window.

Searching for a particular church I found that the search engine will not accept the apostrophe character, as in St. Paul's. It will however accept the full stop after St. So how do you select only London, St. Paul's church from the list below:-

London St. Paul
London St Paul
London St. Paul's
London St Paul's
London, St. Paul's
St. Paul's, London
London St Anywhere
Clondon, St. Paul
Londoning, St Paul

The following SQL query will select only the first four places from the above list as family marriage places. There is a SPACE after the word London to eliminate Londoning but this also eliminates London, St. Paul's because of the COMMA after London. It also eliminates St. Paul's, London because there is no SPACE after London.

I wanted to search for all the Notes with a Privacy Level of 66 from Families, but restrict the search to Families on Layer 2. I don't use Privacy Level 66 for People Notes so the search does not check that it is only searching Family Notes. Here is the query :-

= F.[Layer] AND L.[Code] = 2 AND N.[RecordCode] = F.[Code] AND
N.[PrivacyLevel] = 66
The result shows the description field of the Family, the Description field of the Notes, and the first line of the text of the Notes. If you export the result to a text file you get all the text from the Notes.
Please Nick can you check that what I have said is correct.
John Wood

The "Editing data in bulk" help page suggests a possible use for this feature but does not include an example. Here is the paragraph from the help page:-

"For example, if you have added a new user-defined field to the people table, you may want to add the data for the new field for every person in the database. To do this you would apply an appropriate SQL query which would include the new field so that every person record is displayed in the results list but possibly only showing the new field and the "Description" field."

Here is an example of how I use it. I have a User Defined Field for Families of "On 1911 Census", which I use to record where people are living on the 1911 census. The following query lists all the families with any information in the "On 1911 Census" field and sorts them by address. I can then see if I have entered addresses in a consistent form. I can also see who was living near to who in 1911.

--Where are families in 1911?
SELECT Families.[Description], Families.[On 1911 Census]
FROM Families
WHERE ( Families.[On 1911 Census] IS NOT NULL )
ORDER BY Families.[On 1911 Census]

By changing the search from "NOT NUL" to"dudley" I can check which families lived in Dudley in 1911, see below:-

There are lots of other permutations of these simple searches. You don't have to be editing data in bulk to use these queries, but if you do resort to editing data in bulk please read the WARNING on the help page.

You backup your database, by taking a copy, once a week or once a month. You then start to worry about what you have changed. You would like to go back to the old database for some of your data, but not it all. The following queries will show you which person & family records have changed. Change the date following the JDATE to when you last took a copy of your database.

If you want to compare the old & new family records side by side, export the specific families from the two versions of your database as GEDCOM files. Then import the two GEDCOM files into a new database.

This query can be used in Kith & Kin to show all people who are in limbo. Furthermore, as you add the people to their families, they immediately disappear from the query result list ("live" update), so you can tell how far along you've gotten.

-- List all people who are in limbo
select * from People
where Code not in
(select P.Code from People P, Links L where
(P.Code = L.ChildMarr and L.RecType = 'F') or (P.Code = L.PersFam and L.RecType = 'P'));

Further to the first post in this section on 14 May to combine Families and Layers I have another permutation to add. If you need to use the Layer feature in the Query Family window you need to use the number of the layer not the name you have given the layer. The following mod. to the previous syntax shows the layer number as well as the layer name for each family.

Here is a query I use to list the picture description & file name for a specific set of pictures I have linked to my database. You could list all your pictures and filter the specific set by copying the list into a spreadsheet. First a bit of background as to how I set up my database. Under the Pictures folder that the system creates automatically I have sub-folders for census, certificates, BDM Images, Reports, and others. Under the census sub-folder I have other sub-folders for each census year, ie. 1841, 1851 etc.. Also under each Family in Kith&KinPro I have user defined fields for each census year. In the Notes field for each Source Reference census year, I add my transcription of the census page for that family for that year. I also add the image of the page, having first copied the image into the census year sub-folder.

If you are still with me, the following SQL query lists the description & file name of all pictures linked to Source References of the 1881 census.

Here is a variation to the query I posted on the 14 May 2009 under the subject "List all references to a particular source document" First a recap on what I said then:-

To give quick access to all references to a particular source document use the following query. The document code will be the one found under the "Code" column of the "Source documents" window and will be unique to your database. It could be a particular church baptism records, or a census year, or what your great aunt told you. Simply replace the number 16 with your source document code. Copy the example below to a notepad and save with a .sql extension, then load it into the SQL Query by clicking the "Load from file" icon under File on the SQL Query window.

Only search family record types.
Look for the word "eureka" missing from the notes field.

The word "eureka" is case sensitive. You can change the word "eureka" for any other word or string, (you may test for "not shure about this" etc), that you expect to find in your notes. Remove the NOT before the LIKE to find notes fields that do contain the word "eureka".

SELECT S.RecordType, S.RecordCode, S.FieldName, S.Notes
FROM Sources S, Documents D
WHERE S.Notes NOT LIKE '%eureka%' AND S.RecordType LIKE '%F%' AND S.Document = D.Code AND D.Code = 16
ORDER BY S.RecordCode

Thanks John
This set me thinking.
Using separate queries for the Family and Person tables means you can include fields from those tables in the output (and if you wish the selection criteria too), making the resulting output more useful.

The above includes the Family description and lists all records which link to a source containing the word 'letter' (or 'Letter' or 'LETTER').

Similarly instead of referencing the Sources table you can link to Notes.
I add a note with the description TO DO to every record where some information is missing, describing the work to do. The following then lists those records.
SELECT Notes.RecordType, Notes.RecordCode, People.Description Person, Notes.Description,
Notes.PrivacyLevel, Notes.Notes
FROM Notes, People
WHERE Notes.RecordType = 'P' AND People.Code = Notes.RecordCode AND UPPER(Notes.Description) LIKE '%TO DO%'

The column for People.Description is given the header "Person"
Using UPPER term guards against me entering "To Do" sometimes!

You could also search for text in the Notes.Notes field. I use a note ADDRESSES to store addresses on the Family table, so could include WHERE UPPER(Notes.Notes) LIKE '%LONDON%' to find all families who had ever lived in London.

These indicate the power of the SQL tool and really set the product apart from the likes of Ancestry - many thanks Nick!