Finding events with no source citations; finding multiple events (SQL pt. 4)
The following operations will go a long way to help you clean up your genealogy data.
Events with no citations
Let’s say you want to want to find all persons with an 1850 Census fact, but with no source citation. It can be any event type, this is just an example.
First, make a list of all the event type codes in your database:
select distinct EVENTS.EV_TYPE, EVENTS.SUB_TYPEB as num, PLACES.PLACE_NAME from EVENTS
inner join PLACES on PLACES.PLACE_NO = EVENTS.SUB_TYPEB
where EVENTS.EV_TYPE = 56
“56” is the code for user-defined event (see list here).
You will get something like this. Note that “1850 Census” is event sub-type 173.
Next, search for all persons who have an 1850 Census fact, for which there are no source citations. Note the “173” which we are using based on the previous results:
select PERSONS.PIN, PERSONS.NAME, EVENTS.FROM_DATE, PLACES.PLACE_NAME
from ((EVENTS inner join PERSONS on EVENTS.PIN = PERSONS.PIN) inner join PLACES on EVENTS.PLACE_NOB = PLACES.PLACE_NO)
where EVENTS.SUB_TYPEB = 173 and EVENTS.EVENTFOOTB = 0
You will get this:
You can search for each person in your genealogy database using the name, and add the source.
Multiple occurrences of same event
What if you need to identify persons who have more than one instance of the same event? This could be due to a merge, and you want to consolidate the events into a single one.
Do the following. Remember to substitute “173” with whatever number applies to your database:
select PERSONS.PIN, PERSONS.NAME, EVENTS.FROM_DATE, PLACES.PLACE_NAME, count(*) as c
from ((EVENTS inner join PERSONS on EVENTS.PIN = PERSONS.PIN) inner join PLACES on EVENTS.PLACE_NOB = PLACES.PLACE_NO)
where EVENTS.SUB_TYPEB = 173
group by EVENTS.PIN
having c > 1
You will get:
Preserving and safeguarding your family’s historical documents is crucial for maintaining a connection with your heritage. Here are some tips to help you protect these valuable pieces of your family history:
- Store Documents in a Safe Location:
- Choose a cool, dry, and dark place to store your historical documents. Avoid areas with fluctuating temperatures or high humidity, as these conditions can damage paper and ink.
- Use Archival-Quality Materials:
- When possible, use acid-free folders, boxes, and sleeves for storing documents. Archival-quality materials help prevent deterioration and yellowing of paper over time.
- Handle with Clean Hands:
- Wash and thoroughly dry your hands before handling historical documents. Oils and dirt from your fingers can transfer to the documents, leading to damage over time.
- Avoid Sunlight and Harsh Lighting:
- Exposure to sunlight and harsh artificial lighting can fade and damage documents. Store them in a place where they are shielded from direct sunlight, and use low-intensity, non-UV lighting when examining them.
- Digitize Documents:
- Create digital copies of your historical documents. This serves as a backup and allows you to share the information with other family members. Use high-resolution scanning equipment and save the files in a secure, accessible location.
- Label and Organize:
- Clearly label and organize your documents. Create a system that makes it easy to locate specific items and maintain a record of what each document contains.
- Handle with Care:
- When handling documents, be gentle and use proper support. If possible, use cotton gloves to prevent direct contact with the paper. Avoid folding or creasing fragile documents.
- Protect Against Pests:
- Store documents in pest-resistant containers or use archival-quality pest control methods. Insects and rodents can cause significant damage to paper and other materials.
- Consider Climate-Controlled Storage:
- If you have valuable or irreplaceable documents, consider investing in a climate-controlled storage facility. These environments help regulate temperature and humidity levels, providing optimal conditions for document preservation.
- Create a Disaster Preparedness Plan:
- Develop a plan for unexpected events, such as floods, fires, or other disasters. Consider keeping copies of important documents in a separate, secure location, or use fireproof and waterproof storage containers.
- Share Information with Family Members:
- Ensure that other family members are aware of the importance of preserving historical documents. Encourage a sense of shared responsibility for maintaining and safeguarding the family’s heritage.
- Consult Preservation Professionals:
- If you have particularly delicate or valuable documents, consult with preservation professionals or archivists. They can provide guidance on proper storage, restoration, and handling techniques.
By taking these precautions, you can help ensure that your family’s historical documents are preserved for future generations, allowing them to connect with their roots and appreciate the rich tapestry of your family’s history.
Social media can be a valuable tool for growing your family tree and connecting with relatives. Here are some ways social media can assist you in this process:
- Connect with Relatives:
- Platforms like Facebook, Instagram, and Twitter allow you to search for and connect with relatives. You can join genealogy or family history groups, where people often share information about their family trees.
- Create a Family Page or Group:
- Establish a dedicated page or group for your family. This can serve as a centralized hub for sharing information, photos, and updates about the family tree. Invite relatives to join and contribute.
- Share Family Stories and Photos:
- Social media platforms provide an excellent space to share old family photos, stories, and anecdotes. This can help jog memories and encourage relatives to share their own stories and information.
- Use Genealogy-specific Platforms:
- Platforms like Ancestry.com, MyHeritage, and Geni are designed specifically for genealogy research. While not traditional social media, these sites enable collaboration and sharing of family trees among relatives.
- Collaborate on Research:
- Social media allows for real-time collaboration. You can create events or posts to coordinate family research efforts. This can help avoid duplication of work and lead to the discovery of new information.
- Utilize Hashtags:
- Create a unique family tree hashtag to make it easier for relatives to find and share information related to your family history. This can be especially useful during family reunions or special events.
- Engage in Q&A Sessions:
- Host Q&A sessions on social media to encourage discussions about family history. This can be a great way to gather information, answer questions, and connect with relatives who may have valuable insights.
- Attend Virtual Family Reunions:
- Use social media to organize and host virtual family reunions. This allows relatives from different locations to participate, share stories, and contribute to the family tree.
- Promote DNA Testing:
- If your family is open to it, encourage relatives to take DNA tests through platforms like AncestryDNA, 23andMe, or MyHeritage. This can help confirm relationships, identify distant relatives, and expand your family tree.
- Be Respectful of Privacy:
- Keep in mind that not everyone may be comfortable sharing personal information on social media. Respect privacy concerns and be cautious about sharing sensitive details without permission.
By leveraging the power of social media, you can enhance collaboration, gather information, and enrich your family tree with the contributions of relatives from around the world.
All burials in a certain place, ex: “CT” Connecticut
select PIN, FROM_DATE, EV_TYPE,
(select NAME from PERSONS as P where P.PIN = E.PIN) as NAME,
(select PLACE_NAME from PLACES as PL where PL.PLACE_NO = E.PLACE_NOB and PL.PLACE_NO > 0 ) as PLACE
from EVENTS as E where E.EV_TYPE = 4 and PLACE like "%CT%"
Burial before Death, or too long after
select E1.PIN,
(select NAME from PERSONS as P where P.PIN = E1.PIN) as NAME,
E1.FROM_DATE as DATE_DEATH,
(select FROM_DATE from EVENTS as E2 where E2.PIN = E1.PIN and E2.EV_TYPE = 4) as DATE_BURIAL
from EVENTS as E1
where E1.EV_TYPE = 3 and DATE_BURIAL > 0 and (DATE_DEATH / 10000 > DATE_BURIAL / 10000 or (DATE_BURIAL - DATE_DEATH) > 10000)
order by DATE_BURIAL, NAME
Persons dead, with no Burial
select E1.PIN,
(select NAME from PERSONS as P where P.PIN = E1.PIN) as NAME,
E1.FROM_DATE as DATE_DEATH,
(select count (*) from EVENTS as E2 where E2.PIN = E1.PIN and E2.EV_TYPE = 4) as BURIAL_COUNT
from EVENTS as E1
where E1.EV_TYPE = 3 and BURIAL_COUNT = 0
order by DATE_DEATH
All men eligible for WW1 draft
select E1.PIN,
(select NAME from PERSONS as P where P.PIN = E1.PIN) as NAME,
(select SEX from PERSONS as P where P.PIN = E1.PIN) as SEX,
E1.FROM_DATE as DATE_BIRTH
from EVENTS as E1
where E1.EV_TYPE = 1 and DATE_BIRTH >= 18720000 and DATE_BIRTH <= 19000000 and SEX = 1
Show children with adoptive + bio parents
select P.NAME, E.EV_TYPE, E.EV_SEQNO as ADOPTER, P2.NAME as NAME_ADOPTER, P3.NAME as NAME_FATHER_BIOLOGICAL
from
(((PERSONS as P inner join EVENTS as E on P.PIN = E.PIN)
inner join PERSONS as P2 on P2.PIN = E.EV_SEQNO)
inner join PERSONS as P3 on P3.PIN = P.FATHER_PIN)
where E.EV_TYPE = 8
Search for an exact match on Surname
select NAME from persons where NAME like "%" || x'05' || "Bouvier" || x'06' || "%"
Search for an exact match on Given name
select NAME from persons where NAME like "%" || x'02' || "Lee" || x'03' || "%"
Death event but zero death date
select PIN, EVENTS.FROM_DATE,
(select PERSONS.NAME from PERSONS where PIN = EVENTS.PIN) as NAME
from EVENTS
where EVENTS.EV_TYPE = 3 and EVENTS.FROM_DATE = 0
Here’s how to find a SMITH who married a JONES:
select P1.NAME, P2.NAME
from ((EVENTS inner join PERSONS as P1 on EVENTS.PIN = P1.PIN)
inner join PERSONS as P2 on EVENTS.EV_SEQNO = P2.PIN)
where P1.NAME like "%SMITH%" and P2.NAME like "%JONES%"
To list everyone and their parents:
select P.NAME, P1.NAME, P2.NAME
from ((PERSONS as P inner join PERSONS as P1 on P.FATHER_PIN = P1.PIN)
inner join PERSONS as P2 on P.MOTHER_PIN = P2.PIN)
where P.FATHER_PIN > 0 or P.MOTHER_PIN > 0
order by P1.NAME, P2.NAME
List events + Places
select PIN, EVENTS.FROM_DATE,
(select PLACES.PLACE_NAME from PLACES where PLACE_NO > 0 and PLACE_NO = EVENTS.PLACE_NOB)
from EVENTS
where EVENTS.EV_TYPE = 3 and EVENTS.FROM_DATE = 0
Search for no Surname
select NAME from persons where NAME like "%" || x'05' || x'06' || "%"
Search for no Given name
select NAME from persons where NAME like "%" || x'02' || x'03' || "%"
Formatting
If an alias is assigned (“as…”) that starts with “NAME”, Charting Companion will understand that it is a name column, and will re-format it accordingly. Charting Companion stores the names in an internal format that includes binary delimiters of the name parts.
The following aliases can be used. The alias need only begin with these codes, and can include additional characters. Ex: NAME1, NAME_PARENT, etc.
| Alias (as …) | Converted to |
| NAME | Full name |
| FIRST | Given name |
| LAST | Surname |
| DATE | Date (YYYYMMDD) |
| DAY | Day part of date |
| MONTH | Numeric month part of date |
| YEAR | Year part of date |
SQL commands – examples
Retrieve all persons and their parents
select P.NAME, P1.NAME as NAME_father, P2.NAME as NAME_mother
from ((PERSONS as P inner join PERSONS as P1 on P.FATHER_PIN = P1.PIN)
inner join PERSONS as P2 on P.MOTHER_PIN = P2.PIN)
where P.FATHER_PIN > 0 or P.MOTHER_PIN > 0
order by P1.NAME, P2.NAME
Display all couples
select P1.NAME, P2.NAME, EVENTS.EV_TYPE
from ((EVENTS inner join PERSONS as P1 on EVENTS.PIN = P1.PIN)
inner join PERSONS as P2 on EVENTS.EV_SEQNO = P2.PIN)
where EVENTS.DUAL_KEY = 1 and P1.SEX = 1
List persons with no Sex specified
select PIN, NAME from PERSONS where SEX < 1 or SEX > 2
Find couples with no children
Three steps of preparation; run once per GEDCOM import:
drop table COUPLES
click “Execute” (ignore error if table does not exist yet)
create table COUPLES (HUSB int, WIFE int, SEX int)
click “Execute”
INSERT INTO COUPLES (HUSB, WIFE, SEX)
select
EVENTS.PIN,
EVENTS.EV_SEQNO,
(select PERSONS.SEX from PERSONS where PIN = EVENTS.PIN) as SEX1
from EVENTS
where EVENTS.DUAL_KEY = 1 and SEX1 = 1
click “Execute”
Shows couples with no children (run as often as you wish):
select
HUSB as Husband_PIN,
(select PERSONS.NAME from PERSONS where PIN = HUSB) as NAME,
WIFE as Wife_PIN,
(select PERSONS.NAME from PERSONS where PIN = WIFE) as NAME,
(HUSB || ',' || WIFE ) as Marriage_PINs
from COUPLES
where Marriage_PINs not in
(select distinct (FATHER_PIN || ',' || MOTHER_PIN) from PERSONS)
Display all children and their parents
select
HUSB as Husband_PIN,
(select PERSONS.NAME from PERSONS where PIN = HUSB) as NAME,
WIFE as Wife_PIN,
(select PERSONS.NAME from PERSONS where PIN = WIFE) as NAME,
(HUSB || ',' || WIFE ) as Marriage_PINs
from COUPLES
where Marriage_PINs not in
(select distinct (FATHER_PIN || ',' || MOTHER_PIN) from PERSONS)
order by P2.NAME
Display contents of COUPLES
select P1.NAME, P2.NAME
from ((COUPLES inner join PERSONS as P1 on COUPLES.HUSB = P1.PIN)
inner join PERSONS as P2 on COUPLES.WIFE = P2.PIN)
order by P1.NAME
Daughters with different surname than father
Could be an indication that they are recorded with their married name.
select PIN, NAME, SURNAME as SURNAME1,
(select SURNAME from PERSONS as P2 where P2.PIN = P1.FATHER_PIN) as SURNAME2
from PERSONS as P1
where SEX = 2 and P1. FATHER_PIN > 0 and SURNAME1 <> SURNAME2
Extract all Births
select PIN, FROM_DATE, EV_TYPE,
(select NAME from PERSONS as P where P.PIN = E.PIN) as NAME,
(select PLACE_NAME from PLACES as PL where PL.PLACE_NO = E.PLACE_NOB and PL.PLACE_NO > 0 ) as PLACE
from EVENTS as E where E.EV_TYPE = 1
Calculate life spans
select E1.PIN, E1.FROM_DATE, E2.FROM_DATE, (E2.FROM_DATE - E1.FROM_DATE)/10000 as Life_Span,
(select NAME from PERSONS as P where P.PIN = E1.PIN) as NAME
from EVENTS as E1
inner join EVENTS as E2 on E2.PIN = E1.PIN and E2.EV_TYPE = 3
where E1.EV_TYPE = 1 and E1.FROM_DATE > 0 and E2.FROM_DATE > 0
order by Life_Span
List everyone whose death date is before birth date
select PIN, NAME,
(select FROM_DATE from EVENTS as E1 where E1.PIN = P.PIN and E1.EV_TYPE = 1) as [DATE_BIRTH],
(select FROM_DATE from EVENTS as E2 where E2.PIN = P.PIN and E2.EV_TYPE = 3) as [DATE_DEATH]
from PERSONS as P
where DATE_BIRTH > 0 and DATE_DEATH > 0 and DATE_DEATH < DATE_BIRTH
order by DATE_BIRTH
List everyone’s birth, death + place
Makes Charting Companion a handy, general-purpose extraction & reporting tool
select E1.PIN,
(select NAME from PERSONS as P where P.PIN = E1.PIN) as NAME,
E1.FROM_DATE as DATE_BIRTH,
(select PLACE_NAME from PLACES as PL where E1.PLACE_NOB > 0 and PL.PLACE_NO = E1.PLACE_NOB and E1.EV_TYPE = 1) as Birth_Place,
E2.FROM_DATE as DATE_DEATH,
(select PLACE_NAME from PLACES as PL where E2.PLACE_NOB > 0 and PL.PLACE_NO = E2.PLACE_NOB) as Death_Place
from EVENTS as E1
left join EVENTS as E2 on E2.PIN = E1.PIN and E2.EV_TYPE = 3
where E1.EV_TYPE = 1
Total number of children in each family
select P2.NAME,P3.NAME, count(*) as kid_count
from (((COUPLES inner join PERSONS as P1 on COUPLES.HUSB = P1.FATHER_PIN and COUPLES.WIFE = P1.MOTHER_PIN)
inner join PERSONS as P2 on P2.PIN = COUPLES.HUSB)
inner join PERSONS as P3 on P3.PIN = COUPLES.WIFE)
group by P2.PIN, P3.PIN
Notes
“Order by NAME” will sort by first name, as this is how the name is stored in the database. To sort by surname/given name, add a column with “as FIRST” alias, and add:
order by SURNAME, FIRST
When clicking on a column header, CC will sort as plain text, which puts ’10’ before ‘2’.
DB Browser for SQLite
DB Browser for SQLite is a free utility for general-purpose inspection and exploration of SQLite databases. You can download it from here:
New Charting Companion “SQL query” feature
Charting Companion 8.2 introduces the SQL query feature, which enables you to extract custom or bespoke information from your genealogy database.
“SQL” stands for Structured Query Language. It is a set of general-purpose commands to change or retrieve information from any relational database. CC uses SQLite.
The SQL query operates on GEDCOM files, because each genealogy program’s native database design (called a schema) is different.
Charting Companion database schema
The best way to explain SQL is with some simple examples. First, you need to be familiar with Charting Companion’s schema. It is documented in detail at:
https://progenygenealogy.com/blogs/charting-companion-database-schema/
Your genealogy data is stored in three main tables: PERSONS, PLACES, EVENTS. There are additional tables IMAGES, NOTES, CITATIONS, AUTHORS.
Each table contains fields or columns. Columns can be numeric or text. The PERSONS table includes :
- PIN, a unique identification number
- NAME, the person’s name (text)
- FATHER_PIN, the PIN of the Father
- MOTHER_PIN
- SEX
- PAR_MRINB, the unique number of the parents’ marriage or family
Examples
Keywords
So, to display a specific person’s information, click on the menu “Find ➔ SQL query”.
In the dialog, enter this command in the “SQL Query” box, then click “Execute”:
select PIN, NAME from PERSONS where PIN = 1
Charting Companion returns the PIN & NAME of the person with PIN #1.
This is not terribly useful, but it does introduce some SQL concepts:
- ‘select’ picks the columns that will be displayed. If you say “select *”, all columns will be displayed.
- ‘from’ identifies from which table the information will be retrieved.
- ‘where’ specifies the condition(s).
Conditions
To list all men, enter:
select NAME from PERSONS where SEX = 1
To list all persons whose name contains “JONES”, enter:
select PIN, NAME from PERSONS where NAME like "%JONES%"
‘like’ means ‘contains’. The percent “%” symbol is a wildcard. It stands for any number of characters preceding or following the string JONES.
Put the two conditions together and find all men named Jones:
select PIN, NAME from PERSONS
where NAME like "%JONES%"
and SEX = 1
You can break up a command over multiple lines for clarity.
To search for men named “SMITH” or “SMYTH” or “SMYTHE”:
select PIN, NAME from PERSONS
where (NAME like "%SMITH%" or NAME like "%SMYTH%" or NAME like "%SMYTHE%")
and SEX = 1
Note the parentheses around the names, so they are all combined with the SEX condition. The order of precedence of ‘and/or’ is like multiplication/addition in arithmetic.
To specify in which order you want the results sorted, use ‘order’:
select PIN, NAME from PERSONS
where SEX = 1
order by NAME
The EVENTS table
To display all births between 1940 and 1950, enter:
select PIN, EVENTS.FROM_DATE
from EVENTS
where EVENTS.EV_TYPE = 1 and EVENTS.FROM_DATE >= 19400000 and EVENTS.FROM_DATE <= 19501231
Event type ‘1’ is a birth. The event types are documented in the link at the beginning of this article.
‘>=’ means “greater than or equal to”.
Dates are stored as ‘YYYYMMDD’, where the MM and DD can be zero if unknown.
That’s OK, but it would be nice to show the name as well. This is where things get interesting: we can combine two or more tables. To pull in the names, say:
select PIN, EVENTS.FROM_DATE,
(select PERSONS.NAME from PERSONS where PIN = EVENTS.PIN) as NAME
from EVENTS
where EVENTS.EV_TYPE = 1 and EVENTS.FROM_DATE >= 19400000 and EVENTS.FROM_DATE <= 19500000
Notice the second line, enclosed in parentheses. We are introducing a sub-clause where we are we are tying the PERSONS table, which contains the names, to the EVENTS table, which only has PINs. This is called a “normalized” database, where data is stored in only one place (a table column) to avoid redundancy and save space.
The ‘as’ keyword is called an alias. It assigns a shorter name to the column. Calling it “NAME” tells CC to reformat the name by stripping the delimiters.
The PLACES table
Now we’re getting serious. To find all births in Sydney, we combine PERSONS, EVENTS and PLACES:
select PIN,
(select PERSONS.NAME from PERSONS where PIN = EVENTS.PIN) as NAME,
(select PLACES.PLACE_NAME from PLACES where PLACE_NO = EVENTS.PLACE_NOB and PLACE_NO > 0) as Place_Name
from EVENTS
where EVENTS.EV_TYPE = 1 and Place_Name like "%Sydney%"
The third line ties EVENTS and PLACES. It specifies that the Place number in the EVENTS table (EVENTS.PLACE_NOB) is used to look up its corresponding PLACES record (PLACE_NO ) and its name in the PLACES table (PLACES.PLACE_NAME).
We assign an alias to the Place name so we can parse it for “Sydney” in the last line.
‘PLACE_NO > 0’ excludes events with no place specified.
Saving your results
Click “Save” to save the results to a comma-separated text file (.CSV), or a genuine Excel spreadsheet (.XLSX).
Click “Clipboard” to copy the results to your computer’s clipboard. You can paste them into a document or other spreadsheet.
Click on a column header to sort the results. When saving, the results will be written in the original order. Use the ‘order’ keyword if you want to save the results sorted your way.
Dates
To isolate the year, month or day, use:
- Year = FROM_DATE / 10000 (rounds to integer)
Ex: 19400630/ 10000 = 1940 - Month = (FROM_DATE % 10000) / 100
Here the percent ‘%’ is the modulus operator, meaning the remainder after division
Ex: 19400630 % 10000 = 0630
0639 / 100 = 6 - Day = FROM_DATE % 100
Ex: 19400630 % 100 = 30
Historical notes
The Charting Companion schema will strike the more experienced reader as somewhat spartan or terse. For example, the PLACES table serves multiple roles, also storing Source, Address, Repository, Publisher, Title, User-defined event descriptions and text dates. This complicates retrieval. There is a reason for this.
Back in the 90’s, CC used the xBase format, where each table was a separate file. Early Windows limited the number of file handles that a program could open, so we couldn’t get crazy with too many tables.
Between 1999 and 2010, Progeny published the Pedigree Resource file for the LDS Church. PRF was a collection of CDs and DVDs made from the GEDCOM files submitted to the Church by genealogists all over the World.
We squeezed 1M names on a CD, and 5M on a DVD. Each extra byte added to a Person record would cost us 1M of disk space. So we had to be frugal in the file design. The “Name” field is divided into six parts. Rather than squander separate fixed-length fields for each of six name parts, we combine them all into one field, delimited by binary codes. If a Person Name overflows, the remainder is stored in additional Person records. Similar for long Place Names.
We stored some values in binary format, rather than all-character as prescribed by xBase, to reduce space.
Fast GEDCOM
The new GEDCOM import, ten times faster than the original, takes advantage of technologies not available when PAF*Mate and GED*Mate, the grand-daddies of Charting Companion, were developed in 1995.
Currently, the Fast GEDCOM only uses the PERSON, PLACES, EVENTS and IMAGES tables. If you want to do queries on SOURCES, NOTES or CITATIONS, un-check the “Fast GEDCOM” option when opening a file.
Don’t worry about clobbering the database, it is re-created every time you import a newer copy of the .GED file. To force a re-build, hold down the Shift key when you click on the “Open” button.
We do plan on making the Fast GEDCOM import everything soon.
How to use
Don’t worry if you don’t follow the detailed explanations. Just copy & paste the examples, and modify them to suit your needs: names, dates, place names, etc.
If you have a query and you can’t figure out how to do it, contact VicGUM, or the Charting Companion User Group on Facebook, or Progeny, an expert might be able to conjure a spell for you :o)
Remember: with great power comes great responsibility…
Resources
For more information on SQL, see:
https://www.w3schools.com/sql/
https://www.tutorialspoint.com/sql/index.htm
https://mode.com/sql-tutorial/
When Charting Companion imports a GEDCOM file, it creates an auxiliary database as a workfile, in SQLite format.
Charting Companion can execute SQL Queries by clicking “Find ➔ SQL Query”. Requires version 8.2 or later.
Alternatively, the SQLite file is located in “C:\Users\user-id\AppData\Local\Temp\Progeny\CC\“, has the same name as the GEDCOM file, with the extension .DB. The SQLite file can be displayed and searched with a free utility called “DB Browser for SQLite“.
The content and structure of the SQLite file is explained here.
Tables: PERSONS, PLACES, EVENTS, IMAGES, NOTES, CITATIONS, AUTHORS.
Text is stored as UTF-8.
Tables PERSONS and AUTHORS
| Name | Type | Description |
| PIN | Number | Person Id. Primary key. |
| NAME | Text | Name parts separated by delimiters: x01 (binary ‘1’, not character) Name prefix (Mr., Mrs., Dr.) x02 Given name x03 Middle name(s), Nickname x04 Surname prefix (Von, de, la,) x05 Surname x06 Name suffix (Jr., III, Esq.) |
| NAME_SORT | Text | Sort key: Surname – Given name |
| FATHER_PIN | Number | PIN of father (link to PERSONS.PIN) |
| MOTHER_PIN | Number | PIN of mother (link to PERSONS.PIN) |
| SEX | Number | ‘0’ Unknown, ‘1’ Male, ‘2’ Female |
| REF | Text | User-defined Reference no. (GEDCOM REFN tag) |
| PAR_MRINB | Number | Parents’ Marriage Id no. Matches EVENTS.MRINB |
| SURNAME | Text | Extract of surname, to facilitate SQL Query |
Table PLACES, also Sources
| Name | Type | Description | ||||||||||
| PLACE_NO | Number | Place No. Primary key | ||||||||||
| PLACE_NAME | Text | Name of Place or Source | ||||||||||
| PLAS_TYPE | Number |
|
||||||||||
| REPOSITORY | Number | Source Repository. Links to PLACES.PLACE_NO | ||||||||||
| ORIGINATOR | Number | Source Author. Links to AUTHORS.PIN. | ||||||||||
| PUBLISHER | Number | Source Publisher. Links to PLACES.PLACE_NO | ||||||||||
| LINK | Number | Place name overflow. Links to PLACES.PLACE_NO | ||||||||||
| PUBL_DATE | Number | YYYYMMDD | ||||||||||
| CALL_PHONE | Text | Repository: Phone no. Source: Library Call no. |
Table EVENTS
| Name | Type | Description | ||||||||||||
| PIN | Number | PIN of owner. Primary key. Links to PERSONS.PIN. | ||||||||||||
| MRINB | Number | Id no. of parents’ marriage. | ||||||||||||
| EV_TYPE | Number | Event type. See list at end | ||||||||||||
| EV_SEQNO | Number | If DUAL_KEY = ‘0’, Event sequence no., unique within each Person. If DUAL_KEY = ‘1’, PIN of other Person in relationship (Links to PERSONS.PIN). Is an indexed key. |
||||||||||||
| FROM_DATE | Number | YYYYMMDD of event, or start/from date MM and DD can be zero if unknown. If DATE_TYPE = ‘8’, estimated date. |
||||||||||||
| TO_DATE | Number | YYYYMMDD of end/to date, or dual date MM and DD can be zero if unknown. If DATE_TYPE = ‘8’, PLACE_NO of PLACES that contains text of date (Links to PLACES.PLACE_NO). |
||||||||||||
| EVENTFOOTB | Number | Citation no. | ||||||||||||
| DUAL_KEY | Number | ‘0’ event involving one person ‘1’ Event involving two persons |
||||||||||||
| DATE_TYPE | Number |
|
||||||||||||
| AGEB | Number | Child order (Birth sequence no. within a family) | ||||||||||||
| PLACE_NOB | Number | Place of event. Links to PLACES.PLACE_NO. | ||||||||||||
| DESCR_NOB | Number | Description of event. Links to PLACES.PLACE_NO. | ||||||||||||
| SUB_TYPEB | Number | Non-standard event name when DATE_TYPE = 8 Links to PLACES.PLACE_NO. |
Table IMAGES
| Name | Type | Description |
| PIN | Number | PIN of owner. Primary key. Links to PERSONS.PIN. |
| FILENAME | File path | |
| MEDIATYPE | Number | ‘0’ Undefined, ‘1’ Image, ‘2’ URL |
Table NOTES
| Name | Type | Description |
| PIN | Number | PIN of owner. Primary key. Links to PERSONS.PIN. |
| BIO_NOTES | Text | Text of note. |
| EV_NOTE | Text | GEDCOM NOTE tag |
Table CITATIONS
| Name | Type | Description |
| PIN | Number | PIN of owner. Primary key. Links to PERSONS.PIN. |
| EV_TYPE | Number | Event type that this Citation is linked to |
| EV_SEQNO | Number | Sequence no. of event. Matches EVENTS.EV_SEQNO |
| SOURCE_NOB | Number | Source referenced by this Citation Links to PLACES.PLACE_NO. |
| FOOT_SEQ | Number | Sequence no. of Citation within one event |
Event types
| Value | Description | GEDCOM tag |
| 01 | Birth | BIRT |
| 02 | Marriage | MARR |
| 03 | Death | DEAT |
| 04 | Burial | BURI, CEME |
| 05 | Occupation | EMPL, OCCU |
| 06 | Residence | ADDR, RESD, RESI |
| 07 | Christening | CHR, CHRI |
| 08 | Adoption | ADOP |
| 09 | Census | CENS |
| 10 | Miscellaneous | |
| 11 | Next | |
| 12 | Unknown | |
| 13 | Baptism (LDS) | BAPL |
| 14 | Confirmation | CONF |
| 15 | Bar mitzvah | BARM |
| 16 | Adopted | |
| 17 | Extra 4 | |
| 18 | First communion | FCOM |
| 19 | Extra 0 | |
| 20 | Probate | PROB |
| 21 | Endowment (LDS) | ENDL |
| 22 | Passenger | |
| 23 | Extra 1 | |
| 24 | Immigration | IMMI |
| 25 | Graduation | GRAD |
| 26 | Sealed to Parents (LDS) | SLGC, SLGP |
| 27 | Retirement | RETI |
| 28 | Extra 2 | |
| 29 | Joined | |
| 30 | Sealed to Spouse (LDS) | SLGS |
| 31 | Divorce | DIV |
| 32 | Annulment | ANUL |
| 33 | Baptism | BAPM |
| 34 | Bas mitzvah | BASM |
| 35 | Cremation | CREM |
| 36 | Education | EDUC, _DEG |
| 37 | Emigration | EMIG |
| 38 | Engagement | ENGA |
| 39 | Religion | RELI |
| 40 | Will, testament | WILL |
| 41 | Blessing | BLES, BLSL |
| 42 | LDS confirmation | CONL |
| 43 | Marriage banns | MARB |
| 44 | Marriage contract | MARC |
| 45 | Marriage license | MARL |
| 46 | Marriage settlement | MARS |
| 47 | Naturalization | NATU |
| 48 | LDS Ordinance | ORDN |
| 49 | LDS Ordination | ORDI, ORDL |
| 50 | ILLN | |
| 51 | Travel | TRAV |
| 52 | Military award | MILI_AWA |
| 53 | ELEC, _ELEC | |
| 54 | HONR | |
| 55 | Stillbirth | |
| 56 | User-defined event | EVEN, FACT, ILLE, _ATTR, DATE |
| 57 | User-defined marriage event | MEVEN |
| 59 | Fact | |
| 60 | FTM Fact 1 | _FA1 |
| 61 | FTM Fact 2 | _FA2 |
| 62 | FTM Fact 3 | _FA3 |
| 63 | FTM Fact 4 | _FA4 |
| 64 | FTM Fact 5 | _FA5 |
| 65 | FTM Fact 6 | _FA6 |
| 66 | FTM Fact 7 | _FA7 |
| 67 | FTM Fact 8 | _FA8 |
| 68 | FTM Fact 9 | _FA9 |
| 69 | FTM Fact 10 | _FA10 |
| 70 | FTM Fact 11 | _FA11 |
| 71 | FTM Fact 12 | _FA12 |
| 72 | FTM Fact 13 | _FA13 |
| 73 | Marriage fact | |
| 75 | Email address | EMAL, _EMAIL, _EMAL |
| 76 | Medical | _MDCL |
| 77 | Military service | MILI, _MILT |
| 78 | Description | DSCR |
| 79 | Nationality | NATI |
| 80 | Fax phone no. | _FAX |
| 81 | URL | URL |
| 82 | PROP | |
| 83 | _NAMS | |
| 84 | IDNO | |
| 85 | Social Security Number | SSN |
| 87 | Association | ASSO |
| 88 | Caste | CAST |
| 89 | Adult christening | CHRA |
| 90 | Number of children | NCHI |
| 91 | Number of marriages | NMR |
| 92 | Divorce Filing | DIVF |
| 93 | Title | TITL |
| 94 | LDS Initiatory | WAC |
| 95 | Photo | |
| 96 | Relationship | |
| 97 | DNA Test |
Examples
Refer to this SQL Tutorial for info & examples. Enter the commands in the “SQL Query” box, or DB Browser’s “Execute SQL” tab. See also SQLite Documentation (click on SELECT).
Click on “Save” to save as a CSV or Excel XLSX file. Click on “Clipboard” to copy results to Clipboard.
[1] To find everyone born between 1950 and 1952:select PERSONS.NAME, EVENTS.FROM_DATE, PLACES.PLACE_NAME
from ((EVENTS inner join PERSONS on EVENTS.PIN = PERSONS.PIN) inner join PLACES on EVENTS.PLACE_NOB = PLACES.PLACE_NO)
where EVENTS.FROM_DATE >= 19500000 and EVENTS.FROM_DATE <= 19520000 and EVENTS.EV_TYPE == 1
select PERSONS.NAME from PERSONS where PERSONS.FATHER_PIN = 0 and PERSONS.MOTHER_PIN = 0
select PERSONS.NAME, EVENTS.FROM_DATE
from EVENTS
inner join PERSONS on EVENTS.PIN = PERSONS.PIN
where (EVENTS.FROM_DATE % 10000) / 100 = 7 and EVENTS.FROM_DATE % 100 = 4 and EVENTS.EV_TYPE == 1
select distinct PERSONS.NAME, IMAGES.FILENAME from IMAGES inner join PERSONS on IMAGES.PIN=PERSONS.PIN group by IMAGES.FILENAME order by IMAGES.FILENAME, IMAGES.PIN
select PERSONS.NAME, EVENTS.FROM_DATE, PLACES.PLACE_NAME from ((EVENTS inner join PERSONS on EVENTS.PIN = PERSONS.PIN) inner join PLACES on EVENTS.PLACE_NOB = PLACES.PLACE_NO) where EVENTS.PLACE_NOB = 1122
select PLACES.PLACE_NAME from EVENTS inner join PLACES on EVENTS.TO_DATE = PLACES.PLACE_NO where EVENTS.DATE_TYPE = 8
You can create new tables to extract & combine results for more sophisticated reports and analysis with “Insert into“.
[7] Count all menselect count(*) from PERSONS where SEX = 1
select PIN, NAME from PERSONS where SEX < 1 or SEX > 2
First, run these commands once each time the GEDCOM file is re-imported:
create table COUPLES (HUSB int, WIFE int)
Click “Execute”
INSERT INTO COUPLES (HUSB,WIFE)
select EVENTS.PIN, EVENTS.EV_SEQNO
from EVENTS inner join PERSONS on PERSONS.PIN = EVENTS.PIN and PERSONS.SEX =1 and EVENTS.EV_TYPE = 2
where EVENTS.DUAL_KEY = 1
Click “Execute”
Next, run the following command as often as desired:
select
HUSB as Husband_PIN,
(select PERSONS.NAME from PERSONS where PIN = HUSB) as NAME,
WIFE as Wife_PIN,
(select PERSONS.NAME from PERSONS where PIN = WIFE) as NAME,
(HUSB || ',' || WIFE ) as Marriage_PINs
from COUPLES
where Marriage_PINs not in
(select distinct (FATHER_PIN || ',' || MOTHER_PIN) from PERSONS)
The special alias “NAME” causes the name to be formatted correctly.
If there are several people with the same name, press Ctrl + G in Charting Companion, and enter the PIN to go to that person.
[10] All couples where “Smith” married “Jones”select P1.NAME, P2.NAME
from ((EVENTS inner join PERSONS as P1 on EVENTS.PIN = P1.PIN)
inner join PERSONS as P2 on EVENTS.EV_SEQNO = P2.PIN)
where P1.NAME like "%SMITH%" and P2.NAME like "%JONES%"
Admittedly these are not trivial, but the Charting Companion Facebook User Group might include some SQL gurus who can be of assistance.
Passenger lists, Ship’s Manifests, Border Crossings, Customs & Immigration forms are all rich sources of ancestor information. Here are a few Websites wit extensive information to help you tell the story of your family.
Genetic research offers new ways for genealogists to build their family tree, where conventional records and sources are not available.
Genealogists are submitting DNA samples to testing companies who can identify unique patterns in the DNA, and match the genealogist to millions of other participants.
Adoptees are using DNA to search for biological parents. Distant cousins and other relatives can contribute to your tree by providing information on branches where they have more research than you do.
The DNA Matrix helps you by showing an overview of DNA matches in a clear, simple chart. Charting Companion can also “validate” your tree by comparing actual DNA test results, against the number expected from the relationships. The percentage ranges for each type of relationship are based on Leah LaPerle Larkin’s excellent blog “The DNA Geek“.
Are there surprises in your tree?
Charting Companion works with all genealogy programs: Family Tree Maker, RootsMagic, Legacy, Ancestral Quest, Family Historian, GEDCOM, etc.
Get Charting Companion today and make family history.
Ex: Melvin Smith and Zachary Smith are first cousins (“1C”). They share 800 centiMorgans (a unit of length of chromosomes), which is typical of this relationship 95% of the time.
In one of their tests, the half-siblings Percival Smith and Conchita Smith share 70 cM, which is unusually low.
Percival shares 2400 cM with his nephew Zachary, which is too high and could hint at a closer biological relationship. Did Percival consort with Zoë?
Click to see sample of DNA Matrix.
If you hover the cursor over a cM or relationship on the blue line, Charting Companion will display the chromosome segments that two people have in common:
Click on multiple boxes to see the segments that three or more people share:
Researching your family history and building a family tree chart can be a fulfilling pastime. It helps you learn your family’s story and understand yourself a little better in the process. But how can you get the best out of the many online resources and software programs now available?
Below, we’ve looked at all the ways online resources make it easier to build and share your family tree. See below for more information.
Benefits of Using Online Family Tree Software
Online genealogy offers several benefits for individuals interested in exploring their family history. Some of the key benefits include:
- Access to vast resources: Online platforms provide access to genealogical records, databases, and archives from around the world. This allows individuals to access more information and increases the chances of discovering new branches of their family tree.
- Convenience and flexibility: Online genealogy platforms enable people to conduct research from the comfort of their own homes. There’s no need to visit physical archives or libraries. In fact, digitized records are now available online. This convenience and flexibility make it easier for individuals to fit genealogical research into their busy lives.
- Collaboration and sharing: Online genealogy platforms often feature social and collaborative elements. This helps users connect with other researchers, share information, and collaborate on family history projects. It can be incredibly valuable for sharing insights, tips, and resources, and even connecting with distant relatives.
- Organization and documentation: Online platforms typically provide tools and features to help individuals organize and document their genealogical research. These tools may include family tree builders, note-taking capabilities, and file storage options. Such features streamline the research process and make it easier to keep track of findings.
- DNA testing and matching: Many online genealogy platforms offer DNA testing services. This allows individuals to uncover genetic connections and find potential relatives. Users can discover previously unknown branches of their family and connect with relatives they may not have known existed.
- Education and learning: Online genealogy platforms provide educational resources, tutorials, and guides. This is to help users learn more about genealogical research methods, techniques, and historical contexts. These resources can be valuable for both beginners and more experienced researchers looking to expand their knowledge.
- Preservation of family history: Online genealogy platforms provide a means to preserve and share family history for future generations. Digitization of genealogical records can make family history easily accessible to future descendants.
Note: It’s always a good idea to verify the information and corroborate findings using multiple sources to ensure accuracy.
Finding the Right Genealogy Program
You can start your family tree by speaking with family members and using your own resources. However, at some point, it will be helpful to turn to online resources. These sources can offer even more information for your genealogical research.
There are two main types of websites that you can use for research. These are:
- Websites that store primary sources of information, such as vital statistics (births, deaths, marriages), censuses, military, property, wills & testaments, etc.
- Websites where you can record your family members and lineages.
Some websites offer both facilities, others only one or the other. They will both be useful to you.
The three major websites of the second type, where you can record your family history are:
- FamilySearch.org: Membership is free. All family trees are public. Anyone can edit them.
- Ancestry.com: Membership ranges from $264–$960 per year. Family trees are private by default.
- MyHeritage.com: Has a free option and paid plans ranging from $129–$269 per year. Trees are also private by default.
All three of these organizations offer extensive primary sources digitized from archives and governments all over the world. They also offer a family tree maker online that you can utilize.
Using Genealogy Software
There are two ways to use these websites. You can store all your data directly in the cloud on their servers, or you can use a desktop program that “synchronizes” periodically with the website. The latter approach gives you more autonomy and independence.
The RootsMagic and Family Tree Maker desktop programs can synchronize with FamilySearch.org and Ancestry.com. Ancestral Quest synchronizes with FamilySearch.org.
The advantage of cloud-based genealogy is that it makes you more likely to connect with people who share your ancestry. This means connecting with people who have already done the research for you.
You can acquire huge chunks of your family tree with no effort. The benefit is that you are free to pursue new avenues that are yet unexplored contributing to both family trees.
There are countless websites offering images of original documents, photographs, as well as indexes and transcriptions. Many specialize by geographic region or period in history. In addition to the three sites above, here are some other helpful sites.
- General, comprehensive lists:
- FamilySearch (FamilySearch.org)
- Ancestry (Ancestry.com)
- MyHeritage (MyHeritage.com)
- Cyndi’s List (www.cyndislist.com/) is a compendium of everything genealogical.
- National Genealogical Society (www.ngsgenealogy.org/free-resources/websites/). This is very comprehensive, although not complete because they don’t mention paid sources such as Ancestry.
- Genealogy Online (https://www.genealogieonline.nl/en/)
- Find My Past (www.findmypast.com)
- WikiTree (www.wikitree.com)
- Archives (archives.com)
- The Ancestor Hunt (theancestorhunt.com)
- US GenWeb (www.usgenweb.org)
- Access Genealogy (accessgenealogy.com)
- WorldGenWeb Project (www.worldgenweb.org)
- RootsWeb (wiki.rootsweb.com/wiki/index.php/Main_Page)
- Geneanet (en.geneanet.org)
- Introduction to genealogy:
- Family Tree Magazine (familytreemagazine.com) for beginners.
- ThoughtCo (thoughtco.com/genealogy-4133308) Good introduction.
- Family History Fanatics (www.familyhistoryfanatics.com/online-genealogy-search-tips)
- Specialized sources:
- United States National Archives (www.archives.gov/research/genealogy)
- United States Library of Congress (chroniclingamerica.loc.gov)
- Fold3 (www.fold3.com) Military personnel.
- Allen County Public Library (acpl.lib.in.us/genealogy)
- David Rumsey Map Collection (www.davidrumsey.com)
- FreeBMD (www.freebmd.org.uk)
- Dead Fred (deadfred.com) photos
- Social Security Death Index (ssdi.rootsweb.ancestry.com/)
- Guild of One-Name Studies (one-name.org)
- Immigrant Ancestors Project (immigrants.byu.edu/main_page)
- Burial sites & cemeteries:
- Find a Grave (www.findagrave.com)
- Billion Graves (billiongraves.com)
- Newspapers:
- Genealogy Bank (www.genealogybank.com) Newspapers.
- Newspaper Archive (newspaperarchive.com)
- Newspapers by Ancestry (www.newspapers.com)
- Elephind (elephind.com)
- These sites specialize by country or ethnic origin:
- Irish Genealogy (www.irishgenealogy.ie/en/)
- Jewish Gen (www.jewishgen.org)
- American Ancestors (www.americanancestors.org)
- Daughters of the American Revolution (www.dar.org)
- Ellis Island (Ellisisland.org)
- Italy (Italiangen.org)
- UK & Ireland genealogy (genuki.org.uk)
- National Archives of Ireland (genealogy.nationalarchives.ie)
- UK National Archives (www.nationalarchives.gov.uk)
- UK Census Records (www.freecen.org.uk)
- UK Parish Registers (www.freereg.org.uk)
Notes On Creating a Family Tree
When you incorporate information in your genealogy from web-based sources, always include citations to the website.
A reference to the source will be important if you are questioned by someone who is collaborating with you. It will also help if you encounter contradictory information somewhere else. The latter is unfortunately a not uncommon occurrence.
Names were often misspelled when people were illiterate in the past. There may also have been incentives to misrepresent one’s age. For example, when enrolling in the military, getting married, or applying for a pension.
When researching your family history, always work from the known towards the unknown. Start with your immediate family, and search for ancestors one generation at a time going towards the past. Do not start with a distant, ancient namesake and try to work your way toward the present. The latter approach will cost you much time and lead to many dead ends.
When researching foreign sources, consider hiring a professional genealogist specializing in the country or area where your ancestors came from. These specialists are familiar with the sources, the archives, the libraries, and the government offices.
They can perform much quicker than you can. Make note of which sources they have consulted that have not resulted in any leads. You will then be able to avoid revisiting these sources in the future.
You could get a temporary subscription to a website, extract the information you need, then discontinue the subscription. However, you can benefit any time someone else adds information that complements your own when using a family tree subscription. It’s the gift that keeps on giving.
Final Thoughts
Online resources for family tree templates are abundant these days. So, using the right software, or a combination of software, is a really easy way to create professionally designed and comprehensive family trees.
We’ve included all our own tried and tested methods in this post. Why not check out some of our recommendations and begin building your family tree today?
Looking for family tree software online? Check out the Progeny Genaology products now to find a family tree maker online that suits you.