Re: Limit query results to 1 item

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

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)
  • Need help with multi threading, got error "Cross-thread operation.
    ... SortFields sortFields = rdLog.DataDefinition.SortFields; ... selection.", MessageBoxButtons.OK, MessageBoxIcon.Information); ... goto CleanUp; ... or clik the OK button to continue creating the report? ...
    (microsoft.public.dotnet.languages.csharp)