Thanks Brian - I'll work on that. Brian Cummiskey <Brian at hondaswap.com> wrote:j s wrote: > 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 Of course you're getting dupes-- you're selecting EVERYTHING from each table. what you need to do is to JOIN the tables on a unique ID that is common to all the tables. if File_Number is in all the tables, LEFT OUTER JOIN each table on that field. Thus, getting only one record, but all the info from all the tables. effectively, changing: strSQL = strSQL & "FROM Vehicle_Files, Employees, Mexican_Cities, File_Status, Adjusters " to strSQL = strSQL & "FROM Vehicle_Files " &_ LEFT OUTER JOIN Employees on Employess.File_number = Vehicle_Files.File_Number " &_ LEFT OUTER JOIN Mexican_Cities on Mexican_Cities.File_Number = Vhicle_Files.File_Number " &_ and the same for File_Status and Adjusters (sorry, tired of typing...) if you don't have anything that links the tables together, you need to re-build the database and make it relational, or no asp or sql code is going to help you really. -- * * Please support the community that supports you. * * http://evolt.org/help_support_evolt/ For unsubscribe and other options, including the Tip Harvester and archives of thelist go to: http://lists.evolt.org Workers of the Web, evolt !