|
|
Hi Marshall Barton,
Thank you very much for your earlier reply. I tried a ranking sub query but
I can't
seem to get the ranking to disregard ranking ties, in that the requirement is
to have a ranking sequential number e.g. instead of 1, 2, 2, 4, 5 or 1, 2, 2,
3, 4 I need to show 1, 2, 3, 4, 5. (I know it's an odd way of ranking but I
work
with odd people!).
If it is possible to disregard ranking ties, can you show me on the example
below,
SELECT tblRANK.COUNTRY, tblRANK.AMOUNT, (SELECT Count(*) As RANK FROM
tblRANK As Z WHERE Z.COUNTRY = tblRANK.COUNTRY And Z.Amount >
tblRANK.Amount)+1 AS RANK
FROM tblRANK
ORDER BY tblRANK.COUNTRY, tblRANK.AMOUNT DESC;
Thank you in advance, have a great day and take care.
regards
KM
"Marshall Barton" wrote:
[Quoted Text] > Kevin McCartney wrote:
> >I Have the following code, basically the code is to set the ranking number
> >against a set of records in a table called 'tblRANK'. The table contains
> >around 50,000 records and the ranking is across each country, in that the
> >ranking starts agian at 1 each time there is a new country, the table is
> >already sort by COUNTRY Asc and AMOUNT dec so the table is in the corret
> >order and the result is as expected.
> >
> >This issue is, that the database size explodes massively and I don't
> >understand why and thus don't know of a solution on how to correct it. I've
> >tried BeginTrans and CommitTrans but that only seems to delay the increase in
> >the database size until the end. I'm expecting it to do something with that
> >I'm using simple DAO Edit and Update. Would I get the same issue if I try ADO?
> >
> >
> > Dim wrkCurrent As DAO.Workspace
> > Dim dbCurrent As DAO.Database
> > Dim rstDATA As DAO.Recordset
> >
> > Dim varReturn As Variant
> > Dim strCOUNTRY As String
> > Dim lngRECORD As Long
> >
> > DAO.DBEngine.SetOption dbMaxLocksPerFile, 100000
> > Set wrkCurrent = DBEngine.Workspaces(0)
> > Set dbCurrent = CurrentDb
> > Set rstDATA = dbCurrent.OpenRecordset("tblRANK", dbOpenDynaset)
> >
> > strCOUNTRY = rstDATA!COUNTRY
> > lngRECORD = 1
> >
> > wrkCurrent.BeginTrans
> > Do Until rstDATA.EOF
> >
> > If strCOUNTRY <> rstDATA!COUNTRY Then
> > strCOUNTRY = rstDATA!COUNTRY
> > lngRECORD = 1
> > End If
> >
> > rstDATA.Edit
> > rstDATA!RANK = lngRECORD
> > rstDATA.Update
> > rstDATA.MoveNext
> >
> > lngRECORD = lngRECORD + 1
> > Loop
> > wrkCurrent.CommitTrans
>
>
> I suspect that the explosion is temp space need to process
> such a large recordset. Besides, that's not a good way to
> go about it.
>
> The ranking could change any time a record is added or
> edited, which means that you should not have a rank field in
> a table and that you need to recalculate the ranking every
> time you want to look at it or use it in a form or report.
>
> This can be done by using a query with a ranking subquery:
>
> SELECT tblRANK.*,
> (SELECT Count(*) As Rank
> FROM tblRANK As X
> WHERE X.Country = tblRANK.Country
> And X.Amount <= tblRANK.Amount) As Rank
> FROM tblRANK
>
> Then you can use this kind of query instead of using the
> table in a form or report.
>
> --
> Marsh
> MVP [MS Access]
>
|
|
Kevin sounds like you are looking for a Row Num not a Ranking. Try this:
SELECT Count(*) AS RowNum, a.Amount, a.Country
FROM (SELECT distinct Country, Amount FROM tblRank FROM Country is not null)
AS a,
(SELECT distinct Country, Amount FROM tblRank FROM Country is
not null) AS b
WHERE (format([a].[Amount],'0000000000') & a.Country) <=
(format([b].[amount],'0000000000') & b.Country)
GROUP BY a.Amount, a.Country
ORDER BY Count(*)
Should produce one row per country with a acending row num and decending
amount. Or have I got your requirements all bolixed up.
Rdub
"Kevin McCartney" <KevinMcCartney[ at ]discussions.microsoft.com> wrote in
message news:B91B2238-7CDB-4A41-8E6D-29092804E0D8[ at ]microsoft.com...
[Quoted Text] > Hi Marshall Barton,
>
> Thank you very much for your earlier reply. I tried a ranking sub query
> but
> I can't
> seem to get the ranking to disregard ranking ties, in that the requirement
> is
> to have a ranking sequential number e.g. instead of 1, 2, 2, 4, 5 or 1, 2,
> 2,
> 3, 4 I need to show 1, 2, 3, 4, 5. (I know it's an odd way of ranking but
> I
> work
> with odd people!).
>
> If it is possible to disregard ranking ties, can you show me on the
> example
> below,
>
> SELECT tblRANK.COUNTRY, tblRANK.AMOUNT, (SELECT Count(*) As RANK FROM
> tblRANK As Z WHERE Z.COUNTRY = tblRANK.COUNTRY And Z.Amount >
> tblRANK.Amount)+1 AS RANK
> FROM tblRANK
> ORDER BY tblRANK.COUNTRY, tblRANK.AMOUNT DESC;
>
> Thank you in advance, have a great day and take care.
>
> regards
> KM
>
> "Marshall Barton" wrote:
>
>> Kevin McCartney wrote:
>> >I Have the following code, basically the code is to set the ranking
>> >number
>> >against a set of records in a table called 'tblRANK'. The table contains
>> >around 50,000 records and the ranking is across each country, in that
>> >the
>> >ranking starts agian at 1 each time there is a new country, the table is
>> >already sort by COUNTRY Asc and AMOUNT dec so the table is in the corret
>> >order and the result is as expected.
>> >
>> >This issue is, that the database size explodes massively and I don't
>> >understand why and thus don't know of a solution on how to correct it.
>> >I've
>> >tried BeginTrans and CommitTrans but that only seems to delay the
>> >increase in
>> >the database size until the end. I'm expecting it to do something with
>> >that
>> >I'm using simple DAO Edit and Update. Would I get the same issue if I
>> >try ADO?
>> >
>> >
>> > Dim wrkCurrent As DAO.Workspace
>> > Dim dbCurrent As DAO.Database
>> > Dim rstDATA As DAO.Recordset
>> >
>> > Dim varReturn As Variant
>> > Dim strCOUNTRY As String
>> > Dim lngRECORD As Long
>> >
>> > DAO.DBEngine.SetOption dbMaxLocksPerFile, 100000
>> > Set wrkCurrent = DBEngine.Workspaces(0)
>> > Set dbCurrent = CurrentDb
>> > Set rstDATA = dbCurrent.OpenRecordset("tblRANK", dbOpenDynaset)
>> >
>> > strCOUNTRY = rstDATA!COUNTRY
>> > lngRECORD = 1
>> >
>> > wrkCurrent.BeginTrans
>> > Do Until rstDATA.EOF
>> >
>> > If strCOUNTRY <> rstDATA!COUNTRY Then
>> > strCOUNTRY = rstDATA!COUNTRY
>> > lngRECORD = 1
>> > End If
>> >
>> > rstDATA.Edit
>> > rstDATA!RANK = lngRECORD
>> > rstDATA.Update
>> > rstDATA.MoveNext
>> >
>> > lngRECORD = lngRECORD + 1
>> > Loop
>> > wrkCurrent.CommitTrans
>>
>>
>> I suspect that the explosion is temp space need to process
>> such a large recordset. Besides, that's not a good way to
>> go about it.
>>
>> The ranking could change any time a record is added or
>> edited, which means that you should not have a rank field in
>> a table and that you need to recalculate the ranking every
>> time you want to look at it or use it in a form or report.
>>
>> This can be done by using a query with a ranking subquery:
>>
>> SELECT tblRANK.*,
>> (SELECT Count(*) As Rank
>> FROM tblRANK As X
>> WHERE X.Country = tblRANK.Country
>> And X.Amount <= tblRANK.Amount) As Rank
>> FROM tblRANK
>>
>> Then you can use this kind of query instead of using the
>> table in a form or report.
>>
>> --
>> Marsh
>> MVP [MS Access]
>>
>
|
|
OH boy!
Really screwed up the Sql statement as I was (re)editing it in the Post.
Should have left it as I had Pasted it from ny test query. Should read.
SELECT Count(*) AS RowNum, a.Amount, a.Country
FROM
(SELECT distinct Country, Amount FROM tblRank WHERE Country is not null) AS
a,
(SELECT distinct Country, Amount FROM tblRank WHERE Country is not null) AS
b
WHERE (format([a].[Amount],'0000000000') & a.Country) <=
(format([b].[amount],'0000000000') & b.Country)
GROUP BY a.Amount, a.Country
ORDER BY Count(*)
Really sorry about that :-(
Rdub
"Ron Weiner" <rweineratworksritedotcom> wrote in message
news:OMdL6DWUJHA.5860[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > Kevin sounds like you are looking for a Row Num not a Ranking. Try this:
>
> SELECT Count(*) AS RowNum, a.Amount, a.Country
> FROM (SELECT distinct Country, Amount FROM tblRank FROM Country is not
> null) AS a,
> (SELECT distinct Country, Amount FROM tblRank FROM Country is
> not null) AS b
> WHERE (format([a].[Amount],'0000000000') & a.Country) <=
> (format([b].[amount],'0000000000') & b.Country)
> GROUP BY a.Amount, a.Country
> ORDER BY Count(*)
>
> Should produce one row per country with a acending row num and decending
> amount. Or have I got your requirements all bolixed up.
>
> Rdub
>
>
> "Kevin McCartney" <KevinMcCartney[ at ]discussions.microsoft.com> wrote in
> message news:B91B2238-7CDB-4A41-8E6D-29092804E0D8[ at ]microsoft.com...
>> Hi Marshall Barton,
>>
>> Thank you very much for your earlier reply. I tried a ranking sub query
>> but
>> I can't
>> seem to get the ranking to disregard ranking ties, in that the
>> requirement is
>> to have a ranking sequential number e.g. instead of 1, 2, 2, 4, 5 or 1,
>> 2, 2,
>> 3, 4 I need to show 1, 2, 3, 4, 5. (I know it's an odd way of ranking but
>> I
>> work
>> with odd people!).
>>
>> If it is possible to disregard ranking ties, can you show me on the
>> example
>> below,
>>
>> SELECT tblRANK.COUNTRY, tblRANK.AMOUNT, (SELECT Count(*) As RANK FROM
>> tblRANK As Z WHERE Z.COUNTRY = tblRANK.COUNTRY And Z.Amount >
>> tblRANK.Amount)+1 AS RANK
>> FROM tblRANK
>> ORDER BY tblRANK.COUNTRY, tblRANK.AMOUNT DESC;
>>
>> Thank you in advance, have a great day and take care.
>>
>> regards
>> KM
>>
>> "Marshall Barton" wrote:
>>
>>> Kevin McCartney wrote:
>>> >I Have the following code, basically the code is to set the ranking
>>> >number
>>> >against a set of records in a table called 'tblRANK'. The table
>>> >contains
>>> >around 50,000 records and the ranking is across each country, in that
>>> >the
>>> >ranking starts agian at 1 each time there is a new country, the table
>>> >is
>>> >already sort by COUNTRY Asc and AMOUNT dec so the table is in the
>>> >corret
>>> >order and the result is as expected.
>>> >
>>> >This issue is, that the database size explodes massively and I don't
>>> >understand why and thus don't know of a solution on how to correct it.
>>> >I've
>>> >tried BeginTrans and CommitTrans but that only seems to delay the
>>> >increase in
>>> >the database size until the end. I'm expecting it to do something with
>>> >that
>>> >I'm using simple DAO Edit and Update. Would I get the same issue if I
>>> >try ADO?
>>> >
>>> >
>>> > Dim wrkCurrent As DAO.Workspace
>>> > Dim dbCurrent As DAO.Database
>>> > Dim rstDATA As DAO.Recordset
>>> >
>>> > Dim varReturn As Variant
>>> > Dim strCOUNTRY As String
>>> > Dim lngRECORD As Long
>>> >
>>> > DAO.DBEngine.SetOption dbMaxLocksPerFile, 100000
>>> > Set wrkCurrent = DBEngine.Workspaces(0)
>>> > Set dbCurrent = CurrentDb
>>> > Set rstDATA = dbCurrent.OpenRecordset("tblRANK", dbOpenDynaset)
>>> >
>>> > strCOUNTRY = rstDATA!COUNTRY
>>> > lngRECORD = 1
>>> >
>>> > wrkCurrent.BeginTrans
>>> > Do Until rstDATA.EOF
>>> >
>>> > If strCOUNTRY <> rstDATA!COUNTRY Then
>>> > strCOUNTRY = rstDATA!COUNTRY
>>> > lngRECORD = 1
>>> > End If
>>> >
>>> > rstDATA.Edit
>>> > rstDATA!RANK = lngRECORD
>>> > rstDATA.Update
>>> > rstDATA.MoveNext
>>> >
>>> > lngRECORD = lngRECORD + 1
>>> > Loop
>>> > wrkCurrent.CommitTrans
>>>
>>>
>>> I suspect that the explosion is temp space need to process
>>> such a large recordset. Besides, that's not a good way to
>>> go about it.
>>>
>>> The ranking could change any time a record is added or
>>> edited, which means that you should not have a rank field in
>>> a table and that you need to recalculate the ranking every
>>> time you want to look at it or use it in a form or report.
>>>
>>> This can be done by using a query with a ranking subquery:
>>>
>>> SELECT tblRANK.*,
>>> (SELECT Count(*) As Rank
>>> FROM tblRANK As X
>>> WHERE X.Country = tblRANK.Country
>>> And X.Amount <= tblRANK.Amount) As Rank
>>> FROM tblRANK
>>>
>>> Then you can use this kind of query instead of using the
>>> table in a form or report.
>>>
>>> --
>>> Marsh
>>> MVP [MS Access]
>>>
>>
>
>
|
|
|