Bringing back only one row in a joined table using MS Access

Question

Hi All

I know there are similar postings out there, but when I have tried to use any of them in my own query I can not get it working.

Basically, I have 3 tables that I want to query in ACCESS using the SQL view.

Initially though and for this example I am trying to do it with just the 2.

tb1 name: Tasks

tb1 fields wanted: Task ID Task Title Project ID (This is to be used to grab the project title at the end, i.e. the 3rd table as mentioned above)

tb2 name: Task Notes

tb2 fields wanted: Task Note ID Task Note Date Task Note

What was happening using the design view, is that if there was 3 notes for example relating to one task in the Tasks table, it was listing all 3 task notes.

What I want to do, is to bring out the last Task Note entered only, be it by [Task Note ID] or by [Task Note Date].

What I am left with now, built from help from here is the following SQL statement:

SELECT 

t.[Task ID], 
t.[Task Title], 
t.[Project ID],
tn.*

FROM
Tasks t

INNER JOIN [Task Notes] tn ON t.[Task ID] = tn.[Task ID]

WHERE tn.[Task ID] = 
  (SELECT max(tn.[Task Note Date]) FROM tn where tn[Task ID] = t.[Task ID])
;

I am getting a syntax error with this, but previous incarnations have also left me stuck.

I normally develop outside of ACCESS and in php/asp it is fine, however in ACCESS I am a newbie.

Any help would be greatly appreciated

Cheers

Matthew

Answer 1

Try something like this

SELECT t.[Task ID], 
       t.[Task Title], 
       t.[Project ID],
       tn.*    
  FROM Tasks t 
       INNER JOIN
       (
          SELECT [Task ID], 
                 MAX([Task Note Date]) MaxDate
            FROM [Task Notes] 
           GROUP 
              BY [Task ID]
       ) TaskNoteIDS 
          ON t.[Task ID] = TaskNoteIDS.[Task ID] 
       INNER JOIN [Task Notes] tn 
          ON TaskNoteIDS.[Task ID] = tn.[Task ID]
             AND TaskNoteIDS.MaxDate = [Task Notes].[Task Note Date];

Answer 2

Try:

FROM tasks AS t
FROM [other table] AS tn

It's an "SQL flavor variant" found in MS Access ...