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
