First And Last Dates From a Recordset

[Jables]

I have a database which tracks employee time by project. I would like to retrieve the date corresponding to the first and last day the project has been worked on.

So I established a first query, qry1. The SQL is like this:

SELECT tbljobs.jobnumber, tbljobs.jobname, tbltime.dateworked
FROM tbljobs INNER JOIN tbltime ON tbljobs.jobID = tbltime.fkjobID
WHERE (((tbljobs.jobnumber)=[Enter Job Number]))
ORDER BY tbltime.dateworked;

It returns results like this:

jobnumber jobname dateworked
139 xjobx 10/2/2002
139 xjobx 10/15/2002
139 xjobx 10/15/2002
139 xjobx 10/16/2002
139 xjobx 10/20/2002

Note that the dates are in ascending order so that I can easily see the first and last day on which the project has been worked.

I made another query, qry2. Here is the SQL:

SELECT First([qry1].[dateworked]) AS FirstOfdateworked, Last([qry1].[dateworked]) AS LastOfdateworked
FROM [qry1];

It should return these results:

FirstOfdateworked LastOfdateworked
10/2/2002 10/20/2002

Unfortunately, though, qry2 does not return these results. It is finding two dates, but they are not the first and last dates in the recordset defined by qry1. I don't get it. Any ideas?

 

[mwolf00]

SELECT MAX(dateworked), MIN(dateWorked) FROM myTable where jobnumber = 139 -- What did you expect? This is FREE advice. LOL