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

Rabu, 20 Februari 2013

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

Hi Bill

I have never before seen and therefore have never before used the return tab - I see now how it works. Thanks for pointing that out - I will use it in the future.

Putting yes as the criteria in the core field means that only records with that value are returned - regardless of the use of the return tab to return all records or just the top 5.

This is not at all what I am after.

loads of bets are placed day by day
all bets have an account no
some of the bets are core bets and some are not
whether a bet is a core bet or not is nothing to do with account no - it is determined by some other criteria

I want to somehow get a list of the accounts where the LAST 5 bets placed on that account have ALL been core bets.

I haven't a clue how I would go about sending a zipped sample version of my database - that is something I would rather avoid having to do really

I hope I have explained what I am after accurately this time and maybe you have a further suggestion

much appreciated

Phil

________________________________
From: Bill Mosca wrmosca@comcast.net>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, 20 February 2013, 18:10
Subject: [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 mailto:MS_Access_Professionals%40yahoogroups.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: mailto:MS_Access_Professionals%40yahoogroups.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]
>

[Non-text portions of this message have been removed]