Re: Working with a subset of a query's records
- From: Sophie <Sophie@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 20 Feb 2007 15:34:11 -0800
Hello Marshall
I completely new at SQL and I have to admit that its driving me crazy. (My
husband thinks it's an acronym for Seems Quite Loony!) In my project, this
is the last big (for me) hurdle, but I just can't seem to get this to work.
Here's the essentials:
1) My form's source is a table, tblDivScore, having fields, Div (pk),
DivName, DivType and Score.
2) This form has 2 unbound comboboxes (cboSelectDivName and
cboSelectDivType) that are used to filter the records in the table. This
works well. Currently, the 2nd cbo's AfterUpdate event provides the following
filter:
Me.Filter = "DivName = " & cboSelectDivName & "AND DivType = " &
cboSelectDivType
Me.FilterOn = True
3) When I was trying to understand Ranking, I developed SQL that works
perfectly when applied to the FULL, UNFILTERED TABLE (giving tie-handled
ranks like 1, 2T, 2T, 3...) This 'test' SQL, called qryRankDivs, is shown
below:
SELECT S.Div, S.DivName, S.DivType, S.Score, (SELECT Count(*) FROM (SELECT
DISTINCT tblDivScore.Score
FROM tblDivScore
ORDER BY tblDivScore.Score DESC) AS D WHERE [D].[Score] > [S].[Score])+1 AS
SRank, IIf((SELECT Count(*) FROM (SELECT tblDivScore.Score, tblDivScore.Div
FROM tblDivScore
ORDER BY tblDivScore.Score DESC , tblDivScore.Div) AS T WHERE T.Score =
S.Score )>1,"T","") AS Tie, [SRank] & [Tie] AS STRank
FROM [SELECT tblDivScore.Score, tblDivScore.Div, tblDivScore.DivName,
tblDivScore.DivType
FROM tblDivScore
ORDER BY tblDivScore.Score DESC , tblDivScore.Div]. AS S;
Here is my problem. The Ranking SQL, qryRankDiv, works well when applied to
ALL records in
tblDivScore, but I'd like to remove the Ranking capability from the qry and
apply it ONLY to my form's filtered results. Only the filtered results
should show (1, 2T, 2T, 3, etc) I just can't seem to get this to work. I
would be SO appreciative of help with this.
--
Thanks
Sophie
"Marshall Barton" wrote:
Sophie wrote:.
I know I'm probably missing something simple, but here goes.
I have a form based on a query. The query has many hundreds of records, but
the form uses code behind 3 comboboxes to build a filter so that only a
subset of the records are shown on the form. Now I want to Rank these
records. I've written some SQL that works well with great help from the
experts in this forum! (Ranking - Dealing with ties, Feb 17)
My problem is that I don't know where to put this Ranking SQL. I don't want
to apply it to all the records in the query, only the filtered ones showing
on the form. I seem to be stuck on this point and would appreciate an
assist. How is this generally done?
Instead of using the Filter property (which doesn't always
do wha you want it to do), construct a new record source
query.
With the ranking subquery, the code might look a little
messy, but the approach is pretty straightforward.
Dim strSQL As String
Dim strWhere As String
strWhere = <whatever you are using as the filter>
strSQL = "SELECT f1,f2,f3, . . ., " _
& "(SELECT Count(*) + 1 FROM something As X " _
& "WHERE X.score < T.score " _
& "And " & strWhere & ") As Rank " _
& "FROM something As T " _
& "WHERE " & strWhere
Me.RecordSource = strSQL
Actually that may not be all that helpful. If it isn't,
please post a Copy/Paste of your current record source query
along with your ranking query.
--
Marsh
MVP [MS Access]
- Follow-Ups:
- Re: Working with a subset of a query's records
- From: Marshall Barton
- Re: Working with a subset of a query's records
- References:
- Re: Working with a subset of a query's records
- From: Marshall Barton
- Re: Working with a subset of a query's records
- Prev by Date: Re: dirty (?) footer
- Next by Date: Re: Lookup queries
- Previous by thread: Re: Working with a subset of a query's records
- Next by thread: Re: Working with a subset of a query's records
- Index(es):
Relevant Pages
|
