Distinct rows issue

[badger8]

I would like to only get back distinct rows but one of my select columns will prevent this from happening because one of my columns that I would like to include in the select is notes. Notes represents what list it was imported from. This means I may have two John smiths from the same company but was imported for two different lists.

How can I only get back distinct rows and still incluse notes as one of the selected columns in my final query.

need help

 

[tlbroadbent]

You can select First(Notes), Last(Notes), Min(Notes) or Max(Notes) in an aggregate query to return one row and one Notes column.

Example:

Select ColX, ColY, ColZ, First(Notes)
From Table1
Group By ColX, ColY, ColZ Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[badger8]

I tried doing your example that you gave me. Below is what I did:

SELECT First_Name, Last_Name, Account, notes, First_Name(notes)
FROM [Marketing lists]
Order BY Last_Name

Access didn't like the First_Name(notes).

Need help.

 

[badger8]

I would like to identify duplicate recordes and move them to a separate table. I just can't get it to work. I have multiple columns but the four columns that I will be looking at to see if the record has a duplicate is by the First_Name, Last_Name, Account, and City fields.

I would like to know the easiest way to do this if possible.

Thanks

 

[tlbroadbent]

Did youresolve the issue with First_Name(notes)? It should be First(Notes).

Access has a "Find Duplicates Query Wizard" that will help you identify duplicates. Slect New in the Query window and then select the "Find Duplicates Query Wizard."

You should end up with a query like the following.

Select
First_Name, Last_Name,
Account, City
From [Marketing Lists]
Group By
First_Name, Last_Name, Account, City
Having Count(*)>1

This will identify the duplicates. I would insert the results of this query into a new table and use that table to control which records are copied to the other new table and subsequently deleted from the [Marketing Lists] table. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.