Return the next entry in a query/table 1

[mrtauntaun]

Im not sure how to describe this, but here it goes.
I have a query that returns numerous results for various jobs. Each job has an id, Job_Num. Everytime this job is modified, a new entry is put into the table, Job_History, so I can track the history of that job. So there are numerous results for each Job_Num. The query I have, Jobs/Month, shows me all Job_Num for the previous month, with each Job_Num appearing many times. What I want is to create another Query that only shows me the first result listed for each Job_Num (therefore each Job_Num should be unique in the results, only appearing once).
Does that make sense? If so, is this even possible? Thanks!

 

[lynchg]

Hopefully your history table has a timestamp field in it to record when that history record was created.

If so, create a query that groups by the Job_Num, and take the minimum timestamp value.

Example:

Select Job_Num, First(Field1), First(Field2), Min(Timestamp)
From yourTable
Group By Job_Num;

 

[mrtauntaun]

Great, that helps a great deal. There is another query I want to try and make similar to this one, that would also be helpful. Using the same information above, I want to take the query with the multiple Job_Num, return to me a row of information AFTER another row with certain criteria, not date related.
For example, return to me every row after the row which containts Field1="Home"
So if row 1 in a query result has "home" in it, it returns me row 2. If row 1100 has "home" in it, return me row 1101.
Unfortunately, I have no common denominator to make this easier, so this is an odd request I know. Anyhelp would be great, thanks.

 

[lynchg]

This kind of thing could be done using a recordset in code. But you would need to determine what your definition of "next" is. Then you can sort the rows in your recordset accordingly, and as you step through the recordset and hit the triggering record, just advance to the next record to get the value(s) you are interested in.

 

[mrtauntaun]

The query is sorted chronologically by the aforementioned timestap, and bascially what I am trying to do is pull information from the next chronological record after that criteria is found. Im not too good with queries, and even worse with code, but i'll try checking out a recordset, thanks for the insight, you've put me on the right track