Need SQL Query Help

[Thanos]

I have a ADODB connection to a SQL server over a LAN. It has a specific database with five Tables that contain data from 50 automated testcells that send a 26 Field record for each completed test cycle over an an OPC Server/Router based connection. So each Table has 26 Fields. I need to construct a simple tool that will connect to the table of my choosing and using a timer loop periodically update a Label control caption with the lastest value of Field number 3.

I can do all of this except construct the SQL query that pulls the latest value for Field number 3.

Something like:

SELECT * Field3, ORDER BY Field3... something, something

[RobDog888]

It would be more like ...

Dim oRs As ADODB.Recordset

Combo1.AddItem "Table1"
Combo1.AddItem "Table2"
Combo1.AddItem "Table3"
Combo1.AddItem "Table4"
Combo1.AddItem "Table5"

Set oRs = New ADODB.Recordset
oRs.Open "SELECT TOP 1 Field3 FROM [" & Combo1.Text & "] ORDER BY YourDateTimeStampField DESC", oConn

If oRs.BOF = False And oRs.EOF = False Then
    Label1.Caption = Trim$(oRs.Fields("Field3").Value & vbNullString)
Else
    MsgBox "No Records Returned"
End If

[Ron Weller]

SELECT Field3 FROM TABLE1 ORDER BY Field3 Desc

Note this will only work if the value of Field3 is always increasing, since you are sorting in descending order the first record will be the record you want. Now if the values can be any amount then you will need another field to sort by like a date time field that shows when the entry was made, or if you have a field that automatically increments you can sort it in descending order and that will make the latest entry the first record.

[RobDog888]

Correct and which is why I'm ordering by a datetime stamp field: YourDateTimeStampField

[Thanos]

Yes the values in the field contain the date-time stamp for each completed test so they are constantly updating. The problem we have is that the server is not monitored for activity on a regular basis so i need a desktop app with a timer that will check the DB periodically and compare the latest value to the last value and set off an audible alert if it isn't updating regularly. The system is very stable overall, but the OPC Server/Router is extremely sensitive to device collisions between IP addresses and I can't rely on my IS department not to step on my subnet. The data collection process is too critical to let lapse.

[RobDog888]

What db is it running on? Access, SQL Server, MySQL, etc?

[Ron Weller]

SELECT Field3 FROM Table1 ORDER BY Field4 Desc

Here Field3 is the value you need and Field4 is the date-time stamp field. What are you creating this monitoring program in? VB, or Access, ..?
Right now I am using Access as a front end for an Oracle database. It's working well so far.

[Ron Weller]

SELECT Field3, "Table1" As TableName FROM Table1 WHERE Field4 = (SELECT Max(Field4) FROM Table1)
UNION SELECT Field3, "Table2" As TableName FROM Table2 WHERE Field4 = (SELECT Max(Field4) FROM Table2)
UNION SELECT Field3, "Table3" As TableName FROM Table3 WHERE Field4 = (SELECT Max(Field4) FROM Table3)
UNION SELECT Field3 , "Table4" As TableName FROM Table4 WHERE Field4 = (SELECT Max(Field4) FROM Table4)
UNION SELECT Field3, "Table5" As TableName FROM Table5 WHERE Field4 = (SELECT Max(Field4) FROM Table5)
ORDER BY TableName

This is a union query that will produce a single recordset with exactly 5 records where each record has the latest Field3 value for each table, as well as the table name it came from.

[Ron Weller]

Just a little test app I threw together in Access.
If you open the database in Access the form automatically opens and starts checking the server.
All of the tables are actually local, not on a server, but all you have to do is delete or rename the test tables and link in the Sql Server tables. Then if needed open the unuion query and change the table names and field names to match what is in your tables. I used Alias's for all of the field names so the form should work no mater what field names your tables use.
The timer is set to check the server every 30 seconds. The Form Load Event is where I set the Interval, so you can change it there. The blinking colons in the time display is so the form does not look dead, like it might be locked up or something.