Help with Query

  • Thread starter Thread starter Davie3035
  • Start date Start date
D

Davie3035

Access 2003 - I have a table of report data with twelve columns . The first
column called 'ID' is the table autonumber column. The column of interest
(Col2) is called 'StationNumber' which contains data between numbers 1 and 4.
For the example assume Col1 is 'ID', Col2 is 'StationNumber', Col3 is
'Quantity'. The data in order of time created is:
109, 1, 203.6
108, 1, 203.4
107, 1, 203.9
106, 3, 178.5
105, 3, 178.3
How can I query the 'Last' row to be added from 'StationNumber' 3?
The single row I'm looking for in this case is Row 4 with Quantity of 178.5
but I can't seem to get the correct query to return this.
Anybody help? Thanks.
 
SELECT SomeTable.*
FROM SomeTable INNER JOIN
(SELECT StationNumber, Max(ID) as LastID
FROM SomeTable
GROUP BY StationNumber) As X
ON SomeTable.StationNumber = X.StationNumber
AND SomeTable.ID = X.LastID

If you need an updateable query.

SELECT SomeTable.*
FROM SomeTable
WHERE SomeTable.ID =
(SELECT Max(ID)
FROM SomeTable as X
WHERE X.StationNumber = SomeTable.StationNumber)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
John, many thanks for reply.
The query gives me the last for every station.
ie. last from every station 1 thru 4. I also want to perform this but for my
initial query I wanted to 'hardcode' the Station number into the query.
So... Return last row where StationNumber = "3" OR
Return last row where StationNumber = "1" etc.
Sorry for not making clear.
 
so just add or expand the where clause

WHERE SomeTable.StationNumber = 3

or for the second query
WHERE SomeTable.StationNumber = 3
and SomeTable.ID =
(SELECT Max(ID)
FROM SomeTable as X
WHERE X.StationNumber = SomeTable.StationNumber)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top