Larry LTools has a wide selection of SQL routines.
Ron Ferguson Sent from my Xperia™ smartphone Larry Lee <[email protected]> wrote: >I renamed this to SQL Queries Library. > >I know what SQL Queries are but I do not know how they are used with >Legacy and if they would have much demand. Is this something that is >appropriate for only advanced users? > >Jay, if you feel this would be useful maybe you could maintain an online >library for these as I doubt Millennia would officially sanction & support >them. > >Larry >On Jul 16, 2014 2:24 PM, "Jay 1FamilyTree" <[email protected]> >wrote: > >> Ron, >> >> Not sure why your emails keep ending up in my spam folder.......... >> >> >> Anyway, there is a reason, >> Many years ago I had Dennis at LTools create a process to automatically >> add spouses and parents to my database. >> (see the Create spouses option in LTools) >> That worked fine, but as the years have gone by, I realized that the >> process was a bit too much. >> >> I should have limited it to specific descendant lines and not the entire >> database. >> So, I have these parents out there, most all are UnknownM (Surname) + >> UnknownF Unknown that are not really needed, as maybe they are the parents >> of an aunt or cousin. >> I want to 'trim' those from the database. >> >> But, some of the parents have a second child married into the database, in >> which case I want to keep them >> (in order to preserve the relationships) >> Hence, the search for parents with only one child. >> >> Your code worked well and provides a list from which I can use to review >> and delete the couple if appropriate. >> Which just helps to keep my database relevant and compact. >> >> >> I really wish Legacy would have a central page where the various SQL >> queries could be posted for all to share and use. >> >> Thanks for your help!! >> >> Jay >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> On Fri, Jul 11, 2014 at 7:01 AM, Ron Taylor <[email protected]> wrote: >> >>> Jay, >>> I'm wondering what prompted you to search for this combination. What are >>> you trying to solve? >>> Ron Taylor >>> >>> >>> On Wednesday, July 9, 2014 8:33 AM, Ron Taylor <[email protected]> >>> wrote: >>> >>> >>> This one could be fun. >>> 1) Clear tag1 >>> 2) use Legacy>Search>Find>Miscellaneous>Linked individuals with no >>> parents then click "Clear List before this search" and click "Create List". >>> When then search results list is displayed, click Options>Advanced >>> Tagging>Tag and make changes to tag1. Click Everyone in Search List. Now >>> all individuals without parents have tag1. >>> 3) Create and save the following query which will be used in step4. Name >>> it qtblMR_parents_eol >>> >>> SELECT DISTINCT tblCR.IDMR >>> FROM ((tblCR INNER JOIN tblMR ON tblCR.IDMR=tblMR.IDMR) INNER JOIN tblIR >>> ON tblMR.IDIRHusb=tblIR.IDIR) INNER JOIN tblIR AS tblIR_1 ON >>> tblMR.IDIRWife=tblIR_1.IDIR >>> WHERE (((tblCR.IDMR)>0) AND ((tblIR.Tag1)>0) AND ((tblIR_1.Tag1)>0) AND >>> ((tblMR.IDIRHusb)>0) AND ((tblMR.IDIRWife)>0)); >>> >>> 4) Run this query to find the marriage records to answer your question. >>> >>> SELECT tblCR.IDMR, Count(tblCR.IDIR) AS Children >>> FROM qtblMR_parents_eol INNER JOIN tblCR ON qtblMR_parents_eol.IDMR = >>> tblCR.IDMR >>> GROUP BY tblCR.IDMR >>> HAVING (((Count(tblCR.IDIR))=1)) >>> ORDER BY tblCR.IDMR; >>> >>> Let me know if this is what you needed. Note you could tweak the >>> "Having" number of children as desired. >>> Ron Taylor >>> >>> >>> On Tuesday, July 8, 2014 8:33 PM, Jay 1FamilyTree < >>> [email protected]> wrote: >>> >>> >>> I would like to create a report of all parents that only have one child >>> AND the parents have no father or mother in my database. >>> >>> If any great sql coders could help me out, that would be great. >>> >>> Thanks >>> >>> Jay >>> >>> >>> >>> >>> Legacy User Group guidelines: >>> http://www.LegacyFamilyTree.com/Etiquette.asp >>> Archived messages after Nov. 21 2009: >>> http://www.mail-archive.com/[email protected]/ >>> Archived messages from old mail server - before Nov. 21 2009: >>> http://www.mail-archive.com/[email protected]/ >>> Online technical support: http://www.LegacyFamilyTree.com/Help.asp >>> Follow Legacy on Facebook (http://www.facebook.com/LegacyFamilyTree) and >>> on our blog (http://news.LegacyFamilyTree.com). >>> To unsubscribe: http://www.LegacyFamilyTree.com/LegacyLists.asp >>> >>> >>> >>> >>> >>> >>> Legacy User Group guidelines: >>> http://www.LegacyFamilyTree.com/Etiquette.asp >>> Archived messages after Nov. 21 2009: >>> http://www.mail-archive.com/[email protected]/ >>> Archived messages from old mail server - before Nov. 21 2009: >>> http://www.mail-archive.com/[email protected]/ >>> Online technical support: http://www.LegacyFamilyTree.com/Help.asp >>> Follow Legacy on Facebook (http://www.facebook.com/LegacyFamilyTree) and >>> on our blog (http://news.LegacyFamilyTree.com). >>> To unsubscribe: http://www.LegacyFamilyTree.com/LegacyLists.asp >>> >> >> >> >> Legacy User Group guidelines: >> http://www.LegacyFamilyTree.com/Etiquette.asp >> Archived messages after Nov. 21 2009: >> http://www.mail-archive.com/[email protected]/ >> Archived messages from old mail server - before Nov. 21 2009: >> http://www.mail-archive.com/[email protected]/ >> Online technical support: http://www.LegacyFamilyTree.com/Help.asp >> Follow Legacy on Facebook (http://www.facebook.com/LegacyFamilyTree) and >> on our blog (http://news.LegacyFamilyTree.com). >> To unsubscribe: http://www.LegacyFamilyTree.com/LegacyLists.asp >> > > > >Legacy User Group guidelines: >http://www.LegacyFamilyTree.com/Etiquette.asp >Archived messages after Nov. 21 2009: >http://www.mail-archive.com/[email protected]/ >Archived messages from old mail server - before Nov. 21 2009: >http://www.mail-archive.com/[email protected]/ >Online technical support: http://www.LegacyFamilyTree.com/Help.asp >Follow Legacy on Facebook (http://www.facebook.com/LegacyFamilyTree) and on >our blog (http://news.LegacyFamilyTree.com). >To unsubscribe: http://www.LegacyFamilyTree.com/LegacyLists.asp > Legacy User Group guidelines: http://www.LegacyFamilyTree.com/Etiquette.asp Archived messages after Nov. 21 2009: http://www.mail-archive.com/[email protected]/ Archived messages from old mail server - before Nov. 21 2009: http://www.mail-archive.com/[email protected]/ Online technical support: http://www.LegacyFamilyTree.com/Help.asp Follow Legacy on Facebook (http://www.facebook.com/LegacyFamilyTree) and on our blog (http://news.LegacyFamilyTree.com). To unsubscribe: http://www.LegacyFamilyTree.com/LegacyLists.asp
