ACCESS FEED: [MS_AccessPros] Re: Finding the last 3 values in a query

Rabu, 20 Februari 2013

[MS_AccessPros] Re: Finding the last 3 values in a query

Phil

You can do it in design view. There is a dropdown in the query tab in 2010. The label reads "Return". In Acc 2003 there is no label. (Hey! that's the first improvement over 2003 I'll admit to).

Select from the list or just type in a value.

Join the main table and the Category table on the account number. sort the date field as Descending. put criteria "core" under the category field.

If that doesn't get you what you want you can always upload a zipped sample database to our Assistance needed folder in the Files section.

Bill

--- In MS_Access_Professionals@yahoogroups.com, Phil Knowles wrote:
>
> Hi Bill
> Â
> thanks for your prompt reply.
> Â
> i'm still not getting it.
> Â
> I create my queries using design view and don't seem to be able to do this in that view (or can I?) Â but I have changed to sql view to add this TOP function to no avail - it is returning 5 records but not in the way I am looking for.
> Â
> It is a bit more complicated than I first described - there are 2 tables and the field that I am testing for 5 occurances in the last 5 records of the 1st table is in the 2nd table
> Â
> Table 1 - BetsÂ has the following relevant fieldsÂ Â date placed, account no, category
> Table 2 - Categories is linked by the field category and has a relevant field called core
> Â
> I want to a list of accounts where the last 5 bets (by date placed) have had a category which has core=yes
> Â
> so i don't necessarily want the detail of the last 5 records which fulfil the criteria - I just want to know the account nos
> Â
> Is the TOP function still what I need to achieve this?
> Â
> Sorry I wasn't clear enough before - am I this time ??!!
> Â
> cheers
> Â
> Phil
>
>
> ________________________________
> From: Bill Mosca
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Wednesday, 20 February 2013, 14:54
> Subject: [MS_AccessPros] Re: Finding the last 3 values in a query
>
>
> Â
>
> Phil
>
> Easy-squeezy!
> SELECT TOP 5 *
> FROM MyTable
> WHERE Color = "Green"
> ORDER BY SalesDate DESC
>
> DESC means decending or reverse order. The TOP operator returns the stated number of records. You can also use TOP 5% if you want a percentage of records.
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com/
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> My nothing-to-do-with-Access blog
> http://wrmosca.wordpress.com/
>
> --- In mailto:MS_Access_Professionals%40yahoogroups.com, "pdk444444" wrote:
> >
> > Hi
> >
> > I am trying to do something that seems straightforward but I am struggling to think of a solution and I wondered if anyone can help me out.
> >
> > 2 fields on my sales table are called product and colour (it's just an example so I'm not interested in whether design is right or wrong)
> >
> > I want a query to list any products whose last 5 sales have been green.
> >
> > Probably dead easy but I am not seeing it - help please.
> >
> > Phil
> >
>
>
>
>
> [Non-text portions of this message have been removed]
>