Re: Limit query results to 1 item

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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 wrote:
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

"Armen Stein" wrote:

On Mon, 7 Apr 2008 12:20:04 -0700, Valix
<Valix@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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


.

Relevant Pages

  • Re: Clear and Requery combo boxes
    ... Q3 - when a selection is made for the project team, ... When I close the report and go back ... >> The choices then generate a report based on the selection. ... >> Exit Sub ...
    (microsoft.public.access.formscoding)
  • Re: Option Groups
    ... Can't find "Command button report selection" even though I did a search. ... 'This procedure used inh Preview_Click and Print_Click Sub procedures. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: music popularity experiment
    ... On the face of the report, the first group selected their favourites based upon a positive reaction to what they heard thus giving a measure of 'quality'. ... We do not know the selection criteria of the second group but are led to the inference that their selection was based in whole or in part upon the new variable introduced, namely knowledge of the selections made by the first group. ... However, at least as reported, this doesn't give any insight in its own terms into real world behaviours since it is not apparent that there is a community of listeners exercising choice based solely on perception of musical quality which is in any position to influence the choice of the putative second group. ...
    (uk.music.folk)
  • Re: OpenReport action cancelled because of Printer
    ... You should be able to import the toolbar from the sample database with: ... setting my report toolbars to 'ReportToolbar' caused ... printer (I just clicked 'OK' with the 'use default printer' selection, ...
    (microsoft.public.access.reports)
  • Re: Limit query results to 1 item
    ... "John Spencer" wrote: ... FROM tblFullSelectInfo INNER JOIN ... say most with selection date of 31 Mar ... If I have 30 people on my report with 3-4 dates each, how do I get it to ...
    (microsoft.public.access.queries)