Hi, I'm developing a small reception application for staff to use.
The app records visitors signing in and out of the building. CREATE TABLE av2 ( Activity_ID INTEGER PRIMARY KEY, Timestamp TIMESTAMP, Visitor_ID VARCHAR(8), Action VARCHAR(1), Message VARCHAR(255)); insert into av2 (Activity_ID,Timestamp,Visitor_ID,Action,Message) values (NULL,"2004-05-23 22:29:32","00120145","I","Signed IN"); insert into av2 (Activity_ID,Timestamp,Visitor_ID,Action,Message) values (NULL,"2004-05-23 23:29:32","00120145","O","Signed OUT"); insert into av2 (Activity_ID,Timestamp,Visitor_ID,Action,Message) values (NULL,"2004-05-23 20:29:32","00144321","I","Signed OUT"); What I would like is to run a query, at night, which will display the last entered value for each visitor_id. Anyone still logged in will alert the building manager. I thought the following would work; SELECT Visitor_ID,Action,Message, MAX(Timestamp) FROM (SELECT * FROM av2 WHERE (Action='I' OR Action='O')) GROUP BY Visitor_ID; It seems to almost do it, the result; 00120145|I|Signed IN|2004-05-23 23:29:32 The Time is right but the Action and Message is from 22:29:32 log, bit of a mix- up! Its probably down to my daft SQL query, any idea's ? Dan --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
