Hi , I have two tables as shown below
Faults table
FaultID Status
1 Cleared
2 In Progress
3 In Progress
Actions table
FaultID ActionNo
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
I cant change the tables (there are alot more fields in each of them but this is all I need for my question)
What i’m attempting to get is the last ActionNo for each FaultId for example
Query
faultid actionno status
1 3 Cleared
2 2 In Progress
3 3 In Progress
My query so far
SELECT Faults.Faultid, Faults.Status, ACTIONS.actionnumber
FROM Faults INNER JOIN ACTIONS ON Faults.[Faultid] = ACTIONS.[Faultid];
Any help would be greatly appreciated
