Working with a subset of a query's records

G

Guest

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?

thank you
 
M

Marshall Barton

Sophie said:
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?
Click to expand...


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.
 
G

Guest

Hello Damian and all
To try to understand SQL (which I'm new at), I'm working with a slightly
simplified case of the one I described earlier. Here's the essentials:

1) a table, tblDivScore has 3 text fields, Div (pk), DivName, DivType and 1
numerical field, Score.

2) a query, qryRankDiv has the following SQL: (which works well)

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

3) My form has 2 unbound comboboxes; cboSelectDivName and cboSelectDivType.
My Filter comes from the 2nd cbo's AfterUpdate event:
Me.Filter = "DivName = " & cboSelectDivName & "AND DivType = " &
cboSelectDivType

Here is my problem. The Ranking currently applies to ALL records in
qryRankDiv. I'd like to remove the Ranking capability from the qry and move
it somewhere so that it applies ONLY to my form's filtered results. Only the
filtered results should show (1, 2T, 2T, 3, etc) But.... I'm new enough to
Access that I'm just not sure how to go about accomplishing this.
 
G

Guest

Hi Sophie,

Perhaps you can post your ranking SQL and your query SQL so that we can see
what you are trying to achieve.

Damian.
 
G

Guest

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

Marshall Barton

Sophie said:
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] > .[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.
Click to expand...



Your query that worked for the whole table seem's to be
unnecessarily confusing. At least you should get rid ot the
Order By clauses in the subqueries that do not use the TOP
predicate. I also can't see a reason for the subquery in
the main FROM clause. Won't this generate the same ranking
over the entire table?

SELECT S.Div, S.DivName, S.DivType, S.Score,
(SELECT Count(*)
FROM (SELECT DISTINCT tblDivScore.Score
FROM tblDivScore) AS D
WHERE [D].[Score] > .[Score])+1 AS SRank,
IIf((SELECT Count(*)
FROM tblDivScore AS T
WHERE T.Score = S.Score ) > 1, "T", "") AS Tie,
[SRank] & [Tie] AS STRank
FROM tblDivScore AS S

If I have unraveled your query correctly, then try setting
the form RecordSource to this:

SELECT S.Div, S.DivName, S.DivType, S.Score,
(SELECT Count(*)
FROM (SELECT DISTINCT tblDivScore.Score
FROM tblDivScore
WHERE DivName = Forms!yourform.cboSelectDivName
And DivType = Forms!yourform.cboSelectDivType
) AS D
WHERE [D].[Score] > .[Score]
)+1 AS SRank,
IIf((SELECT Count(*)
FROM tblDivScore AS T
WHERE T.Score = S.Score
And DivName = Forms!yourform.cboSelectDivName
And DivType = Forms!yourform.cboSelectDivType
) > 1, "T", "") AS Tie,
[SRank] & [Tie] AS STRank
FROM tblDivScore AS S
WHERE DivName = Forms!yourform.cboSelectDivName
And DivType = Forms!yourform.cboSelectDivType

The combo box's AfterUpdate event would then only need to
use:
Me.Requery
 
G

Guest

Marshall
I very much appreciate the time you spend on these answers. I'm looking
forward to implementing of your ideas.
--
Thanks
Sophie


Marshall Barton said:
Sophie said:
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] > .[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.
Click to expand...



Your query that worked for the whole table seem's to be
unnecessarily confusing. At least you should get rid ot the
Order By clauses in the subqueries that do not use the TOP
predicate. I also can't see a reason for the subquery in
the main FROM clause. Won't this generate the same ranking
over the entire table?

SELECT S.Div, S.DivName, S.DivType, S.Score,
(SELECT Count(*)
FROM (SELECT DISTINCT tblDivScore.Score
FROM tblDivScore) AS D
WHERE [D].[Score] > .[Score])+1 AS SRank,
IIf((SELECT Count(*)
FROM tblDivScore AS T
WHERE T.Score = S.Score ) > 1, "T", "") AS Tie,
[SRank] & [Tie] AS STRank
FROM tblDivScore AS S

If I have unraveled your query correctly, then try setting
the form RecordSource to this:

SELECT S.Div, S.DivName, S.DivType, S.Score,
(SELECT Count(*)
FROM (SELECT DISTINCT tblDivScore.Score
FROM tblDivScore
WHERE DivName = Forms!yourform.cboSelectDivName
And DivType = Forms!yourform.cboSelectDivType
) AS D
WHERE [D].[Score] > .[Score]
)+1 AS SRank,
IIf((SELECT Count(*)
FROM tblDivScore AS T
WHERE T.Score = S.Score
And DivName = Forms!yourform.cboSelectDivName
And DivType = Forms!yourform.cboSelectDivType
) > 1, "T", "") AS Tie,
[SRank] & [Tie] AS STRank
FROM tblDivScore AS S
WHERE DivName = Forms!yourform.cboSelectDivName
And DivType = Forms!yourform.cboSelectDivType

The combo box's AfterUpdate event would then only need to
use:
Me.Requery
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Updating Multiple records 2
Return "No Matching Records" 2
Get a Max value from a continous form's (subset) records 5
Input a value in a form and display in a report 0
Update Query based on Current Recordset 26
Getting all records with same name passed from a textbox 6
Form doesn't sort records per the underlying query 46
How do I get a subset of a recordset created from a form??? 5

Top