Help with selecting distinct records and proper order. 1

[GunJack]

I've been working with sql for years but my limited knowledge of it has finally caught up with me. I'm working in a web scripting environment where I'm using odbc to run sql statements against an access db.

I have a table with contains entries for time spent on projects by an employee. Each record contains a start and stop time for when the work was performed and a description of the work. A person might work on the same task for several days and have a record for each day with the same description of what they were doing. I want to provide a drop down list containing the last ten descriptions a person used ordered by the description name.

I basically need to sort the entries by date, retrieve the last ten distinct descriptions, and then order them by the description name. I'm trying to find if there is a way to do this in sql without having to use the "brute force" method on the query results.

The first two steps are where I'm really having trouble. I can't seem to find a way to get a distinct lising on the "description" field without losing the date information so I can take the last ten used by the employee. I don't want them to see a description that was used ten months ago, I want them to see the most recent descriptions.

Any help would be much appreciated,
GJ

 

[sko]

On the Access query grid, right-click on the top half of the grid and have a look at query properties. There are Top Values, Unique Values and Unique Records properties you could set and then view the SQL code.

HTH

 

[RickSpr]

I tried this earlier today and couldn't figure it out. It's certainly not the simplest of queries!

But this evening, I had a brainstorm, and I think I may have it. Here's the SQL I ended up with:
SELECT TOP 10 WorkRecords.Description
FROM WorkRecords
GROUP BY WorkRecords.Description, WorkRecords.EmployeeID
HAVING (((WorkRecords.EmployeeID)=[EmpID]))
ORDER BY First(WorkRecords.StartTime) DESC;
Rick Sprague

 

[GunJack]

Hey Rick,

Thanks for your help. That's pretty close but it appears that it's not truly picking up the most recent ones. I set the number of distinct records to 1, then 2, then 3,... and watched which ones it picked out. It appears to work fine until it encounters a description that has multiple entries. What happens in this case is that is uses the date assigned to the earlier occurance of the record. Iow, if the employee entered work under a description a year ago and then with the same description today, it appears that it's using the date from a year ago and drops this "description" down lower in the list.

Any ideas? I don't understand the sql syntax so I'm not sure what can be modified if any to fix it.

Thanks again,
GJ

 

[RickSpr]

That's odd. Are you sure the ORDER BY expression ends with DESC? That should make them sort from latest to earliest.

I'm not sure why, but I think I have it now. Change "First" in the ORDER BY clause to "Last". I added more records to my test table and it seems to work. Still, I don't have the range of data you have, so it might be a coincidence that it wroks right on mine. Try it and see. Rick Sprague

 

[GunJack]

Hey Rick,

That seems to be the trick! Thanks a bunch for helping on that one. The only remaining item it sorting the records by description now that I have the correct group of records. Is it possible to do this inside the query without messing up anything? If not, I can dump the query into an array and sort it after the fact as the hard part's over.

In the real application, I have to apply a few additional criteria to selecting records which I seem to have added correctly as it still works. Do you see any glaring problems with how I've added it? I basically had to guess until it started working.

SELECT TOP 10 hours.Desc
FROM hours
GROUP BY hours.Desc, hoursType, EmployeeID, idNum, closed, active
HAVING ((
(hoursType) = '#attributes.hType#' and
(employeeID)=#attributes.empID# and
(idNum)=#attributes.idNum# and
(closed)=#attributes.closed# and
(active)=#attributes.active#
))

Thanks again,
GJ