using UNION to get unique records, not working

[dharkangel]

Hi,

I tried to use a UNION query to get rid of duplicates between two tables. The problem is, each table has duplicates of their own and they are still showing up. I am using the SiteAddress field as the one to determine if there are duplicate records. The site address field contains home address, ie. 7876 somewhere lane....

I verified that the spellings were the same so that is not the problem. Basically I need to pull only the unique records from the two tables to make one list comprised of only unique records.

thanks in advance,

here is my query:

SELECT getLandsAmerica.SiteAddress, getLandsAmerica.Parcel, getLandsAmerica.PageGrid, getLandsAmerica.Owner, getLandsAmerica.SiteCityState, getLandsAmerica.SiteZipCode, getLandsAmerica.MailAddress, getLandsAmerica.MailCityState, getLandsAmerica.MailZipCode, getLandsAmerica.TrusteeName, getLandsAmerica.TrusteeAddress, getLandsAmerica.TrusteeCity, getLandsAmerica.TrusteeState, getLandsAmerica.TrusteeZipCode, getLandsAmerica.TrusteePhone, getLandsAmerica.LenderName, getLandsAmerica.LenderAddress, getLandsAmerica.LenderCityState, getLandsAmerica.LenderPhone, getLandsAmerica.LenderZipCode, getLandsAmerica.DefaultNumber, getLandsAmerica.RecDate, getLandsAmerica.DelqAmt, getLandsAmerica.AsOf, getLandsAmerica.LoanNumber, getLandsAmerica.RecDate2, getLandsAmerica.LoanAmt, getLandsAmerica.SaleNumber, getLandsAmerica.SqFt, getLandsAmerica.LotSize, getLandsAmerica.Garage, getLandsAmerica.Use, getLandsAmerica.Zone, getLandsAmerica.LandValue, getLandsAmerica.ImpValue, getLandsAmerica.AsdValue, getLandsAmerica.LastSold, getLandsAmerica.LastAmt, getLandsAmerica.YearBuilt, getLandsAmerica.Bedrooms, getLandsAmerica.Bathrooms, getLandsAmerica.Units, getLandsAmerica.TDNumber, 'LandsAmerica' AS Type
FROM getLandsAmerica
UNION ALL SELECT getUnitedTitle.SiteAddress, getUnitedTitle.Parcel, getUnitedTitle.PageGrid, getUnitedTitle.Owner, getUnitedTitle.SiteCityState, getUnitedTitle.SiteZipCode, getUnitedTitle.MailAddress, getUnitedTitle.MailCityState, getUnitedTitle.MailZipCode, getUnitedTitle.TrusteeName, getUnitedTitle.TrusteeAddress, getUnitedTitle.TrusteeCity, getUnitedTitle.TrusteeState, getUnitedTitle.TrusteeZipCode, getUnitedTitle.TrusteePhone, getUnitedTitle.LenderName, getUnitedTitle.LenderAddress, getUnitedTitle.LenderCityState, getUnitedTitle.LenderPhone, getUnitedTitle.LenderZipCode, getUnitedTitle.DefaultNumber, getUnitedTitle.RecDate, getUnitedTitle.DelqAmt, getUnitedTitle.AsOf, getUnitedTitle.LoanNumber, getUnitedTitle.RecDate2, getUnitedTitle.LoanAmt, getUnitedTitle.SaleNumber, getUnitedTitle.SqFt, getUnitedTitle.LotSize, getUnitedTitle.Garage, getUnitedTitle.Use, getUnitedTitle.Zone, getUnitedTitle.LandValue, getUnitedTitle.ImpValue, getUnitedTitle.AsdValue, getUnitedTitle.LastSold, getUnitedTitle.LastAmt, getUnitedTitle.YearBuilt, getUnitedTitle.Bedrooms, getUnitedTitle.Bathrooms, getUnitedTitle.Units, getUnitedTitle.TDNumber, 'UnitedTitle' AS Type
FROM getUnitedTitle;

 

[dhookom]

First thing to get rid of is "UNION ALL" since this will include all records from both tables regardless if there are duplicates. Any uniqueness in any field will cause duplicates in other fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Golom]

As Duane said, UNION ALL doesn't eliminate duplicates but UNION does.

Two or more records are duplicates however, only if they have identical values in EVERY field. You may have duplicate values for the SiteAddress field if there are differences in any of the other fields.

 

[dharkangel]

I got rid of ALL but the duplicates are still appearing.

 

[dhookom]

Go back and read both responses and then verify in your data that you have records with the same values in all fields that appear more than once.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dharkangel]

Golom was correct, there are duplicates in the SiteAddress field but different values for other fields. How would you suggest I now solve this problem? I guess UNION is not going to do it for me.

 

[dhookom]

Either remove the SiteAddress field or UNION some totals/group by queries the find the first of the SiteAddress.

This all depends on your requirements.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Golom]

You have multiple other fields associated with a particular SiteAddress and you need to either eliminate them from your query or select which of the several available values for those fields that you want to see.

 

[dharkangel]

If I understand what Golom is suggesting, this is going to be really difficult to do since the rest of the fields are text fields, so there could be any number of values that I cannot predict. I was just trying to produce a combined report from these two tables and eliminate the duplicates that exist between the two. I think I have to think about this some more, i thought maybe the UNION would have been quick and easy.

 

[lespaul]

so if you have:

SiteAddress otherfield1 otherfield2 otherfield3
123 Main st something nothing anything
123 Main st doodah thingee whatchamacallit


does it matter if you take the first one or the last one? What determines which record you want to keep?

What you are doing is a plausible way to get the information, you just need to decide which of the multiple records you want to keep. If it doesn't matter you can use FIRST.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[dharkangel]

THANK YOU!! I definitely have to get this requirement from my client, I don't have it, it completely makes sense now.