I am trying to compare 2 tables "Customers" and "CustomersNew" to find matching addresses.
The objective is to identify an address that we have worked at previously.
I have been trying the below code
Code:
SELECT DISTINCT Customers.OrderDate, Customers.Address
FROM Customers RIGHT JOIN Customers_New ON Customers.Address = Customers_New.Address
ORDER BY Customers.Address;
This gives the desired results, but also includes other records from "CustomersNew" please refer to attached file.
Is there a way of just returning the duplicate records only.
SELECT DISTINCT Customers.OrderDate, Customers.Address
FROM Customers INNER JOIN Customers_New ON Customers.Address = Customers_New.Address
ORDER BY Customers.Address;
The query is matching addresses. You get 'duplicates' because one of the tables has multiple records for same address. The query is doing what it is designed to do but apparently not what you want.
Using DISTINCT does nothing because the OrderDate field makes each record unique.
SELECT DISTINCT Customers.Address, Customers_New.OrderDate AS NewOrderDate, Customers.OrderDate AS PreviousOrderDate
FROM Customers, Customers_New
WHERE (((Customers.Address)=[Customers_New].[Address]) AND ((Customers.OrderDate)<>[Customers_New].[OrderDate]))
ORDER BY Customers.Address, Customers_New.OrderDate;
Similar idea to the above but using an inner join rather than a Cartesian join (so should be faster)
Code:
SELECT Customers.OrderDate, Customers.Address
FROM Customers INNER JOIN Customers_New ON Customers.Address = Customers_New.Address
WHERE (((Customers_New.OrderDate)<>[Customers].[OrderDate]))
ORDER BY Customers.Address;
This gives exactly the same results as the above post
Or omit the OrderDate field from the query results
Code:
SELECT DISTINCT Customers.Address
FROM Customers INNER JOIN Customers_New ON Customers.Address = Customers_New.Address
WHERE (((Customers_New.OrderDate)<>[Customers].[OrderDate]))
ORDER BY Customers.Address;
SELECT Customers.CustomerName, Customers.Address, Count(*)
FROM Customers INNER JOIN Customers_New ON Customers.Address = Customers_New.Address
Group By Customers.CustomerName, Customers.Address
ORDER BY Customers.CustomerName,Customers.Address;
SELECT Customers.OrderDate, Customers.Address
FROM Customers INNER JOIN Customers_New ON Customers.Address = Customers_New.Address
WHERE (((Customers_New.OrderDate)<>[Customers].[OrderDate]))
ORDER BY Customers.Address;
The objective is to run a query that will find matching addresses and show the history of work done at the address.
Is there a way of using the LIKE function as I have an issue where most of the addresses have comas, but occasionally an address doesn't have comas, isladogs code works perfectly but misses the addresses without comas.
I looked at majp's post again and yes you are correct, it is very similar, isladogs comment "Similar idea to the above but using an inner join rather than a Cartesian join (so should be faster)" caught my eye and followed that suggestion.
I just created a query in my DB with majp's code this also works perfectly, but still have the problem with the comas.
I am curious about using the LIKE function is it usable in majp or isladogs codes.
The queries look for matching addresses in both tables. If an address has no commas in both tables it will still match.
If an address has commas in one table but not the other, Access sees them as different...just as it would if the spelling changed.
Presumably commas could be missing in any record in either table...
One way of handling that would be to make two additional queries, each with an extra field Address1 with commas removed
1. qryCustomers
Code:
SELECT Customers.*, Replace([Address],",","") AS Address1
FROM Customers;
2. qryCustomersNew
Code:
SELECT Customers_New.*, Replace([Address],",","") AS Address1
FROM Customers_New;
Now make a third query based on those and joining by the Address1 field
Code:
SELECT qryCustomers.OrderDate, qryCustomers.Address1
FROM qryCustomers INNER JOIN qryCustomersNew ON qryCustomers.Address1 = qryCustomersNew.Address1
WHERE (((qryCustomers.OrderDate)<>[Customers_New].[OrderDate]))
ORDER BY qryCustomers.OrderDate, qryCustomers.Address1;
Or omitting the OrderDate field and using DISTINCT to avoid duplicate addresses (better in my opinion):
Code:
SELECT DISTINCT qryCustomers.Address1
FROM qryCustomers INNER JOIN qryCustomersNew ON qryCustomers.Address1 = qryCustomersNew.Address1
WHERE (((qryCustomers.OrderDate)<>[Customers_New].[OrderDate]))
ORDER BY qryCustomers.Address1;
Not very elegant but it will work. There may be a simpler solution. If anyone else wants to suggest an alternative, that's fine by me.
Do bear in mind that doing this is a 'band aid' to handle bad data.
However none of that will help if the address spellings differ in any way. That will definitely happen due to human error. Obviously a much better solution would be to ensure neither commas or spelling were an issue by using a unique number ID field for each address and using that in your table join instead.
I suggest you change your table structure to reduce the risk of these issues.
For example, store the addresses in a separate table tblAddresses each with an auto number AddressID primary key field.
You would also be better splitting the address into several separate fields
I would also have a unique CustomerID autonumber field and store First name and last name in separate fields
