SQL help please

• Shailen-277197

  Newbie

  Points: 6

  More actions

  November 14, 2005 at 1:34 am

  #165932

  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

• Lowell

  SSC Guru

  Points: 323546

  More actions

  November 14, 2005 at 1:33 pm

  #603749

  it looks like you just need to add a WHERE statement to the end:

  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]

  WHERE v2.[Comment] <> 'Completed'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!