I have a system of color coding all persons in my data base. I also have defined a fact call _color which I would like to add to each person to reflect their current color coding. Once that is done, I can use color coding for other purposes and still would be able to revert to my color coding system.
So I would like to be able to take all people and add a _color fact reflecting their current color coding.

What I want is a list (report) of everyone in my database with the following information:

Person’s RefNo, Person’s Name, Spouse’s RefNo, Spouse’s Name, Father’s RefNo, Father’s Name, Mother’s RefNo & Mother’s Name

For example:

Person’s Spouse’s Father’s Mother’s
RefNo Name RefNo Name RefNo Name RefNo Name
H456 George Harris H717 Ed Harris L954 Edith Lister No spouse
J001 Joyce Smith M124 Fred Brown J002 Bill Smith G056 Mary Good Spouse, father & mother
M546 Harry Mead M856 Roy Mead Only a father
R563 Edith Royce W067 Angela Woodman Only a mother

If the person does not have a spouse then that information should be blank. The same thing goes for either of the person’s father or mother.

Can someone point me to an SQL query that might provide this kind of information? Right now, I am trying to maintain this information in an Excel spreadsheet but it is very labour-intensive and prone to errors and discrepancies. Any help on this would be greatly appreciated.

How do I find the Home Person who is not necessarily PersonId 1?
Cheers
Niall

I have looked at the Source queries but find all the ones with Citation Fields in them list all the fields together in one cell. I would like a query that lists the citation fields in separate columns. As the list of citation fields can be different between any two sources the query would have to be limited to the user specifying one of the following:

1. which Source Template to run the query for eg Basic Ancestry Database Template ( where I have multiple Master Sources based on a single Source Template it would include all of those sources), or
2. which specific Master Source for eg My English Birth Certificates (from the Master Source List).
Fields required:

RecNo, Source Name, Surname, Given Name, OwnerID, Event Type, Cit Field 1, Cit Field 2, Cit Field 3,….etc, Citation Quality

Any help would be appreciated.

thanks
Anne-Marie

Hi folks!

I work with DBs for a few years now, but the desing of the RM6 DB is not clear for me. And I’m not in genealogy – I do this for my wife.

Currently I work on a program that imports a RM DB and generates a graphical view of the relationships for direct ancestors. But I have trouble always detecting the correct parents, and this is where I need your assistance.

I have a name, and using nametable.ownerid, I go to the person. Then the problem begins. There are at least three different tables where I could look up the parents:
childtable ( … where childtable.childid = persontable.personid) (I dont belive this table really helps me)
familytable ( select fatherid, motherid from persontable where familytable.childid = persontable.personid)
parenttable (select parentid from persontable where persontable.personid = nametable.ownerid).

Ok, enough confusion.

What’s the correct way to look up a person’s parents?

Thank you four your help!

I have a good number of people where one of the spouses is unknown (usually the wife). When I imported to RM (from The Master Genealogist) a separate unknown spouse was assigned to each of the children. For instance, in TMG I had John Smith with 1 unknown wife and 5 children. In RM I have John Smith with 5 unknown wives, each with 1 child. I need a SQLite that will merge the children under the known spouse and combine all the unknown spouses.

Thanks!

In entering names in the suffix field for Alternate Name with name type Married, I entered some names like ( Doe Sally). With a space between the parenthesis and the D. I did the following query and got the people I wanted:
Select suffix
from nametable
where suffix like ‘( %’;

A space between the parenthesis and the percent.

So I created the following update query:
update nametable
set suffix = ‘(%’
where suffix like ‘( %’;

the problem is it wiped the names out and put (% in the suffix field.

What should the update query have been?
What query can I run to make it (Doe Sally) again?

When I run the following query:
Select suffix
from nametable
where suffix like ‘(%’;

I get the following results:
(Doe Sally)
(%
(White Snow)

I want the entries with (% fixed. Of course it would be OK to update all suffix entries because I am sure there may be some typos.

If I wanted to change the suffix name to (Sally Doe) what would the update query be? I do have husbands who have no wives and wives that have no husbands. I do not want a suffix entry in those.

I have 34 marriage records in my RM file with a sentence that begins with “[couple] married on…” instead of the spouse’s names. All affected marriages were from records added in Ancestry and pulled into RM. I’ve narrowed down the problem in the data: there are marriage events for each of the spouses, but the OwnerType is 0, not 1, and the FamilyID is 0 instead of the FamilyID for the couple.

Below are the changes I developed. I’ve verified the data afterwards, by data dumps and printing the narrative reports. I had to do this as a multi-step process; there may be a more way to code the SQL but I’m sure this worked.

— test table development below
— create temporary table to hold problem records

DROP TABLE IF EXISTS TesttableEventTable ;

CREATE TEMP TABLE TesttableEventTable AS
Select *
FROM EventTable
where EventType = 300
and OwnerType <> 1
Order by Date;

— this SQL Identifies the two rows where individuals also have a valid marriage record (these records to be deleted)
— and stores the data into a temporary testing table:
DROP TABLE IF EXISTS TestEventsToDELETE
;
CREATE TEMP TABLE TestEventsToDELETE AS

select * from TestTableEventTable T
Where T.OwnerID in
(Select F.FatherID from FamilyTable F
INNER JOIN EventTable E on F.FamilyID = E.OwnerID
AND E.OwnerType = 1
And E.EventType = 300
)
UNION
select * from TestTableEventTable T
Where T.OwnerID in
(Select F.MotherID from FamilyTable F
INNER JOIN EventTable E on F.FamilyID = E.OwnerID
AND E.OwnerType = 1
And E.EventType = 300
)

;
— END this SQL stores the above data into a temporary testing table

— this SQL stores the IDs to be updated into a temporary testing table:
DROP TABLE IF EXISTS TestEventsToUPDATE
;
CREATE TEMP TABLE TestEventsToUPDATE AS

Select EventID
FROM TesttableEventTable
where (EventType = 300
and OwnerType <> 1)
AND EventID not in (Select EventID from TestEventsToDELETE)
GROUP BY Date
HAVING ( COUNT (Date) > 1)
ORDER BY Date;
— END this SQL stores the IDs to be updated into a temporary testing table

— this SQL creates a second test table to run tests against and compare to first:
DROP TABLE IF EXISTS TesttableEventTable2
;
CREATE TEMP TABLE TesttableEventTable2 AS
Select *
FROM EventTable
where EventType = 300
and OwnerType <> 1
Order by Date;
— END this SQL creates a second test table to run tests against and compare to first

DROP table if exists TestEventsChangeData;

CREATE TEMP TABLE TestEventsChangeData
AS
select TETU.EventID,
TTET.OwnerID,
F.FamilyID
from TestEventsToUPDATE TETU, TestTableEventTable TTET, FamilyTable F
where TETU.EventID = TTET.EventID
And ((F.FatherID = TTET.OwnerID) OR (F.MotherID = TTET.OwnerID))
;

— These three steps, run in order, solve the problem:

BEGIN TRANSACTION;

— set OwnerID in EventTable to Family ID from FamilyTable
UPDATE EventTable

SET
OwnerID = (SELECT TestEventsChangeData.FamilyID
FROM TestEventsChangeData
WHERE TestEventsChangeData.EventID = EventTable.EventID)
WHERE
EXISTS (
SELECT *
FROM TestEventsChangeData
WHERE TestEventsChangeData.EventID = EventTable.EventID
)
;

— set OwnerType in EventTable to 1 instead of 0
UPDATE EventTable
SET
OwnerType = (SELECT 1
FROM TestEventsChangeData
WHERE TestEventsChangeData.EventID = EventTable.EventID)
WHERE
EXISTS (
SELECT *
FROM TestEventsChangeData
WHERE TestEventsChangeData.EventID = EventTable.EventID
)
;

— Delete remaining invalid records
Delete from EventTable
where EventType = 300
and OwnerType <> 1
;

COMMIT;

I have accidentally merged two similar sources and now regret it!

This relates to a parish baptism, marriage and burial data for my local area. I set up separate free-form Sources for each of the three elements. Unfortunately I now have only two sources as I somehow managed to merge the baptism and burial templates.

For obvious reasons I only used the baptism Source for Birth or Baptism events and the burial Source for Death or Burial events.

It is a very simple job to recreate the two original source templates and as stated above it’s quite simple to identify which source should be used for each event.

Would it be possible to create a query which would find instances of the merged source and rewrite them (using the relevant event as key) using the correct Source template – without losing any of the individual detail data?

Thanks in advance for any help you can offer.I’m using RM version 7.5.7.0 BTW.