Hi Graham
Thanks for joining in
1. Yes.
2. No
3. Yes
4. Yes
5. Yes
6. Category id
in actual fact I have used category just to slightly simplify my example - the field on bets is actually sub-category and the primary key on the betting sub categories table is sub cat id - I have stuck with 'categories' in 6 above but hopefully I can translate any answer you give to adapt it back to work for sub-category
cheers
Phil
________________________________
From: Graham Mandeno graham@mandeno.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, 20 February 2013, 22:06
Subject: RE: [MS_AccessPros] Re: Finding the last 3 values in a query
Hi Phil
This is quite a tricky query. Could you please clarify a couple of things?
1. If one or more of the five most recent bets for a particular account is of a category which is NOT "core=yes", then that account should be excluded. Is this correct?
2. Should an account be included if it has fewer than five bets in total, but all of them are "core=yes" categories?
3. Is it possible for an account to have two bets with exactly the same value for [date placed]
4. If so, and the fifth-equal-to-last bets are of different categories, one core and one non-core, should that account be included?
5. Is core a boolean (yes/no) field?
6. What is the name of the primary key field in the Categories table?
Well, that was more than a couple of questions, but we're not very good at counting down here in New Zealand ;-)
Cheers,
Graham
From: mailto:MS_Access_Professionals%40yahoogroups.com [mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of Phil Knowles
Sent: Thursday, 21 February 2013 06:37
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Finding the last 3 values in a query
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 mailto:wrmosca%40comcast.net >
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]
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (8) |
Tidak ada komentar:
Posting Komentar