Link to home
Start Free TrialLog in
Avatar of afreer2
afreer2

asked on

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:
https://www.experts-exchange.com/questions/21986765/querys-ranking.html
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>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
Avatar of afreer2
afreer2

ASKER

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"
SQL go boom when it hits FROM oldrankGroup By

Add a space between oldrank and Group
You can also change your Having clause to a Where clause, and move it before the Group BY
Avatar of afreer2

ASKER

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;
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;
Avatar of afreer2

ASKER

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

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.
Avatar of afreer2

ASKER

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

Avatar of afreer2

ASKER

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?
 
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

Avatar of afreer2

ASKER

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!
ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of afreer2

ASKER

I have no idea how it works but it does! Haven't tried last suggestion yet)
forever grateful:)
thank you
anson
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.