|
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
| 
