Hi there,
Kinda new to programming, and running into problems trying to apply a SQL statement:
My table ABCDdown3Minutes gets updated every minute with data through an API.
here's a shot of the table:
http://screencast.com/t/CdSWs9M3

as you can see I get multiple records with same [ticker] but with different [TimeOfEntry]

when new data comes into the table I have to find each [ticker] with the latest datestamp in [TimeOfEntry]
So this would call for a subquery using an Inner Join, correct?

this is what I've tried so far:

Set rsABCDd3min = db.OpenRecordset("Select t.* from ABCDdown3Minutes as t INNER Join (SELECT ticker, MAX(TimeOfEntry) LatestTimeOfEntry from ABCDdown3Minutes) as toe ON t.ticker = toe.ticker and t.TimeOfEntry = LatestTimeOfEntry WHERE t.[ticker] = ticker")

But I'm getting a "Join Expression not supported" error.

eventually (if I get this to work) I'd like to use a SQLstring in the openrecordset statement, since I have to do more filtering.

anyone any ideas? Much appreciated.

Looks like the closing ) for the sub query is out of place. Needs to be after the "as toe"

Try:

TIP: Avoid a Select * query. It is best to only select the needed fields by listing them.

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

Thanks for the suggestion, however it results in a runtime error 3135 "syntax error in join operation"

just in case; here's the rest of code :

Private Sub TDAComm1_OnOHLCBar(ByVal Bar As Object)
Dim db As DAO.Database
Set db = CurrentDb
Dim rsABCDd3min As DAO.Recordset
Dim RowNumber As Integer
Dim ticker As String
Dim ABCDPRZ(3) As Single
ticker = Bar.Symbol

Set rsABCDd3min = db.OpenRecordset("Select t.* from ABCDdown3Minutes as t INNER Join (SELECT ticker, MAX(TimeOfEntry) LatestTimeOfEntry from ABCDdown3Minutes as toe) ON t.ticker = toe.ticker and t.TimeOfEntry = LatestTimeOfEntry WHERE t.[ticker] = ticker")
With rsABCDd3min
If ticker = !ticker Then
If Bar.Close < !entry Then
If !PRZhigh >= !D Then
ABCDPRZ(0) = !PRZhigh
If !D >= !PRZlow Then
ABCDPRZ(1) = !D
ABCDPRZ(2) = !PRZlow
Else
ABCDPRZ(1) = !PRZlow
ABCDPRZ(2) = !D
End If
Else
ABCDPRZ(0) = !D
ABCDPRZ(1) = !PRZhigh
ABCDPRZ(2) = !PRZlow
End If
If (Bar.Low <= ABCDPRZ(0) And Bar.Low >= ABCDPRZ(2)) Or (Bar.Close <= ABCDPRZ(0) And Bar.Close >= ABCDPRZ(2)) Then
' in ABCD Bullish PRZ zone
Me("WL" & RowNumber + 1).Controls("BullPRZ3min" & RowNumber + 1) = "ABCDPRZ"
Me("WL" & RowNumber + 1).Controls("BullPRZ3min" & RowNumber + 1).BackColor = GoldenRod
ElseIf Bar.Close > ABCDPRZ(0) And Bar.Close < !entry Then
'below entry above PRZ
Me("WL" & RowNumber + 1).Controls("CDdown3min" & RowNumber + 1) = !entry
Me("WL" & RowNumber + 1).Controls("CDdown3min" & RowNumber + 1).BackColor = Gold
ElseIf Bar.Close < ABCDPRZ(2) And Bar.Close < !entry Then
Me("WL" & RowNumber + 1).Controls("BullPRZ3min" & RowNumber + 1) = "below"
Me("WL" & RowNumber + 1).Controls("BullPRZ3min" & RowNumber + 1).BackColor = OrangeRed
Me("WL" & RowNumber + 1).Controls("BullPRZ3min" & RowNumber + 1).ForeColor = vbWhite
End If
Else
Me("WL" & RowNumber + 1).Controls("BullPRZ3min" & RowNumber + 1) = ""
Me("WL" & RowNumber + 1).Controls("BullPRZ3min" & RowNumber + 1).BackColor = vbWhite
Me("WL" & RowNumber + 1).Controls("CDdown3min" & RowNumber + 1) = ""
Me("WL" & RowNumber + 1).Controls("CDdown3min" & RowNumber + 1).BackColor = vbWhite
End If

End If
End With
rsABCDd3min.Close

figured it out.
This SQL stuff takes a little getting used to...

ticker = Bar.Symbol
TableName = "ABCDdown3Minutes"
sqlCommand = "SELECT t.* FROM " & TableName & " as t INNER JOIN (SELECT ticker, MAX(TimeOfEntry) as LatestTimeOfEntry FROM " & TableName & " GROUP BY ticker) toe ON toe.ticker = t.ticker AND toe.LatestTimeOfEntry = t.TimeOfEntry WHERE t.ticker = '" & ticker & "'"

Great job figuring it out.

Thanks for posting your solution.

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

ha yes, but now I discovered I can't edit the recordset with this sql command.