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

Graham-

Well, it's a bug. Access often gets confused with multiple levels of embedded queries.

I tried envisioning this as a JOIN on Account no, but can't make it work.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Graham Mandeno
Sent: Wednesday, February 27, 2013 7:03 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Re: Finding the last 3 values in a query

Hi Phil

I'm glad it did the trick for you :-)

I assume you are using the workaround with the VBA function. I'm still puzzled about why the SQL with the nested subqueries won't work...

John: Perhaps you wouldn't mind taking a look at it? The whole thread is below, but I've pulled the query out of context and copied it up here:

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.

Phil: Yes - shocking news about the fatal attack - the poor bugger was out swimming and strayed into a group of feeding sharks. No doubt he was mistaken for a large, juicy seal - I understand that most sharks aren't terribly enamoured of human flesh, and will spit it out if they accidentally get a mouthful. Fortunately it's a very rare occurrence in New Zealand waters - unlike Australia of South Africa. Extra fortunately, because I go swimming in the sea every morning and I'm not about to give it up!

Cheers,
Graham

_______________________________________
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com ] On Behalf Of Phil Knowles
Sent: Thursday, 28 February 2013 02:11
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Finding the last 3 values in a query

John - Thank you for pointing me in the right direction - a misplaced hyphen was the culprit.

Graham - wow - it works a treat - thank you so much for your help.

cheers

Phil

________________________________
From: John Viescas JohnV@msn.com >
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, 27 February 2013, 10:41
Subject: RE: [MS_AccessPros] Re: Finding the last 3 values in a query

Phil-

Please post the entire code - there's something amiss with the SQL you're assembling. Access considers any field reference to be a Parameter unless the name matches a field name in the tables in the FROM clause. You could be getting this error because you misspelled some names!

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: mailto:MS_Access_Professionals%40yahoogroups.com [mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of Phil Knowles
Sent: Wednesday, February 27, 2013 10:35 AM
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Finding the last 3 values in a query

Hi Graham

Yes, ticking that box seems to have overcome that problem.

However when I now run the query it comes up with a message box saying.

'Too few parameters. Expected 1'

Clearly the function requires 2 parameters so I am stuck again I'm afraid.

Big shock to hear about the NZ shark fatality this morning - what a way to go.

cheers

Phil

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

Hi Phil

It's possible your database was created in Access 2000 (?) when for a short while DAO was thought to be "DOA" and that reference was omitted from the VBA project by default.

In the VBA editor, go to Tools > References, and select the checkbox next to "Microsoft DAO 3.6 Object Library". You can probably unselect "Microsoft ActiveX Data Objects 2.x Library" if it is ticked.

Cheers,/
Graham

From: mailto:MS_Access_Professionals%40yahoogroups.com [mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of Phil Knowles
Sent: Friday, 22 February 2013 22:53
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: 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 mailto:graham%40mandeno.com >
To: mailto:MS_Access_Professionals%40yahoogroups.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]

[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]