Did that work for you Mark? On Thu, Feb 28, 2008 at 11:26 AM, Sonny Savage <[EMAIL PROTECTED]> wrote:
> This should do it. There's a union and a left outer join. I hope this > works in Access, I've never used it... > /* First Get Centennial Members */ > SELECT FirstName > , LastName > , Address1 > , Address2 > , City > , Country > FROM tblCentennialMembers > > /* Now Get Members who are in the Foundation but aren't Centennial Members > */ > UNION > SELECT m.FirstName1 AS FirstName > , m.SurName1 AS LastName > , m.Add1 AS Address1 > , m.Add2 AS Address2 > , m.City > , m.Country > FROM tblMain m > LEFT OUTER JOIN tblCentennialMembers cm > ON m.FirstName1 = cm.FirstName > AND m.SurName1 = cm.LastName > WHERE m.Foundation = Yes > AND cm.FirstName IS NULL /* Removes Centennial Members Records from result > */ > > > On Wed, Feb 27, 2008 at 9:11 PM, Mark Henderson <[EMAIL PROTECTED]> wrote: > > > OK, so that was working, and those are the only two fields I want to > > union on, which is fine. But now I also want to add the address fields > > to the query result, without doing a union on those additional fields. > > Some addresses aren't entered quite right, and this will then create > > duplicates even though the query doesn't see them as such - does that > > make sense? > > > > So, for instance, when I do: > > > > SELECT FirstName1 AS FirstName, Surname1 AS LastName > > FROM tblMain > > WHERE foundation = Yes > > UNION > > SELECT FirstName, LastName > > FROM tblCentennialMembers; > > > > I get the result set I want. But if I do > > > > SELECT FirstName1 AS FirstName, Surname1 AS LastName. Add1, Add2, City, > > Country > > FROM tblMain > > WHERE foundation = Yes > > UNION > > SELECT FirstName, LastName, Address1, Address2, City, Country > > FROM tblCentennialMembers; > > > > It returns almost everything including the duplicates since add1 doesn't > > always match address1, even though the first and last names match. > > > > So, how to get the extra fields into the original result set while only > > doing a union on first and last names? > > > > > > Mark > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300114 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
