By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,767 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,767 IT Pros & Developers. It's quick & easy.

How do I delete duplicates in a row?

P: 2
I'm just starting to use SQL and am much more experienced in Access.

Here is what I do in Access
Copy a table and rename the new table "copytable" also select structure only.

Open "copytable" and highlight columns where I want to delete duplicates if
there are more than one.

Put a primary key on Outreach name and days.

Save and close

Go into query and select orig table and drag asterisk down to field

Go to Append Query and choose "copytable" and execute

The result will have the duplicates removed and only keep 1.

How do I add this part to the end of my query in SQL to deliver the same results? See query below... I want to remove dups using the Outreach name and also the alias "days2" name as criteria. I want the rest of the columns populated as well. Or is there a way, before I run this query to ALTER my table to get rid of the dups? The problem is, one of the criteria columns is an ALIAS. So confused and don't know how to solve. Any help you can give me would be great. THANKS!


SELECT DISTINCT Orig.Outreach_Name, Process_Stage, Request_Type, Provider_Name,
TIN_MPIN, Primary_Specialty, PAR_Status, Market, [orig].Market_Number,
[Region].Region, Outreach_Type, Paper_Type, Contract_Effective_Date,
Emptoris_Contract_ID, Submission_Reference_Number,
Credentialing_Complete_Actual,
--need to add Credentialing_Status, Task_Type, Task_Owner, Task_Status,
Task_Created_Date, Task_Completed_Date, No_of_Outreaches-

DateDiff (d, [Credentialing_Complete_Actual], [Contract_Effective_Date]) AS
Days2, [Range].Range
FROM (Orig
INNER JOIN Region
ON [Orig].Market_Number = [Region].Market_Number)
INNER JOIN Range
ON DateDiff (d, [Credentialing_Complete_Actual],
[Contract_Effective_Date])=[Range].Value
WHERE Contract_Effective_Date BETWEEN '2000-01-01' and '2010-01-10'
AND Credentialing_Complete_Actual BETWEEN '2000-01-01' and '2010-01-10'
AND DateDiff (d, [Credentialing_Complete_Actual],[Contract_Effective_Date])>=0
ORDER by Outreach_name
Oct 3 '09 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
If it's an exact duplicate, will a DISTINCT work?

Good luck!!!

--- CK
Oct 7 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.