For those of you who know how to use ACCESS with LEGACY data files, here's some interesting queries I've worked up. I Use '_______' instead of 'Unknown' or blanks. 1. To find possible misspellings: SELECT Surname, GivenName, HusbSurName, WifeSurName FROM (tblCR INNER JOIN tblIR ON tblIR.IDIR = tblCR.IDIR) INNER JOIN tblMR ON tblMR.IDMR = tblCR.IDMR WHERE Surname <> '_______' AND Surname <> HusbSurName AND SurName <> WifeSurName AND HusbSurName <> '' AND HusbSurName <> '_______' AND WifeSurName <> '_______'AND WifeSurName <> '' ORDER BY Surname; 2. To fill in fathers' names, possibly: SELECT Surname, GivenName, HusbSurName, WifeSurName FROM (tblCR INNER JOIN tblIR ON tblIR.IDIR = tblCR.IDIR) INNER JOIN tblMR ON tblMR.IDMR = tblCR.IDMR WHERE (HusbSurName = '' OR HusbSurName = '_______') AND (WifeSurName = '' OR WifeSurName = '_______') AND Surname <> '' AND Surname <> '_______' ORDER BY Surname; 3. To fill in fathers' names, possibly: SELECT Surname, GivenName, HusbSurName, WifeSurName FROM (tblCR INNER JOIN tblIR ON tblIR.IDIR = tblCR.IDIR) INNER JOIN tblMR ON tblMR.IDMR = tblCR.IDMR WHERE Surname <> '_______' AND Surname <> HusbSurName AND SurName <> WifeSurName AND WifeSurName <> '' AND WifeSurName <> '_______' AND (HusbSurName = '' OR HusbSurname = '_______') ORDER BY Surname; 4. To fill in mothers' names, possibly: SELECT Surname, GivenName, HusbSurName, WifeSurName FROM (tblCR INNER JOIN tblIR ON tblIR.IDIR = tblCR.IDIR) INNER JOIN tblMR ON tblMR.IDMR = tblCR.IDMR WHERE Surname <> '_______' AND Surname <> HusbSurName AND SurName <> WifeSurName AND HusbSurName <> '' AND HusbSurName <> '_______'AND (WifeSurName = '' OR WifeSurName = '_______') ORDER BY Surname; 5. To find children using their mother's surname: SELECT Surname, GivenName, HusbSurName, WifeSurName FROM (tblCR INNER JOIN tblIR ON tblIR.IDIR = tblCR.IDIR) INNER JOIN tblMR ON tblMR.IDMR = tblCR.IDMR WHERE Surname <> '_______' AND Surname <> HusbSurName AND SurName = WifeSurName ORDER BY Surname;
