Finding events with no source citations; finding multiple events (SQL pt. 4)

Published on: 21 November 2023

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:

 

 

 

Share this page

Facebook Twitter Email