Limit query results to 1 item

[Valix]

SELECT tblFullSelectInfo.Name, tblFullSelectInfo.DateSelected,
tblFullSelectInfo.Rank, tblFullSelectInfo.SSN, tblFullSelectInfo.Notes,
tblFullSelectInfo.WorkCenter, tblFullSelectInfo.WorkPhone,
tblFullSelectInfo.PendingPool, tblFullSelectInfo.DueDate,
tblFullSelectInfo.CompleteDate
FROM tblFullSelectInfo
WHERE (((tblFullSelectInfo.DateSelected)>=Date()-30) AND
((tblFullSelectInfo.Notes) Not Like "not in *" AND (tblFullSelectInfo.Notes)
Not Like "PCS*") AND ((tblFullSelectInfo.CompleteDate) Is Null))
ORDER BY tblFullSelectInfo.DateSelected DESC;

I am running 3 query's like above based on 7, 30 and 90 days. When I run
this, I get multiple results for some people due to multiple entries when
selected with no completion date. But what I am looking for is the query
results to only show me the latest date selected with no completion date, one
result. I do have a report built to show these results, but how do I go
about limiting the results to just the one.

Thanks for any info, hope Im not confusing (maybe just myself).

 

[Jeff Boyce]

Take a look at "TOP" -- use it to limit the number of rows returned. You
appear to already by soring by DateSelected.


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Armen Stein]

SELECT tblFullSelectInfo.Name, tblFullSelectInfo.DateSelected,
tblFullSelectInfo.Rank, tblFullSelectInfo.SSN, tblFullSelectInfo.Notes,
tblFullSelectInfo.WorkCenter, tblFullSelectInfo.WorkPhone,
tblFullSelectInfo.PendingPool, tblFullSelectInfo.DueDate,
tblFullSelectInfo.CompleteDate
FROM tblFullSelectInfo
WHERE (((tblFullSelectInfo.DateSelected)>=Date()-30) AND
((tblFullSelectInfo.Notes) Not Like "not in *" AND (tblFullSelectInfo.Notes)
Not Like "PCS*") AND ((tblFullSelectInfo.CompleteDate) Is Null))
ORDER BY tblFullSelectInfo.DateSelected DESC;

I am running 3 query's like above based on 7, 30 and 90 days. When I run
this, I get multiple results for some people due to multiple entries when
selected with no completion date. But what I am looking for is the query
results to only show me the latest date selected with no completion date, one
result. I do have a report built to show these results, but how do I go
about limiting the results to just the one.

Thanks for any info, hope Im not confusing (maybe just myself).

Try adding TOP 1 to your Select statement.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Valix]

Thanks for the info on that, but Im still having an issue. I can see where
that will give me the latest date, but it knocks out alot of records. what I
am looking for is a way to show me the latest date for each person, not just
the people with the latest date for all records.

example: When i run the report/query normal, i get 8 pages of people with
numerous dates showing no completion, say most with selection date of 31 Mar
08. When I added the TOP 1 to the SELECT statement, i got 4 records returned
with the date selected of 4 Apr 08, because that is the latest date input
into my database.

If I have 30 people on my report with 3-4 dates each, how do I get it to
show 30 people, but only their latest date selected?

thanks again

 

[John Spencer]

Perhaps something like the following will work for you. You may need to
expand the where clause in the subquery to include the all or part of
the other criteria you have specified for the main query.

SELECT tblFullSelectInfo.Name
, tblFullSelectInfo.DateSelected
, tblFullSelectInfo.Rank
, tblFullSelectInfo.SSN
, tblFullSelectInfo.Notes
, tblFullSelectInfo.WorkCenter
, tblFullSelectInfo.WorkPhone
, tblFullSelectInfo.PendingPool
, tblFullSelectInfo.DueDate
, tblFullSelectInfo.CompleteDate

FROM tblFullSelectInfo INNER JOIN
(SELECT T.Name, Max(DateSelected) as LastDate
FROM tblFullSelectInfo as T
WHERE T.DateSelected >= Date() -30
GROUP BY T.Name) as TheLast
ON tblFullSelectInfo.Name = TheLast.Name
AND tblFullSelectInfo.DateSelected = TheLast.LastDate

WHERE tblFullSelectInfo.DateSelected>=Date()-30
AND tblFullSelectInfo.Notes Not Like "not in *"
AND tblFullSelectInfo.Notes Not Like "PCS*"
AND tblFullSelectInfo.CompleteDate Is Null

ORDER BY tblFullSelectInfo.DateSelected DESC;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Valix]

Thanks!!!

that did the trick...justa couple minor tweeks and its good.

thanks again!