SQL Help using MS Access as db

• Shailen-277197

  Newbie

  Points: 6

  More actions

  November 14, 2005 at 12:27 am

  #166205

  Hi

  I am using 2 tables from ms access to get my data.

  Table 1

  No (File Number)

  Name Description

  File No (if any)

  Company

  Table 2

  No

  File No

  Name

  Comment Date

  I am using an sql query to extract all the data from table even if theres no matches with table 2. The matches with table to will bring the last comment made on the file. Now the problem i have is..I want to bring all the data as i currently do but exclude from table 2 where the Comment is equal to "Completed"

  My query is

  strQuery = "SELECT Maintenance.[No],Maintenance.[File No],Maintenance.Company,Maintenance.[Name] as [Employee Name],Maintenance.[Description], v2.[Date], v2.[Comment]As [Status] FROM Maintenance LEFT JOIN (SELECT UpdateMaintenance.[No], UpdateMaintenance.[Date], UpdateMaintenance.[Comment] FROM UpdateMaintenance INNER JOIN (SELECT UpdateMaintenance.[No], Max(UpdateMaintenance.Date) AS MaxVanDate From UpdateMaintenance GROUP BY UpdateMaintenance.[No]) v1 ON (UpdateMaintenance.[Date] = v1.MaxVanDate) AND (UpdateMaintenance.[No] = v1.[No])) v2 ON Maintenance.[No] = v2.[No]"

  Can any1 help please

  Thanks

• JeffWenker

  SSC Veteran

  Points: 266

  More actions

  November 15, 2005 at 8:09 am

  #603929

  Hi

  I'd suggest building a subquery for table 2

  such as: Select  from table2 where comments <> 'completed'

  then use that instead of table 2.

  Initial response to this tends to be - can't I do it in one query?

  Maybe .. but this will work