Problem with understanding sql syntax

SeriesIII

Registered User.
Local time
Today, 16:00
Joined
Nov 22, 2006
Messages
44
I think this is a really newbie type of question, basically down to me not understanding SQL syntax, but as that is one the the reasons I am designing this database, I'll ask away if that's alright.

I have a db with a table (TblEvents) that contains;

EventID ( pk autonumber)
EventType ( look-up to another table)
JobID (ditto)
EventTime (time), and a Notes field.

I want to find the latest EventType, EventTime, Notes for each job. Looking through the forum, I have cobbled together this;

SELECT T1.JobID, MAX(T1.EventTime) AS EventTime
FROM TblEvents AS T1
GROUP BY T1.JobID;

This gives me the latest event time for each JobID ~ but how do I include the other fields in the results? Any advice appreciated.
 
Sorry... hold that advice. Just figured that if I switched back to design view, added the fields, then looked back as SQL view, I can answer my own question. Sorry folks.
 
No, I'm afraid I am stumped. However I try to add the additional fields I required in the result, it fails, either by adding an additional grouping level so that I end up with more than one result per JobID, or by giving the error message 'you tried to execute a query that does not include the specified expression ......'

I've searched on this error message, but can't find a solution that appears to work. Can anyone help? (and I promise I am actually stuck this time!)
 
Have you tried setting the extra fields to total type 'expression'. If that does not work, then the technique I use is to link the result set back into original table to pick up the additional data. However this may not always be posible.
 
Thanks Dennisk ~ your tip set me off in the right direction. I set up a query similar to my original one, then used this within another with an inner join. All is now well ~ much appreciated.
 

Users who are viewing this thread

Back
Top Bottom