[thelist] SQL Distinct to 1 item

[Chris at globet.com]

Jess

> Here's the code:
>  
> strSQL = "SELECT Vehicle_Files.File_Number,  Employees.LName, 
> Status.Status, Mexican_Cities.City, Mexican_States.State, 
> Adjusters.Insurance_Co, Vehicle_Files.Agent, 
> Vehicle_Files.Opened_Date, Vehicle_Files.Repatriation_Date, 
> Vehicle_Files.File_Status, Vehicle_Files.Status FROM 
> Employees INNER JOIN ((((Vehicle_Files INNER JOIN Status ON 
> Vehicle_Files.Status = Status.Status_ID) INNER JOIN 
> Mexican_Cities ON Vehicle_Files.Location_City = 
> Mexican_Cities.Mex_City_ID) INNER JOIN Mexican_States ON 
> Vehicle_Files.Location_State =
> Mexican_States.Mex_State_ID) INNER JOIN Adjusters ON 
> Vehicle_Files.Adjuster = Adjusters.Adjuster_ID) ON 
> Employees.Employee_ID = Vehicle_Files.Agent Where 
> (Repatriation_Date BETWEEN #" & txtFrom & "# AND #" & txtTo & 
> "#) ORDER BY Adjusters.Insurance_Co, Vehicle_Files.Status, 
> Repatriation_Date;"

Try writing your code in a more user friendly fashion, this will make it
easier to figure out where you can optimise things. I'm not a database
expert, but it would appear to me that all of those nested inner joins
are going to be almost hopelessly inefficient. I'm sure someone will
correct me if I'm wrong, but try the following. It may be no better, and
it's obviously untested:

    SELECT Vehicle_Files.File_Number,  
           Employees.LName, 
           Status.Status, 
           Mexican_Cities.City, 
           Mexican_States.State, 
           Adjusters.Insurance_Co, 
           Vehicle_Files.Agent, 
           Vehicle_Files.Opened_Date, 
           Vehicle_Files.Repatriation_Date, 
           Vehicle_Files.File_Status, 
           Vehicle_Files.Status 
      FROM Vehicle_Files
           Employees, 
           Mexican_Cities,
           Mexican_States,
           Adjusters
     WHERE (Vehicle_Files.Repatriation_Date BETWEEN #" & txtFrom & "#
AND #" & txtTo & "#) 
       AND Employees.Employee_ID = Vehicle_Files.Agent
       AND Vehicle_Files.Status = Status.Status_ID
       AND Mexican_Cities.Mex_City_ID = Vehicle_Files.Location_City
       AND Mexican_States.Mex_State_ID = Vehicle_Files.Location_State
       AND Adjusters.Adjuster_ID = Vehicle_Files.Adjuster
  ORDER BY Adjusters.Insurance_Co, 
           Vehicle_Files.Status, 
           Repatriation_Date;

Regards

Chris Marsh