使用MS Access在连接表中仅返回一行

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.

基本上,我有3个表,我想使用SQL视图在ACCESS中查询。

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

最初,虽然这个例子我只想用2做。

tb1 name: Tasks

tb1名称:任务

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)

想要的tb1字段:任务ID任务标题项目ID(这用于在最后获取项目标题,即上面提到的第3个表)

tb2 name: Task Notes

tb2名称:任务说明

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

想要的tb2字段:任务说明ID任务说明日期任务说明

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.

使用设计视图发生的情况是,如果有关于Tasks表中的一个任务的3个注释,它列出了所有3个任务注释。

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].

我想要做的是,仅通过[任务注释ID]或[任务注释日期]输出最后输入的任务注释。

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

我现在剩下的,从这里的帮助构建的是以下SQL语句:

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.

我通常在ACCESS之外开发,在php / asp中它很好,但在ACCESS我是新手。

Any help would be greatly appreciated

任何帮助将不胜感激

Cheers

Matthew

2 个解决方案

#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];

#2

Try:

FROM tasks AS t
FROM [other table] AS tn

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

这是MS Access中的“SQL风味变体”...

#1