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

Hi Graham

I tried the public function (it is numeric)

but when I ran the query the debugger didn't seem to like

Dim rs As DAO.Recordset

?? I haven't done any public function programming before!

cheers

Phil

________________________________
From: Graham Mandeno graham@mandeno.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, 21 February 2013, 8:48
Subject: RE: [MS_AccessPros] Re: Finding the last 3 values in a query

[I just looked at what I posted this morning and the formatting is complete bollocks! I'll try again in plain text]

Hi Phil

Not a problem! That's just something that goes with the territory of working with someone on the other side of the world :-)

I'm basing this solution on the fact that a boolean field contains -1 for a True value (or +1 on SQL Server) and 0 for a False value. Therefore, if you sum a bunch of them and take the absolute value, it will give you the number of True ("yes") values.

The following query will list the [core] values of the latest five bets for a given account number:

SELECT TOP 5 Categories.core FROM Categories INNER JOIN Bets
ON Categories.[category id] = Bets.category
WHERE Bets.[account no] = [enter an account no]
ORDER BY Bets.[date placed] DESC;

(Note that more than five records could be returned if there are two or more fifth-equal records)

Using this as a subquery, we can return the number of these last five records with True values:

SELECT Abs(Sum(core)) AS NumCore
FROM (SELECT TOP 5 Categories.core FROM Categories INNER JOIN Bets
ON Categories.[category id] = Bets.category
WHERE Bets.[account no] = [enter an account no]
ORDER BY Bets.[date placed] DESC);

If the number of records with True values is five (or more) then there were no non-core bets in the last five, and that account number should be selected.

So, this query _should_ work, but it does not:

SELECT Accounts.[account no]
FROM Accounts
WHERE (SELECT Abs(Sum(core)) AS NumCore
FROM (SELECT TOP 5 Categories.core FROM Categories INNER JOIN Bets
ON Categories.[category id] = Bets.category WHERE Bets.[account no] = Accounts.[account no]
ORDER BY Bets.[date placed] DESC)) >= 5;

Instead of using Accounts.[account no] as the parameter for the subquery, it displays a parameter prompt asking for Accounts.account no. Perhaps someone who is a bit smarter with SQL than me can spot the problem and jump in?

In the meantime, a workaround is to write a function to return the number of core bets in the latest N for a given account:

Public Function CountCoreBetsInLastN(AccountNo As Long, N As Long) As Long
Dim rs As DAO.Recordset, sSQL As String
On Error GoTo ProcErr
sSQL = "SELECT Abs(Sum(core)) AS NumCore FROM (SELECT TOP " & N _
& " Categories.core FROM Categories INNER JOIN Bets" _
& " ON Categories.[category id] = Bets.category WHERE Bets.[account no] = " _
& AccountNo & " ORDER BY Bets.[date placed] DESC)"
Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenForwardOnly)
CountCoreBetsInLastN = Nz(rs!NumCore, 0)
ProcEnd:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function

This can be used in a very simple query like this:

SELECT Accounts.[account no]
FROM Accounts
WHERE CountCoreBetsInLastN(Accounts.[account no], 5) >= 5;

Note that I am assuming the account no field is numeric. If it is text, then you will need to alter the function to use a string argument (note the extra single quotes in the SQL string):

Public Function CountCoreBetsInLastN(AccountNo As String, N As Long) As Long
Dim rs As DAO.Recordset, sSQL As String
On Error GoTo ProcErr
sSQL = "SELECT Abs(Sum(core)) AS NumCore FROM (SELECT TOP " & N _
& " Categories.core FROM Categories INNER JOIN Bets" _
& " ON Categories.[category id] = Bets.category WHERE Bets.[account no] = '" _
& AccountNo & "' ORDER BY Bets.[date placed] DESC)"
Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenForwardOnly)
CountCoreBetsInLastN = Nz(rs!NumCore, 0)
ProcEnd:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function

You're probably nearly awake again now – I didn't expect to puzzle over this for so long! :-)

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 11:38
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Finding the last 3 values in a query

Hi Graham

Please don't think I'm rude if I don't reply to anything you send but I'm going to bed now!
I will have a look tomorrow with great interest to see if you have come up with anything.

much appreciated

Phil

________________________________
From: Graham Mandeno mailto:graham%40mandeno.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Wednesday, 20 February 2013, 22:11
Subject: RE: [MS_AccessPros] Re: Finding the last 3 values in a query

Oh, and one other question – do you have a separate Accounts table with [account no] as the primary key?

From: mailto:MS_Access_Professionals%40yahoogroups.com [mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of Graham Mandeno
Sent: Thursday, 21 February 2013 11:07
To: mailto:MS_Access_Professionals%40yahoogroups.com
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]

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

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