SQL statment in recordset()

I have two tables a and b
a's primary key [rid] (a string)
b has a forgin key[rid] from a
I don't know how to group b's [rid](about 12 records each)of 1000+ records, use a
ranking function for b.[pts] to get b.[ptrk] and update b.[ptrk].


Set rs = CurrentDb.OpenRecordset("Select oldrank.[" & strField & "] As FieldVal FROM oldrank" & _
                  " where oldrank.rid= oldrank.rid Group By oldrank.[rid],OLDRANK.[" & strField & "] Having len([" & strField & "]) >0" & _
                           " Order By [" & strField & "] Desc")

this works without the where statment(but doesn't group [rid]
I don't know if I group in rs of the query that calls the function.

helped by eghtebas  in:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21986765.html
afreer2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Group By oldrank.[rid],OLDRANK.[" & strField & "]
You have rid in your GROUP BY clause, so you also have to have it in your SELECT clause, or else it'll kick out an error.

Select oldrank.rid, [" & strField & "] As FieldVal
0
afreer2Author Commented:
changed to:

Set rs = CurrentDb.OpenRecordset("Select oldrank.rid,oldRank.[" & strField & "] As FieldVal FROM oldrank" & _
                 "Group By oldrank.[rid],OLDRANK.[" & strField & "] Having len([" & strField & "]) >0" & _
                           " Order By [" & strField & "] Desc")
now get error 3131" syntax error in FROM clause"
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SQL go boom when it hits FROM oldrankGroup By

Add a space between oldrank and Group
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You can also change your Having clause to a Where clause, and move it before the Group BY
0
afreer2Author Commented:
did both suggestions one at a time still no grouping of rid
the if statement in the function
)If rs.RecordCount > 0 Then)
should be 6 to 12 records but is 1000+ records
the result is 0 to 1000+ rsnk
the query I use ito call the function(not a UPDATE SQL yet )

SELECT fnRankRedo('pts',[pts]) AS ptRK, race.RID
FROM race INNER JOIN oldRank ON race.RID = oldRank.RID
GROUP BY fnRankRedo('pts',[pts]), race.RID
ORDER BY race.RID DESC;
0
ee_rleeCommented:
hi

how does fnRankRedo work? you are now grouping all of your select fields so this is just working like a distinct function. to group by RID, you change

GROUP BY fnRankRedo('pts',[pts]), race.RID
to
GROUP BY race.RID

and you must know what to do with th pts. if you grouped all of the RID into 1 record, what will happend to their pts? will you add all the pts then call the function?

i.e:

SELECT fnRankRedo('pts',SUM(pts)) AS ptRK, race.RID
FROM race INNER JOIN oldRank ON race.RID = oldRank.RID
GROUP BY race.RID
ORDER BY race.RID DESC;
0
afreer2Author Commented:
ee_rlee::
attached the function
the pts are stand alone numbers to be ranked.
your "FROM race INNER JOIN oldRank ON race.RID = oldRank.RID"  looks like it might work

Set rs = CurrentDb.OpenRecordset("Select tempR.[" & strField & "] As FieldVal FROM race INNER JOIN tempR ON race.RID = tempR.rid" & _
                  " Where race.[RACE #]=" & intRaceNo & " Group By tempR.[" & strField & "] Having len([" & strField & "]) >0" & _
                           " Order By [" & strField & "] Desc")

above is the orginal rs that I tried to adapt
it works as long as the race day is the same.

the race table connects all detail tables
all detail structures are the same except for table names i
i e:
table a                          tbl b
AID                               BID
RID                               RID
detailS of A tbl  AND TBL B and so on

will attempt ot make changes
thanks for your interest
anson

Function fnRankReDo(strField As String, varField As Variant) As Integer
Dim intTemp As Integer
Dim rs As DAO.Recordset
 
 
 
Set rs = CurrentDb.OpenRecordset("Select oldrank.rid,oldRank.[" & strField & "] As FieldVal FROM oldrank " & _
                "Where len([" & strField & "]) >0 Group By oldrank.[rid],OLDRANK.[" & strField & "] " & _
                           " Order By [" & strField & "] Desc")
 
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
intTemp = intTemp + 1
  If rs!Fieldval = varField Then
          GoTo 10
  End If
 
rs.MoveNext
Loop
End If
10:
rs.Close
 
If Nz(varField, "") = "" Or varField = 0 Then
  fnRankReDo = 0
Else
  fnRankReDo = intTemp
End If
 
End Function

Open in new window

0
ee_rleeCommented:
sorry, i still don't quite understand what you want to do.

if i'm not mistaken, your function returns the 'rank' of a value in a field. are you trying to rank your raceid based on a its pts?

can you post some data and your desired result? it could help me understand what you want to do.
0
afreer2Author Commented:
RID                                   pts                   ptrk
DMR Jul20y07r01              50                    4
DMR Jul20y07r01              30                     5
DMR Jul20y07r01               100                  1
DMR Jul20y07r01               55                    3
DMR Jul20y07r01               25                     6
DMR Jul20y07r01                75                    2
DMR Jul20y07r02
DMR Jul20y07r02
1000+more recs
the problem comes up if  DMR Jul21y07r01 etc. (the next race date)
then it seems it groups [race #] along with rid
if i remove
Where race.[RACE #]=" & intRaceNo
rank shows 0 to 1000+
and not 0 to 6-12
hope this helps narrow problem
0
ee_rleeCommented:
okay, now i understand your problem.

are you using fnRankReDo to compute for your rank?
you should also pass the rid so that it will only compute the rank with the same rank id. try the code below

to call the function:
SELECT fnRankRedo('pts',[pts],race.RID) AS ptRK, race.RID
FROM race INNER JOIN oldRank ON race.RID = oldRank.RID
GROUP BY fnRankRedo('pts',[pts]), race.RID
ORDER BY race.RID DESC;
Function fnRankReDo(strField As String, varField As Variant, strrid as String) As Integer
Dim intTemp As Integer
Dim rs As DAO.Recordset
 
 
 
Set rs = CurrentDb.OpenRecordset("Select oldrank.rid,oldRank.[" & strField & "] As FieldVal FROM oldrank " & _
                "Where len([" & strField & "]) >0 Where oldrank.rid = '" & strid & "'" & _
                           " Order By [" & strField & "] Desc")
 
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
intTemp = intTemp + 1
  If rs!Fieldval = varField Then
          GoTo 10
  End If
 
rs.MoveNext
Loop
End If
10:
rs.Close
If Nz(varField, "") = "" Or varField = 0 Then
  fnRankReDo = 0
Else
  fnRankReDo = intTemp
End If
 
End Function

Open in new window

0
afreer2Author Commented:
had to change ptRK: fnRankRedo('pts',[pts]])  to
ptRK: fnRankRedo('pts',[pts],[race].[RID]) because too few perimeters(unchecked box)
strid needed to be defined
but boom here:
I tried to change some " to' but still:
missing operator vvvv
"Where len([" & strField & "]) >0 Where oldrank.rid = '" & strid & "' " & _
                           " Order By [" & strField & "] Desc")
does the unchecked box in query pass the RID?
since all my detail tables struc. are the same I just changed tbl name in function.
I thought it would work with all tables.
does query have a limit on number if records when using a function like this one?
 
0
ee_rleeCommented:
sorry, i made a mistake in the query, pls try again

to call the function:
SELECT fnRankRedo('pts',[pts],race.RID) AS ptRK, race.RID
FROM race INNER JOIN oldRank ON race.RID = oldRank.RID
GROUP BY fnRankRedo('pts',[pts],race.RID), race.RID
ORDER BY race.RID DESC;
Function fnRankReDo(strField As String, varField As Variant, strrid as String) As Integer
Dim intTemp As Integer
Dim rs As DAO.Recordset
 
 
 
Set rs = CurrentDb.OpenRecordset("Select oldrank.rid,oldRank.[" & strField & "] As FieldVal FROM oldrank " & _
                "Where len([" & strField & "]) >0 AND oldrank.rid = '" & strid & "'" & _
                           " Order By [" & strField & "] Desc")
 
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
intTemp = intTemp + 1
  If rs!Fieldval = varField Then
          GoTo 10
  End If
 
rs.MoveNext
Loop
End If
10:
rs.Close
If Nz(varField, "") = "" Or varField = 0 Then
  fnRankReDo = 0
Else
  fnRankReDo = intTemp
End If
 
End Function

Open in new window

0
afreer2Author Commented:
results are a 0's
the rs.count is 0
your sorry? You work with little info and still are able to come up with something,I 'm the sorry one for putting you up with a old guy that does this as a hobble!
0
ee_rleeCommented:
i modified the function a little bit. please try if this works.

i also wrote a new query below. it should work the same way without using the function.
Function fnRankReDo(strField As String, varField As Variant, strid as string) As Integer
Dim intTemp As Integer
Dim rs As DAO.Recordset
 
Set rs = CurrentDb.OpenRecordset("Select oldrank.rid,oldRank.[" & strField & "] As FieldVal FROM oldrank " & _
                "Where len([" & strField & "]) >0 Group By oldrank.[rid],OLDRANK.[" & strField & "] " & _
                           " Order By [" & strField & "] Desc")
 
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
if rs!rid = strid then
  intTemp = intTemp + 1
  If rs!Fieldval = varField Then
          GoTo 10
  End If
end if
rs.MoveNext
Loop
End If
10:
rs.Close
 
If Nz(varField, "") = "" Or varField = 0 Then
  fnRankReDo = 0
Else
  fnRankReDo = intTemp
End If
 
End Function
 
or try this
 
SELECT race.rid, t.ptRK
FROM race INNER JOIN 
     (SELECT rid, COUNT(*) AS ptRK FROM oldRank WHERE len([pts])>0 AND [pts]>=race.[pts] GROUP BY rid) as t
     ON race.RID=t.RID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
afreer2Author Commented:
I have no idea how it works but it does! Haven't tried last suggestion yet)
forever grateful:)
thank you
anson
0
ee_rleeCommented:
you're welcome.

here is how it works. your previous code checks all the values with pts greater than your current pts without considering the rid. thus i added a parameter to your function (strid), and use it such that only those with the same rid will be counted.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.