Re: maxrecords property doesn't work for me

Bob,
Thanks for the help. I just implemented your SQL statement and tested it.
It works great. I don't know SQL very well, so I tried implementing the
solution with code before asking for help. Your solution is quite elegant.
Thanks

"Bob Barrows [MVP]" wrote:

Bob Barrows [MVP] wrote:
PKI_Dave wrote:
Thanks. I'll give it a try if it results in what I need.
It isn't the same as the five lowest scores
since it will need to disregard scores posted prior to the 10th
oldest.

That's what my suggested query does.

The real
logic required makes this a little difficult. What I want is to find
the 10 most recent records and then sum the lowest 5 scores from
these last 10 records.

Well that can be accomplished by a further subquery

strSQL = "SELECT SUM(q1.CurrentDifferential) as scoretotal " & _
"FROM (
"SELECT TOP 5 q.ScoreDate, q.CurrentDifferential " & _
"FROM (" & _
"SELECT TOP 10 ScoreDate, CurrentDifferential FROM Score " & _
"WHERE PlayerName = '" & strPlayersName & _
"' ORDER BY ScoreDate DESC) As q " & _
"ORDER BY q.ScoreDate ASC) As q1"


Ah. Another problem - not really a typo. It should be:

strSQL = "SELECT SUM(q1.CurrentDifferential) as scoretotal " & _
"FROM (
"SELECT TOP 5 q.ScoreDate, q.CurrentDifferential " & _
"FROM (" & _
"SELECT TOP 10 ScoreDate, CurrentDifferential FROM Score " & _
"WHERE PlayerName = '" & strPlayersName & _
"' ORDER BY ScoreDate DESC) As q " & _
"ORDER BY q.CurrentDifferential ASC) As q1"


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.



.

Relevant Pages

  • Re: maxrecords property doesnt work for me
    ... It isn't the same as the five lowest scores ... "ORDER BY q.CurrentDifferential ASC) As q1" ... Microsoft MVP -- ASP/ASP.NET ... Please reply to the newsgroup. ...
    (microsoft.public.data.ado)
  • Re: Cant Add Fields to Form
    ... Look at the Record Source property of the form. ... the SQL statement or the saved query before they will be visible to the ... "Jeff Miller" wrote in message ... Hoping I'm posting to the right newsgroup. ...
    (microsoft.public.access.forms)
  • Re: Oracle query assistence
    ... ORDER BY PRODUCT_NAME ASC, REPORT_DATE ASC ... IT Manager/Oracle DBA ... ROW_NUMBEROVER (PARTITION BY T.PRODUCT_NAME ORDER BY ... Kind of make you wonder if this confusing SQL statement will also ...
    (comp.databases.oracle.server)
  • Re: Retriving data from DB
    ... rsRecordset("Name"), which name would I get from the recordset, the first ... > the fields in your table and generate this sql statement for you (and even ... > post to an Access newsgroup for help. ... >> tblSchedule and tlbScheduleAdd ...
    (microsoft.public.inetserver.asp.db)
  • Re: insert into error
    ... > Microsoft JET Database Engine ... > I've done a response.write with the SQL statement and I get this ... it's silly to be forced to worry about delimiters (let alone ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.db)