[thelist] SQL Distinct to 1 item
j s
jslist at sbcglobal.net
Fri Jun 17 08:45:22 CDT 2005
Hello,
In ASP I'm using a SQL line that pulls data from a few tables. Is there a way to use Distinct (or something like it) and apply it to only 1 field instead of the whole record? I'm getting duplicates - but if I could distinct on the "File_Number" it would solve my problems.
Here's the actual string:
strSQL = "SELECT Vehicle_Files.File_Number, Vehicle_Files.Agent, Vehicle_Files.Opened_Date, Vehicle_Files.Repatriation_Date, Vehicle_Files.Ins_Claim_Number, Vehicle_Files.OCA, Vehicle_Files.VYR, Vehicle_Files.VMO, Vehicle_Files.VMA, Vehicle_Files.VIN, Vehicle_Files.Theft_Date, Vehicle_Files.Theft_Time, Vehicle_Files.Theft_Address, Vehicle_Files.Theft_City, Vehicle_Files.Theft_State, Vehicle_Files.Theft_Zip, Vehicle_Files.Tow_Location, Vehicle_Files.Repatriation_Cost, Vehicle_Files.NADA_Value, Vehicle_Files.Location_City, Vehicle_Files.Location_State, Vehicle_Files.Vendor, Vehicle_Files.Adjuster, Vehicle_Files.ORI, Vehicle_Files.ORI_Name, Vehicle_Files.File_Status, Vehicle_Files.Status, Vehicle_Files.Closed_Date, Vehicle_Files.Salvage_Proceeds, "
strSQL = strSQL & "Employees.FName, Employees.LName, "
strSQL = strSQL & "Mexican_Cities.City, "
strSQL = strSQL & "File_Status.File_Status, "
strSQL = strSQL & "Adjusters.Insurance_Co "
strSQL = strSQL & "FROM Vehicle_Files, Employees, Mexican_Cities, File_Status, Adjusters "
strSQL = strSQL & "Where ((Repatriation_Date BETWEEN #" & DateAdd("d",0,txtFrom) & "# AND #" & DateAdd("d",0,txtTo) & "#) AND "
strSQL = strSQL & "(Vehicle_Files.Agent = Employees.Employee_ID) AND "
strSQL = strSQL & "(Vehicle_Files.Location_City = Mexican_Cities.Mex_City_ID) AND"
strSQL = strSQL & "(Vehicle_Files.Adjuster = Adjusters.Adjuster_ID))"
strSQL = strSQL & " ORDER BY Adjusters.Insurance_Co, Status, Repatriation_Date;"
Thanks,
Jess
More information about the thelist
mailing list