Join 2 tables with "most recent date" row from sub-table

[bigteks]

I am making a very simple activity tracking app. There are 2 main tables, Issues & Status. There are many Status updates for each Issue. There are lookup tables for the Environment, Project, State and Worker fields but I'm not concerned about that part right now.

Issues Table:
IssuesID, AutoNumber
Issue, Text
Environment, Number
Project, Number
Created, Date/Time
State, Number

Status Table:
StatusID, AutoNumber
Issue, Number
Worker, Number
Next Step, Text
Last Status, Date/Time
Last Communication, Memo
Resolution, Memo

I need a query that includes all fields of both tables. I want to select Issues by “State” (that's not included in this query yet), but include only the most recent status (Status.[Last Status]). Basically I want to populate a table with all open issues, but only one row per issue, showing only the most recent status update of each one.

Here is what I’ve got so far. I think it is close but it is not quite working. When I try to run the query it pops up a window asking for the value of t2.StatusID. Since I'm expecting it to select from all StatusIDs using my criteria, I am not sure why it is asking me to supply a value.

SELECT Status.StatusID AS Expr1, Status.Issue AS Expr2, t3.IssuesID, t3.Environment, t3.Project, t3.Created, t3.State, Status.Worker AS Expr3, Status.[Next Step] AS Expr4, Status.[Last Status] AS Expr5, Status.[Last Communication] AS Expr6, Status.Resolution AS Expr7
FROM Status AS t1, Issues AS t3
WHERE Status.[Issue]=t3.IssuesID AND ([Status].[Last Status])=(SELECT Max(t2.[Last Status])
FROM Status t2
WHERE t2.StatusID=t1.StatusID
GROUP BY t2.StatusID);

 

[arnelgp]

try:

SELECT T1.StatusID AS Expr1, T1.Issue AS Expr2, t3.IssuesID, t3.Environment, t3.Project, t3.Created, t3.State, T1.Worker AS Expr3, T1.[Next Step] AS Expr4, DLast("Last Status","Status","Issue=" & T3.iSSUEID) AS Expr5, T1.[Last Communication] AS Expr6, T1.Resolution AS Expr7
FROM Status AS t1 INNER JOIN Issues AS t3 ON T1.ISSUE=T3.ISSUESID
GROUP BY T1.StatusID;

 

[bigteks]

OK it is almost working.

It was saying "You tried to execute a query that does not include the specified expression 'Expr2' as part of an aggregate function.

So I fixed this by adding all the fields in the query to the GROUP BY statement, like this (I also eleminated the expr & table references):

SELECT Status.StatusID, Issues.IssuesID, Issues.Environment, Issues.Project, 
    Issues.Created, Issues.State, Status.Worker, Status.[Next Step], 
    DLast("Last Status","Status","Issue=" & Issues.[IssuesID]), 
    Status.[Last Communication], Status.Resolution
FROM Status INNER JOIN Issues ON Status.[Issue] = Issues.[IssuesID]
GROUP BY Status.StatusID, 
    Issues.IssuesID, 
    Issues.Environment, 
    Issues.Project, 
    Issues.Created,
    Issues.State, 
    Status.Worker, 
    Status.[Next Step], 
    Status.[Last Communication], 
    Status.Resolution, 
    Status.[Last Status];

This produced a table output like I was wanting. But the "DLast" function was not working right. I was getting the following error in this output field:
Syntax error (missing operator) in query expression 'Last(Last Status')'.

It turns out that Access doesn't handle field names with spaces in them very well, even though I was using quotes and brackets, it wasn't enough. I went back and eliminated spaces in all field names and that eliminated the syntax error.

Now my problem is, the "GROUP BY" fix I did of including all the fields in the GROUP BY section, apparently ruined the date selection method, and it is now including all rows regardless of date. It appears that by grouping by everything, I may have turned every row into it's own separate aggregate which means there's no way to select a single latest date row out of many rows because each aggregate is only one row long.

So now I'm looking for a better way to solve the "not part of an aggregate function" error that I was getting at the start. I need all the rows that are from the same "issue" to be sorted by date and only take the most recent row from each issue.

 

[bigteks]

I get the same result without any errors by eliminating the GROUP BY statement entirely. I would like to understand what that does to include it and what happens by excluding it. Currently it is not reducing to one row per Issue. Here is the latest query that almost works but produces a join with nothing filtered out:

SELECT Status.StatusID, Issues.IssuesID, Issues.Environment, Issues.Project, 
       Issues.Created, Issues.State, Status.Worker, Status.NextStep,
       DLast("LastStatus","Status","Issue=" & Issues.[IssuesID]) AS Expr1,
       Status.LastCommunication, Status.Resolution
FROM Status INNER JOIN Issues ON Status.[Issue] = Issues.[IssuesID]

So what I need help with is how to get the older dated rows with the same IssuesID filtered out of the join. I believe that the DLast() function should do that filter function but it doesn't seem to be doing it.

 

[plog]

DLast is a worthless function, never use it or DFirst (or LAST or FIRST in aggregate SQL).

If you could post some sample data to demonstrate what you would like to occur I can help. I would need 2 sets of data:

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results based on A. Show me what you expect your query to return when you feed it the data in A.

No explanations for now, just sample data.

 

[bigteks]

Data for the Issues table:

View attachment Issues.xls

Data for the Status table:

View attachment Status.xls

Expected output from the query:

View attachment Current Issues Query.xls

Let me know if you need more than that or if it is not clear.

Thanks

 

[plog]

First you've got some really confusing names in your tables. [LastStatus] is a poor choice because it does not hold just the LastStatus, it holds all stati --thus your issue for posting. You have an Issue in Issues, which is text and then an Issue in Status which is numeric. The one in Status should probably be named IssueID so you know what it corresponds to.

To solve your issue, you will need a sub query to get the latest Status record for each Issue:

SELECT Status.Issue, Max(Status.LastStatus) AS MaximumStatus
FROM Status
GROUP BY Status.Issue;

Paste that into a new query and name it LastStatus_sub1. Then the below will give you the results you want:

SELECT Issues.*, Status.*
FROM (Issues INNER JOIN LastStatus_sub1 ON Issues.IssuesID = LastStatus_sub1.Issue) INNER JOIN Status ON (LastStatus_sub1.Issue = Status.Issue) AND (LastStatus_sub1.MaximumStatus = Status.LastStatus);

 

[bigteks]

Perfect! Thanks, that did it.