Can't remove duplicate from union query

[baileywerner]

Hi!



I've created a union query which pulls data from seven tables. The resulting query contains four fields: StreetNumber, StreetName, Longitude, and Latitude.

For some reason, one of the records in the union query is duplicated and I cannot for the life of me figure out why. Two of the seven tables contain this address, but the address is completely identical in both tables.

We've narrowed down the issue to the latitude field-- if this field is excluded, the duplication disappears. However, the latitude for this address in both tables appear to be completely identical. Even copying the latitude from one table to the other does not solve the problem. Replacing the latitude with 99999 in both tables also does not solve the problem (we are then left with two 99999 records in the union query).

Here's the relevant piece of the union query code, if that helps (it's extremely straightforward, so I don't imagine this is the problem):

SELECT table1.Validated_StreetNumber, table1.Validated_StreetName, table1.Longitude, table1.Latitude
FROM table1


UNION

SELECT table2.Validated_StreetNumber, table2.Validated_StreetName, table2.Longitude, table2.Latitude
FROM table2;



Has anyone run into an issue like this before? Or have any ideas how to solve it? We've really tried anything we can think of.

Thank you!

[ssanfu]

It would help if you would post the dB with 10 records in each table to illustrate the problem.


And welcome to the forum......

[Micron]

Don't know how you copied from one to another, but make sure you don't have a multi-line record in one table.
I might create a query with both tables, equal join the latitude fields and put the criteria in against one of those fields. If they are not exactly equal, you'll get nothing when you run the query. Could be a hidden character too.

[isladogs]

Check that the latitude fields are the same number datatype. If one is single and the other double then the precision will differ and the stored numbers may not be identical.