Simple Query Question

[492JQS]

I've got a table in my Engineering Change database with the following fields:

ChangeLink
ChangeIssue
Remarks

The table contains multiple Change Issues for each Change Record. What I'm trying to do, as part of a query that pools data from multiple tables, is display only the latest issue of a change and the associated remarks. For example, if a change was raised to issue 4 to correct a Point of Embodiment, then I only want the query to return change issue 4 with the PoE change remarks, and not issues 1, 2 & 3.

I've searched the forums but cannot find a solution. I'm sure this is fairly simple, but then again, so am I!

Any help would be appreciated.

JQS

 

[snoko]

Order your query by 'Change Issue' in desending order and only return the TOP 1 record. Therefore you'll only return the record with the highest Change Issue number.

Hope this helps

 

[492JQS]

Snoko

I followed your advice, and now the query returns only the record with the highest change issue. What I wanted was a complete list of change records showing only the latest issue for each (if that makes sense). Is there some way I can extend this method to show the latest issue for every change record? I'm fairly new to access, so maybe I'm missing something obvious.

JQS

 

[snoko]

The MAX function may help you. But I would imagine you'll have to make a couple of extra queries and base the final one on these queries to return the correct info.

 

[492JQS]

Snoko

Thanks for your help so far. This obviously isn't as straightforward as I thought! I know I could get around the problem if I added another field to the table, probably a YES/NO field called 'Superseded' or something like that. Then I could filter on that field. I'm trying to keep data entry as simple as possible though, and so want to avoid that route.

JQS

 

[snoko]

try this using your field names - The MAX function returns the highest record from a group...

SELECT Record.Name, Max(IssueNo.Issue) AS MaxOfIssue
FROM Record INNER JOIN IssueNo ON Record.intID = IssueNo.intRecordID
GROUP BY Record.Name;

Two tables Records and IssueNo - One Record can have many IssueNo's then use the MAX function to return the highest IssueNo for each Record.

 

[492JQS]

I'll give that a try.