Help with query for linked tables

[Rayo K]

I have my SQL pasted below. I need some help. My table structure is as
follows. There is an Issues table with a primary key "ID" and an Updates
Table with a field for "IssueID" that is linked to "ID" as one-to-many. The
idea is that for each issue we can create many updates taht are all linked to
that issue.

I now want to create a query to generate a report that will list selected
issues, along with the LAST update for each issue. Each update has a
date/time and I am trying to get the MAX date/time of updates for each issue.
However, the remainder of the data is not showing up correctly. (e.g. I will
get the date/time for update 2 but the text from update 1).

I know i am missing something in the query structure but I do not know what.
Please help!!!!



SELECT DISTINCTROW Max(UpdatesQry.Date) AS MaxOfDate,
First(UpdatesQry.Update) AS FirstOfUpdate, UpdatesQry.Final, [All Open Issues
Query].ID, [All Open Issues Query].Priority, [All Open Issues Query].[Date
Entered], [All Open Issues Query].Area, [All Open Issues Query].Department,
[All Open Issues Query].Issue, [All Open Issues Query].Resolution, [All Open
Issues Query].[Date Closed], UpdatesQry.IssueID
FROM [All Open Issues Query] INNER JOIN UpdatesQry ON [All Open Issues
Query].ID = UpdatesQry.IssueID
GROUP BY UpdatesQry.Final, [All Open Issues Query].ID, [All Open Issues
Query].Priority, [All Open Issues Query].[Date Entered], [All Open Issues
Query].Area, [All Open Issues Query].Department, [All Open Issues
Query].Issue, [All Open Issues Query].Resolution, [All Open Issues
Query].[Date Closed], UpdatesQry.IssueID;

 

[Dale Fye]

The way I usually do this is to create a subquery that identifies the max
date for each ID, I then use that query as a bridge between my other two
tables.

qryMaxDate:

SELECT IssueID, Max([Date]) as MaxDate
FROM tbl_Updates
GROUP BY IssueID

Now, create a new query that contains all three of these queries and join
[All Open Issues Query] to qryMaxDate on the ID = IssueID and qryMaxDate to
UpdatesQry on IssueID and MaxDate.

Then, select the fields you want from the two outer ([All Open Issues
Query], UpdatesQry) queries. You should not need to do use a GroupBy in this
new query

HTH
Dale