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:300090
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
