"Help to create a report on suffix"

[Sadolin]

Hi. Can anyone advise me, how to create a report for something specific and then at the same time exclude something other, which is spelled similar? My idea:

I would like to make a list of people, who have either "Baron" or "Baroness" in their suffix, but at the same time, I would like to exclude the people, who have "Baronet" in their suffix.

"Baron" is part of "baronet", and that is what gives the problem. I have tried various combinations, while creating such report, but I have not been able to exclude "baronet" from the report.

Kind regards, Nick

 

 

Edited November 10, 2015 by Sadolin

[bhemph]

Nick,
     If you post your SQL query or command that does this, it would be a little easier to give you the exact thing to add.  Adding something like (AND suffix <> 'Baronet') to the end of your command should end up excluding those.  If you want to do just specific titles, you could have = instead of LIKE and use OR between the titles that you want to find.

Brent

[Sadolin]

Dear Brent,

Thanks for your response to me. Among many variations, I have tried this, which does not work: 

SQL: SELECT tng_people.living, tng_people.private, lnprefix, prefix, suffix, tng_people.branch,prefix,firstname, lastname,suffix,birthdate,deathdate,if(sex='M',families1.marrdate,families2.marrdate),(if(sex='M',families1.wife,families2.husband)) as spouse,(if(sex='M',families1.wife,families2.husband)) as spouse, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) LEFT JOIN tng_families AS families1 ON (tng_people.gedcom = families1.gedcom AND tng_people.personID = families1.husband ) LEFT JOIN tng_families AS families2 ON (tng_people.gedcom = families2.gedcom AND tng_people.personID = families2.wife ) WHERE (suffix = "Baron" OR suffix = "Baroness" AND suffix != "Baronet") AND tng_people.gedcom = "tree1" ORDER BY prefix,lastname,firstname

Kind regard, Nick

 

[bhemph]

Nick,
     This shouldn't give you Baronet and shouldn't even need the AND suffix != "Baronet" part, since it specifically looks for just Baron or Baroness as the suffix.  But from the other reports that you have, it looks like it should be WHERE (suffix LIKE "% Baron %" OR suffix LIKE "%Baroness%")  There is a space between the % and Baron on each end so that it will select only the word Baron and not Baronet, but will also allow there to be a second title that is Baronet as in "the 1st Baron Beaverbrook and the 1st Baronet Aitken ".  I am guessing that you want him to show as a Baron, since he is the 1st Baron Beaverbrook but would not want him in the report if he was only the 1st Baronet Aitken.  The spaces on either side of Baron allow it to look for that word only and Baroness doesn't need this extra care to identify the word since it is not part of another that you don't want to have selected.  I hope this makes sense and the longer description of what we are doing and why will help you and/or others in the future when trying to select a word that is part of another word that is not wanted.

Brent

[Sadolin]

Dear Brent,

 

Thank you very much. It worked, and the SQL command now looks like this:

 

SELECT prefix,firstname, lastname,suffix,birthdate,deathdate,if(sex='M',families1.marrdate,families2.marrdate),(if(sex='M',families1.wife,families2.husband)) as spouse,(if(sex='M',families1.wife,families2.husband)) as spouse, tng_people.personID, tng_people.gedcom, nameorder FROM (tng_people ) LEFT JOIN tng_families AS families1 ON (tng_people.gedcom = families1.gedcom AND tng_people.personID = families1.husband ) LEFT JOIN tng_families AS families2 ON (tng_people.gedcom = families2.gedcom AND tng_people.personID = families2.wife ) WHERE (suffix LIKE "% Baron %" OR suffix LIKE "% Baroness %") AND tng_people.gedcom = "tree1" ORDER BY lastname,firstname

 

The report, which works, can be seen here: The Report , but it still needs a little further "makeup" in order to look like the other reports. Thanks again!

 

Kind regards, Nick

Edited November 11, 2015 by Sadolin