
- OFFICE ADDRESS
- 10037 – 20 Silver Fox Ave. New Minas, Nova Scotia B4N 5K1 CANADA
Customer Support
Published on: 21 November 2023
The following operations will go a long way to help you clean up your genealogy data.
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.
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: