Selecting Non-Duplicate Rows based on Column Conditions

[Samcjohnson]

I have two tables



Sample1D:

Customer_ID	Account_Name	Address	Leave_Date
1	Bob's Bikes	123 Main St	6/7/2013
2	Bob's Bikes Warehouse	123 Main St	6/7/2013
45	Sally's Salon	457 118th St	1/3/2014
19	Pedro's Pizza	843 Query Rd	9/2/2010

Sample2S:

Contact_Name	Account_Name	Address	Enter_Date
Jane Johnson	Jane's Bowling	123 Main St	10/9/2013
Tammy Barker	Bob's Boats Outlet	2900 8th St	7/29/2013
Bob Barker	Bob's Banana Boats	2900 8th St	7/29/2013
Sam Smith	Sam's Salads	457 118th St	8/3/2015
Dan Jackson	Dan's Donuts	843 Query Rd	9/2/2010

What i am trying to do is run a query to join the tables based on Address. When the addresses are the same, provide the relevant columns. I believe i altered someones code on this forum to select the most recent date row based on address and match it with the other table. The issue i am running into being new to Access SQL Queries is creating one that takes only one row. In some instances there will be two rows with the same recent Leave Date for one address. For my purposes, i just want one. So for example, the first two rows in Sample1D, have the same address and leave date but i only need one two match up with the Sample2S table. Likewise with the Sample2S table, If it has the same address twice with the same Enter Date, i only want one of them to match in the JOIN. So basically, i want ZERO duplicate addresses in the join. If it has different dates, select the most recent. If not, either is fine. I have searched far and wide and haven't found code that tailors to this.

I have tried using

  SELECT t1.*, Latest.*
FROM Sample2S t1 
INNER JOIN
(
SELECT C.* 
FROM SampleD1 C 
INNER JOIN 
 (SELECT Address, MAX(Leave_Date) as MyDate
  FROM Sample1D
  Group By Address 
 ) t2
 ON  C.Address= t2.Address
 AND C.Enter_Date = t2.MyDate
 
) Latest
 On Latest.Address = t1.Address

This however still provided duplicate Addresses in the result. (I believe due to having the same date)
I'm not sure If i'm close or miles away. Help is appreciated!

[June7]

Yes, how should the query know which is 'correct' - this is a tie so both are returned. Review http://allenbrowne.com/ranking.html

[Samcjohnson]

Hey June,

When they have the same date and address, either is correct. If I add a column and have a default primary key where it numbers every row, could i add an additional criteria which says if a tie, then return only the match with either a lower or higher ID?

[June7]

Review http://allenbrowne.com/subquery-01.html#TopN

Try:

SELECT * FROM table WHERE ID IN (SELECT Top 1 ID FROM table AS Dupe WHERE Dupe.Address = table.Address ORDER BY Dupe.Leave_Date DESC, Dupe.ID);

[Samcjohnson]

June7,

Awesome, so i tried that and it removes the duplicates. Does that first filter by leave date then ID or does it just give you the top ID for each address?

Also, would i do this to both of my tables to clear them up and only have unique address, and then from there try to join them?

[June7]

Sorts by Leave_Date and ID and pulls the top ID for each address.

This doesn't really 'clear up' the table. It is just applying a filter to data.

Join tables if you want but the 2900 8th St address is not in Sample1D. What is purpose of joining the datasets? Why are the account names so different? Location taken over by another business? What are you really trying to accomplish?

[Samcjohnson]

What i am trying to accomplish is have general account information for a location taken over by a new business. The new business information is what i mainly want. I need the data in Sample2S when it has a matching address with the first table, Sample1D. Because either table can sometimes have a duplicate due to old locations having two accounts or two new accounts moving into the same location, duplicates can occur. I just want one result for the new business in that location. This is the heart of the issue. This is the main Query.

The reason i wanted the joining data from Sample1D is because in a second query i like to archive the old account info that matches the new info. I would just combine the tables to archive what the old and new are. I really only wanted one result and if they have the same address and date, then it doesn't really matter too much because the date is more important than some of the other columns when there are two entries for the same date.

[June7]

Suggestion work? Issue resolved?

[Samcjohnson]

The suggestion worked well at creating nonduplicate addresses in the table Sample2S which is awesome. Issue is now returning these new unique addresses when the address is also in the first table Sample1D.

[June7]

INNER JOIN query to Sample1D on the address fields.

Of course, this assumes the addresses are exactly the same in both datasets. Not 123 Main St in one and 123 Main Street in other.

[Samcjohnson]

I try using the code given and then doing an inner join by using the code below

SELECT * 
FROM Sample2S
WHERE ID IN (SELECT Top 1 ID FROM Sample2S AS Dupe WHERE Dupe.Address = Sample2S.Address ORDER BY Dupe.Leave_Date DESC, Dupe.ID)
INNER JOIN Sample1D ON (Sample1D.Address = Sample2S.Address);

This produces a long syntax error. Is the code in the wrong arrangement?

[June7]

Why are you joining the 2 tables?

Save the suggested query as an object. Then use the Query Builder design view to help construct correct SQL that joins query to table.

If you really want a single all-in-one query, will have to make adjustment in the SQL View window to replace reference to query object with SQL statement. Something like:

SELECT Sample1D.*, Q1.* FROM Sample1D
INNER JOIN (SELECT * FROM table WHERE ID IN (SELECT Top 1 ID FROM table AS Dupe WHERE Dupe.Address = table.Address ORDER BY Dupe.Leave_Date DESC, Dupe.ID)) AS Q1
ON Sample1D.Address = Q1.Address;

[Samcjohnson]

I was joining because that was what the design view was saying in the SQL view. I tried replacing the Inner Join with the Where clause but it didn't seem to work.

But the code you gave me worked great. I did the code that gave me non-duplicate values,

SELECT * FROM table WHERE ID IN (SELECT Top 1 ID FROM table AS Dupe WHERE Dupe.Address = table.Address ORDER BY Dupe.Leave_Date DESC, Dupe.ID);

I used that code for a query for one table, the same for a query for the other, and then created a combine query i did through design view.
I plan on just having a macro call the queries. Should this be a problem?

[June7]

I still don't understand why the query was constructed joining the tables. My instructions clearly said join query to table.

Why do you need to 'call' SELECT queries?

All you need to do is open the final one. Maybe bind a report to the query.

[Samcjohnson]

I didnt join the tables. What i did was used your code for each table on separate queries to create nonduplicate addresses. From there i joined the two queries together to get my end result of zero duplicate addresses. Don't i need to first call the queries to clear the data of duplicates before i call the combining join query?