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("S elect 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
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("S
" 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
ASKER
changed to:
Set rs = CurrentDb.OpenRecordset("S elect 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"
Set rs = CurrentDb.OpenRecordset("S
"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
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
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;
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;
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))
FROM race INNER JOIN oldRank ON race.RID = oldRank.RID
GROUP BY race.RID
ORDER BY race.RID DESC;
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("S elect 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
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("S
" 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
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.
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.
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
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],rac e.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;
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],rac
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
ASKER
had to change ptRK: fnRankRedo('pts',[pts]]) to
ptRK: fnRankRedo('pts',[pts],[ra ce].[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?
ptRK: fnRankRedo('pts',[pts],[ra
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],rac e.RID) AS ptRK, race.RID
FROM race INNER JOIN oldRank ON race.RID = oldRank.RID
GROUP BY fnRankRedo('pts',[pts],rac e.RID), race.RID
ORDER BY race.RID DESC;
to call the function:
SELECT fnRankRedo('pts',[pts],rac
FROM race INNER JOIN oldRank ON race.RID = oldRank.RID
GROUP BY fnRankRedo('pts',[pts],rac
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
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have no idea how it works but it does! Haven't tried last suggestion yet)
forever grateful:)
thank you
anson
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.
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.
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