Report [Reports]

Prev: Detect when detail section pushes to 2nd page
Next: Report Grand Total Miscalculating Overtime

From: Per Erik on 10 May 2010 06:45

I'm using SQL RANK because I want a proper ranking.
Like this:
1. Person1 - 350 gr
* 2 Person2 - 340 gr
* 2 Person3 - 340 gr
4. Person4 - 320 gr

Those who have the same weight, should get the same space-digits. Like my
example above.

SQL statement in the query: SELECT A. GRAVITY, A. NAME, A. Club, A. CLASS,
(SELECT Count (*) FROM THE LORD AS B WHERE B. GRAVITY> GRAVITY A.) +1 AS Rank
FROM THE LORD AS A;

"Duane Hookom" wrote:

> You need to figure out a different method for the RANK. Is there a reason
> why you can't use a running sum in the report to display the rank?
>
> If you can't, you may need to post something about the report's record
> source including significant SQL views.
> --
> Duane Hookom
> MS Access MVP
>
>
> "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
> news:14C96427-CED4-481A-80AC-207145231274(a)microsoft.com...
> > Yes I have used a subquery called RANK (NR), and this will be sorted to
> > produce the correct results.
> > I get the error message when I put the txtCountAll to the Report Header
> > section.
> >
> > "Duane Hookom" wrote:
> >
> >> Sounds like a issue with the record source of your report. I expect you
> >> used
> >> a subquery to get the NR field. That's a no-no if you intend to sort by
> >> it.
> >>
> >> --
> >> Duane Hookom
> >> MS Access MVP
> >>
> >>
> >> "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
> >> news:5CD5C593-A53A-4608-BF46-31697B34DB11(a)microsoft.com...
> >> > I get an error message: "Multi-level GROUP BY clause is not allowed in
> >> > a
> >> > subquery".
> >> >
> >> > I have created everything according to your commandments, and the code
> >> > in
> >> > the detail section is:
> >> > Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
> >> > Me.Mark.Visible = (Me.txtRunCount = Me.txtCountAll \ 3)
> >> > End Sub
> >> >
> >> > Per Erik
> >> >
> >> > "Duane Hookom" wrote:
> >> >
> >> >> Tell us how you have your report set up including the code that is run
> >> >> and
> >> >> which section.
> >> >>
> >> >> --
> >> >> Duane Hookom
> >> >> MS Access MVP
> >> >>
> >> >>
> >> >> "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
> >> >> news:D22CFF97-51D1-4D79-988C-DDCDD31E0C9E(a)microsoft.com...
> >> >> > The controls are visible in all the lines, not only in the 1/3.
> >> >> >
> >> >> > "Duane Hookom" wrote:
> >> >> >
> >> >> >> It would help if you told us wasn't working. What are your results?
> >> >> >> If
> >> >> >> the
> >> >> >> controls are all visible, what do you see?
> >> >> >>
> >> >> >> My suggestion show have displayed the control named Mark every 1/3
> >> >> >> of
> >> >> >> the
> >> >> >> record count. I think you need to change the code to:
> >> >> >>
> >> >> >>
> >> >> >> Private Sub Detail_Format(Cancel As Integer, FormatCount As
> >> >> >> Integer)
> >> >> >> Me.Mark.Visible = (Me.txtRunCount = Me.txtCountAll \ 3
> >> >> >> End Sub
> >> >> >>
> >> >> >> --
> >> >> >> Duane Hookom
> >> >> >> MS Access MVP
> >> >> >>
> >> >> >>
> >> >> >> "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
> >> >> >> news:89E9A348-9496-4C22-A00F-61572952126E(a)microsoft.com...
> >> >> >> > Like this:
> >> >> >> > NR. NAVN KLUBB VEKT
> >> >> >> > 1 Jim Bekken Raufjøringen 9510 gr.
> >> >> >> > 2 Thomas Ødergård Gjøvik og Toten SFK 8105 gr.
> >> >> >> > 3 Kenneth Ottosen Gjøvik og Toten SFK 5440 gr.
> >> >> >> > 4 Jørgen Langerud Raufjøringen 5245 gr.
> >> >> >> > 5 Ove Lauten Gjerdrum 4745 gr.
> >> >> >> > ****
> >> >> >> > 6 Per E. Hellerud Eidsvoll JFF 4625 gr.
> >> >> >> > 7 Frank Hønsen Gjerdrum SFK 4525 gr.
> >> >> >> > 8 Odd Henning Hansen Gjøvik og Toten SFK 4505 gr.
> >> >> >> > 9 Johnny Ulsrudstuen Gjøvik og Toten SFK 4255 gr.
> >> >> >> > 10 Anders Wold Oslo Sportsfiske 3780 gr.
> >> >> >> > 11 Tom Pedersen Gjøvik og Toten SFK 3325 gr.
> >> >> >> > 12 Eric Olstad Toten JFF Lodd 2 3040 gr.
> >> >> >> > 13 Geir Lillejordet Gjøvik og Toten SFK 2735 gr.
> >> >> >> > 14 Remi A. Olsen SFK Pimpel Sør 2540 gr.
> >> >> >> > 15 Paal Runden Gjøvik og Toten SFK 2495 gr.
> >> >> >> >
> >> >> >> > "Per Erik" wrote:
> >> >> >> >
> >> >> >> >> Hey! First, sorry my bad English!
> >> >> >> >> I have created a results page of a report in Access 2007.
> >> >> >> >> I want to make a mark AFTER the first third of the report. So if
> >> >> >> >> there
> >> >> >> >> are
> >> >> >> >> 33 participants, I would like it to be characterized by the
> >> >> >> >> eleventh.
> >> >> >> >> Is
> >> >> >> >> this
> >> >> >> >> possible in a report?
> >> >> >> >> Hope someone can help me with this!
> >> >> >>
> >> .
> >>
> .
>

From: Per Erik on 10 May 2010 06:51

"Per Erik" wrote:

> I'm using SQL RANK because I want a proper ranking.
> Like this:
> 1. Person1 - 350 gr
> * 2 Person2 - 340 gr
> * 2 Person3 - 340 gr
> 4. Person4 - 320 gr
>
> Those who have the same weight, should get the same space-digits. Like my
> example above.
>
> SQL statement in the query: SELECT A.VEKT, A.NAVN, A.KLUBB, A.KLASSE, (SELECT Count(*) FROM HERRER AS B WHERE B.VEKT>A.VEKT)+1 AS Rank
FROM HERRER AS A;

>
>
> "Duane Hookom" wrote:
>
> > You need to figure out a different method for the RANK. Is there a reason
> > why you can't use a running sum in the report to display the rank?
> >
> > If you can't, you may need to post something about the report's record
> > source including significant SQL views.
> > --
> > Duane Hookom
> > MS Access MVP
> >
> >
> > "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
> > news:14C96427-CED4-481A-80AC-207145231274(a)microsoft.com...
> > > Yes I have used a subquery called RANK (NR), and this will be sorted to
> > > produce the correct results.
> > > I get the error message when I put the txtCountAll to the Report Header
> > > section.
> > >
> > > "Duane Hookom" wrote:
> > >
> > >> Sounds like a issue with the record source of your report. I expect you
> > >> used
> > >> a subquery to get the NR field. That's a no-no if you intend to sort by
> > >> it.
> > >>
> > >> --
> > >> Duane Hookom
> > >> MS Access MVP
> > >>
> > >>
> > >> "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
> > >> news:5CD5C593-A53A-4608-BF46-31697B34DB11(a)microsoft.com...
> > >> > I get an error message: "Multi-level GROUP BY clause is not allowed in
> > >> > a
> > >> > subquery".
> > >> >
> > >> > I have created everything according to your commandments, and the code
> > >> > in
> > >> > the detail section is:
> > >> > Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
> > >> > Me.Mark.Visible = (Me.txtRunCount = Me.txtCountAll \ 3)
> > >> > End Sub
> > >> >
> > >> > Per Erik
> > >> >
> > >> > "Duane Hookom" wrote:
> > >> >
> > >> >> Tell us how you have your report set up including the code that is run
> > >> >> and
> > >> >> which section.
> > >> >>
> > >> >> --
> > >> >> Duane Hookom
> > >> >> MS Access MVP
> > >> >>
> > >> >>
> > >> >> "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
> > >> >> news:D22CFF97-51D1-4D79-988C-DDCDD31E0C9E(a)microsoft.com...
> > >> >> > The controls are visible in all the lines, not only in the 1/3.
> > >> >> >
> > >> >> > "Duane Hookom" wrote:
> > >> >> >
> > >> >> >> It would help if you told us wasn't working. What are your results?
> > >> >> >> If
> > >> >> >> the
> > >> >> >> controls are all visible, what do you see?
> > >> >> >>
> > >> >> >> My suggestion show have displayed the control named Mark every 1/3
> > >> >> >> of
> > >> >> >> the
> > >> >> >> record count. I think you need to change the code to:
> > >> >> >>
> > >> >> >>
> > >> >> >> Private Sub Detail_Format(Cancel As Integer, FormatCount As
> > >> >> >> Integer)
> > >> >> >> Me.Mark.Visible = (Me.txtRunCount = Me.txtCountAll \ 3
> > >> >> >> End Sub
> > >> >> >>
> > >> >> >> --
> > >> >> >> Duane Hookom
> > >> >> >> MS Access MVP
> > >> >> >>
> > >> >> >>
> > >> >> >> "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
> > >> >> >> news:89E9A348-9496-4C22-A00F-61572952126E(a)microsoft.com...
> > >> >> >> > Like this:
> > >> >> >> > NR. NAVN KLUBB VEKT
> > >> >> >> > 1 Jim Bekken Raufjøringen 9510 gr.
> > >> >> >> > 2 Thomas Ødergård Gjøvik og Toten SFK 8105 gr.
> > >> >> >> > 3 Kenneth Ottosen Gjøvik og Toten SFK 5440 gr.
> > >> >> >> > 4 Jørgen Langerud Raufjøringen 5245 gr.
> > >> >> >> > 5 Ove Lauten Gjerdrum 4745 gr.
> > >> >> >> > ****
> > >> >> >> > 6 Per E. Hellerud Eidsvoll JFF 4625 gr.
> > >> >> >> > 7 Frank Hønsen Gjerdrum SFK 4525 gr.
> > >> >> >> > 8 Odd Henning Hansen Gjøvik og Toten SFK 4505 gr.
> > >> >> >> > 9 Johnny Ulsrudstuen Gjøvik og Toten SFK 4255 gr.
> > >> >> >> > 10 Anders Wold Oslo Sportsfiske 3780 gr.
> > >> >> >> > 11 Tom Pedersen Gjøvik og Toten SFK 3325 gr.
> > >> >> >> > 12 Eric Olstad Toten JFF Lodd 2 3040 gr.
> > >> >> >> > 13 Geir Lillejordet Gjøvik og Toten SFK 2735 gr.
> > >> >> >> > 14 Remi A. Olsen SFK Pimpel Sør 2540 gr.
> > >> >> >> > 15 Paal Runden Gjøvik og Toten SFK 2495 gr.
> > >> >> >> >
> > >> >> >> > "Per Erik" wrote:
> > >> >> >> >
> > >> >> >> >> Hey! First, sorry my bad English!
> > >> >> >> >> I have created a results page of a report in Access 2007.
> > >> >> >> >> I want to make a mark AFTER the first third of the report. So if
> > >> >> >> >> there
> > >> >> >> >> are
> > >> >> >> >> 33 participants, I would like it to be characterized by the
> > >> >> >> >> eleventh.
> > >> >> >> >> Is
> > >> >> >> >> this
> > >> >> >> >> possible in a report?
> > >> >> >> >> Hope someone can help me with this!
> > >> >> >>
> > >> .
> > >>
> > .
> >

From: Per Erik on 10 May 2010 06:54

Sorry the SQL was wrong. I don´t know what happened!
The SQL statement in the query:
SELECT A.VEKT, A.NAVN, A.KLUBB, A.KLASSE, (SELECT Count(*) FROM HERRER AS B
WHERE B.VEKT>A.VEKT)+1 AS Rank
FROM HERRER AS A;

"Per Erik" wrote:

> I'm using SQL RANK because I want a proper ranking.
> Like this:
> 1. Person1 - 350 gr
> * 2 Person2 - 340 gr
> * 2 Person3 - 340 gr
> 4. Person4 - 320 gr
>
> Those who have the same weight, should get the same space-digits. Like my
> example above.
>
> SQL statement in the query: SELECT A. GRAVITY, A. NAME, A. Club, A. CLASS,
> (SELECT Count (*) FROM THE LORD AS B WHERE B. GRAVITY> GRAVITY A.) +1 AS Rank
> FROM THE LORD AS A;
>
>
> "Duane Hookom" wrote:
>
> > You need to figure out a different method for the RANK. Is there a reason
> > why you can't use a running sum in the report to display the rank?
> >
> > If you can't, you may need to post something about the report's record
> > source including significant SQL views.
> > --
> > Duane Hookom
> > MS Access MVP
> >
> >
> > "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
> > news:14C96427-CED4-481A-80AC-207145231274(a)microsoft.com...
> > > Yes I have used a subquery called RANK (NR), and this will be sorted to
> > > produce the correct results.
> > > I get the error message when I put the txtCountAll to the Report Header
> > > section.
> > >
> > > "Duane Hookom" wrote:
> > >
> > >> Sounds like a issue with the record source of your report. I expect you
> > >> used
> > >> a subquery to get the NR field. That's a no-no if you intend to sort by
> > >> it.
> > >>
> > >> --
> > >> Duane Hookom
> > >> MS Access MVP
> > >>
> > >>
> > >> "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
> > >> news:5CD5C593-A53A-4608-BF46-31697B34DB11(a)microsoft.com...
> > >> > I get an error message: "Multi-level GROUP BY clause is not allowed in
> > >> > a
> > >> > subquery".
> > >> >
> > >> > I have created everything according to your commandments, and the code
> > >> > in
> > >> > the detail section is:
> > >> > Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
> > >> > Me.Mark.Visible = (Me.txtRunCount = Me.txtCountAll \ 3)
> > >> > End Sub
> > >> >
> > >> > Per Erik
> > >> >
> > >> > "Duane Hookom" wrote:
> > >> >
> > >> >> Tell us how you have your report set up including the code that is run
> > >> >> and
> > >> >> which section.
> > >> >>
> > >> >> --
> > >> >> Duane Hookom
> > >> >> MS Access MVP
> > >> >>
> > >> >>
> > >> >> "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
> > >> >> news:D22CFF97-51D1-4D79-988C-DDCDD31E0C9E(a)microsoft.com...
> > >> >> > The controls are visible in all the lines, not only in the 1/3.
> > >> >> >
> > >> >> > "Duane Hookom" wrote:
> > >> >> >
> > >> >> >> It would help if you told us wasn't working. What are your results?
> > >> >> >> If
> > >> >> >> the
> > >> >> >> controls are all visible, what do you see?
> > >> >> >>
> > >> >> >> My suggestion show have displayed the control named Mark every 1/3
> > >> >> >> of
> > >> >> >> the
> > >> >> >> record count. I think you need to change the code to:
> > >> >> >>
> > >> >> >>
> > >> >> >> Private Sub Detail_Format(Cancel As Integer, FormatCount As
> > >> >> >> Integer)
> > >> >> >> Me.Mark.Visible = (Me.txtRunCount = Me.txtCountAll \ 3
> > >> >> >> End Sub
> > >> >> >>
> > >> >> >> --
> > >> >> >> Duane Hookom
> > >> >> >> MS Access MVP
> > >> >> >>
> > >> >> >>
> > >> >> >> "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
> > >> >> >> news:89E9A348-9496-4C22-A00F-61572952126E(a)microsoft.com...
> > >> >> >> > Like this:
> > >> >> >> > NR. NAVN KLUBB VEKT
> > >> >> >> > 1 Jim Bekken Raufjøringen 9510 gr.
> > >> >> >> > 2 Thomas Ødergård Gjøvik og Toten SFK 8105 gr.
> > >> >> >> > 3 Kenneth Ottosen Gjøvik og Toten SFK 5440 gr.
> > >> >> >> > 4 Jørgen Langerud Raufjøringen 5245 gr.
> > >> >> >> > 5 Ove Lauten Gjerdrum 4745 gr.
> > >> >> >> > ****
> > >> >> >> > 6 Per E. Hellerud Eidsvoll JFF 4625 gr.
> > >> >> >> > 7 Frank Hønsen Gjerdrum SFK 4525 gr.
> > >> >> >> > 8 Odd Henning Hansen Gjøvik og Toten SFK 4505 gr.
> > >> >> >> > 9 Johnny Ulsrudstuen Gjøvik og Toten SFK 4255 gr.
> > >> >> >> > 10 Anders Wold Oslo Sportsfiske 3780 gr.
> > >> >> >> > 11 Tom Pedersen Gjøvik og Toten SFK 3325 gr.
> > >> >> >> > 12 Eric Olstad Toten JFF Lodd 2 3040 gr.
> > >> >> >> > 13 Geir Lillejordet Gjøvik og Toten SFK 2735 gr.
> > >> >> >> > 14 Remi A. Olsen SFK Pimpel Sør 2540 gr.
> > >> >> >> > 15 Paal Runden Gjøvik og Toten SFK 2495 gr.
> > >> >> >> >
> > >> >> >> > "Per Erik" wrote:
> > >> >> >> >
> > >> >> >> >> Hey! First, sorry my bad English!
> > >> >> >> >> I have created a results page of a report in Access 2007.
> > >> >> >> >> I want to make a mark AFTER the first third of the report. So if
> > >> >> >> >> there
> > >> >> >> >> are
> > >> >> >> >> 33 participants, I would like it to be characterized by the
> > >> >> >> >> eleventh.
> > >> >> >> >> Is
> > >> >> >> >> this
> > >> >> >> >> possible in a report?
> > >> >> >> >> Hope someone can help me with this!
> > >> >> >>
> > >> .
> > >>
> > .
> >

From: Per Erik on 10 May 2010 11:04

I don´t know what "happened" with my SQL under here, but here is the correct:

SELECT A.VEKT, A.NAVN, A.KLUBB, A.KLASSE, (SELECT Count(*) FROM HERRER AS B
WHERE B.VEKT>A.VEKT)+1 AS Rank
FROM HERRER AS A;

"Per Erik" wrote:

> I'm using SQL RANK because I want a proper ranking.
> Like this:
> 1. Person1 - 350 gr
> * 2 Person2 - 340 gr
> * 2 Person3 - 340 gr
> 4. Person4 - 320 gr
>
> Those who have the same weight, should get the same space-digits. Like my
> example above.
>
> SQL statement in the query: SELECT A. GRAVITY, A. NAME, A. Club, A. CLASS,
> (SELECT Count (*) FROM THE LORD AS B WHERE B. GRAVITY> GRAVITY A.) +1 AS Rank
> FROM THE LORD AS A;
>
>
> "Duane Hookom" wrote:
>
> > You need to figure out a different method for the RANK. Is there a reason
> > why you can't use a running sum in the report to display the rank?
> >
> > If you can't, you may need to post something about the report's record
> > source including significant SQL views.
> > --
> > Duane Hookom
> > MS Access MVP
> >
> >
> > "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
> > news:14C96427-CED4-481A-80AC-207145231274(a)microsoft.com...
> > > Yes I have used a subquery called RANK (NR), and this will be sorted to
> > > produce the correct results.
> > > I get the error message when I put the txtCountAll to the Report Header
> > > section.
> > >
> > > "Duane Hookom" wrote:
> > >
> > >> Sounds like a issue with the record source of your report. I expect you
> > >> used
> > >> a subquery to get the NR field. That's a no-no if you intend to sort by
> > >> it.
> > >>
> > >> --
> > >> Duane Hookom
> > >> MS Access MVP
> > >>
> > >>
> > >> "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
> > >> news:5CD5C593-A53A-4608-BF46-31697B34DB11(a)microsoft.com...
> > >> > I get an error message: "Multi-level GROUP BY clause is not allowed in
> > >> > a
> > >> > subquery".
> > >> >
> > >> > I have created everything according to your commandments, and the code
> > >> > in
> > >> > the detail section is:
> > >> > Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
> > >> > Me.Mark.Visible = (Me.txtRunCount = Me.txtCountAll \ 3)
> > >> > End Sub
> > >> >
> > >> > Per Erik
> > >> >
> > >> > "Duane Hookom" wrote:
> > >> >
> > >> >> Tell us how you have your report set up including the code that is run
> > >> >> and
> > >> >> which section.
> > >> >>
> > >> >> --
> > >> >> Duane Hookom
> > >> >> MS Access MVP
> > >> >>
> > >> >>
> > >> >> "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
> > >> >> news:D22CFF97-51D1-4D79-988C-DDCDD31E0C9E(a)microsoft.com...
> > >> >> > The controls are visible in all the lines, not only in the 1/3.
> > >> >> >
> > >> >> > "Duane Hookom" wrote:
> > >> >> >
> > >> >> >> It would help if you told us wasn't working. What are your results?
> > >> >> >> If
> > >> >> >> the
> > >> >> >> controls are all visible, what do you see?
> > >> >> >>
> > >> >> >> My suggestion show have displayed the control named Mark every 1/3
> > >> >> >> of
> > >> >> >> the
> > >> >> >> record count. I think you need to change the code to:
> > >> >> >>
> > >> >> >>
> > >> >> >> Private Sub Detail_Format(Cancel As Integer, FormatCount As
> > >> >> >> Integer)
> > >> >> >> Me.Mark.Visible = (Me.txtRunCount = Me.txtCountAll \ 3
> > >> >> >> End Sub
> > >> >> >>
> > >> >> >> --
> > >> >> >> Duane Hookom
> > >> >> >> MS Access MVP
> > >> >> >>
> > >> >> >>
> > >> >> >> "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
> > >> >> >> news:89E9A348-9496-4C22-A00F-61572952126E(a)microsoft.com...
> > >> >> >> > Like this:
> > >> >> >> > NR. NAVN KLUBB VEKT
> > >> >> >> > 1 Jim Bekken Raufjøringen 9510 gr.
> > >> >> >> > 2 Thomas Ødergård Gjøvik og Toten SFK 8105 gr.
> > >> >> >> > 3 Kenneth Ottosen Gjøvik og Toten SFK 5440 gr.
> > >> >> >> > 4 Jørgen Langerud Raufjøringen 5245 gr.
> > >> >> >> > 5 Ove Lauten Gjerdrum 4745 gr.
> > >> >> >> > ****
> > >> >> >> > 6 Per E. Hellerud Eidsvoll JFF 4625 gr.
> > >> >> >> > 7 Frank Hønsen Gjerdrum SFK 4525 gr.
> > >> >> >> > 8 Odd Henning Hansen Gjøvik og Toten SFK 4505 gr.
> > >> >> >> > 9 Johnny Ulsrudstuen Gjøvik og Toten SFK 4255 gr.
> > >> >> >> > 10 Anders Wold Oslo Sportsfiske 3780 gr.
> > >> >> >> > 11 Tom Pedersen Gjøvik og Toten SFK 3325 gr.
> > >> >> >> > 12 Eric Olstad Toten JFF Lodd 2 3040 gr.
> > >> >> >> > 13 Geir Lillejordet Gjøvik og Toten SFK 2735 gr.
> > >> >> >> > 14 Remi A. Olsen SFK Pimpel Sør 2540 gr.
> > >> >> >> > 15 Paal Runden Gjøvik og Toten SFK 2495 gr.
> > >> >> >> >
> > >> >> >> > "Per Erik" wrote:
> > >> >> >> >
> > >> >> >> >> Hey! First, sorry my bad English!
> > >> >> >> >> I have created a results page of a report in Access 2007.
> > >> >> >> >> I want to make a mark AFTER the first third of the report. So if
> > >> >> >> >> there
> > >> >> >> >> are
> > >> >> >> >> 33 participants, I would like it to be characterized by the
> > >> >> >> >> eleventh.
> > >> >> >> >> Is
> > >> >> >> >> this
> > >> >> >> >> possible in a report?
> > >> >> >> >> Hope someone can help me with this!
> > >> >> >>
> > >> .
> > >>
> > .
> >

From: Duane Hookom on 10 May 2010 23:42

I believe you can do this without the subquery but using a totals query and
a self join. MVP John Spencer has posted this type of ranking query in the
past. You will get a more efficient and functional result as needed in the
report record source.

--
Duane Hookom
MS Access MVP

"Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
news:6F079E4A-5F2A-4E82-8767-99910E9BD9FD(a)microsoft.com...
> I don´t know what "happened" with my SQL under here, but here is the
> correct:
>
> SELECT A.VEKT, A.NAVN, A.KLUBB, A.KLASSE, (SELECT Count(*) FROM HERRER AS
> B
> WHERE B.VEKT>A.VEKT)+1 AS Rank
> FROM HERRER AS A;
>
>
> "Per Erik" wrote:
>
>> I'm using SQL RANK because I want a proper ranking.
>> Like this:
>> 1. Person1 - 350 gr
>> * 2 Person2 - 340 gr
>> * 2 Person3 - 340 gr
>> 4. Person4 - 320 gr
>>
>> Those who have the same weight, should get the same space-digits. Like my
>> example above.
>>
>> SQL statement in the query: SELECT A. GRAVITY, A. NAME, A. Club, A.
>> CLASS,
>> (SELECT Count (*) FROM THE LORD AS B WHERE B. GRAVITY> GRAVITY A.) +1 AS
>> Rank
>> FROM THE LORD AS A;
>>
>>
>> "Duane Hookom" wrote:
>>
>> > You need to figure out a different method for the RANK. Is there a
>> > reason
>> > why you can't use a running sum in the report to display the rank?
>> >
>> > If you can't, you may need to post something about the report's record
>> > source including significant SQL views.
>> > --
>> > Duane Hookom
>> > MS Access MVP
>> >
>> >
>> > "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
>> > news:14C96427-CED4-481A-80AC-207145231274(a)microsoft.com...
>> > > Yes I have used a subquery called RANK (NR), and this will be sorted
>> > > to
>> > > produce the correct results.
>> > > I get the error message when I put the txtCountAll to the Report
>> > > Header
>> > > section.
>> > >
>> > > "Duane Hookom" wrote:
>> > >
>> > >> Sounds like a issue with the record source of your report. I expect
>> > >> you
>> > >> used
>> > >> a subquery to get the NR field. That's a no-no if you intend to sort
>> > >> by
>> > >> it.
>> > >>
>> > >> --
>> > >> Duane Hookom
>> > >> MS Access MVP
>> > >>
>> > >>
>> > >> "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
>> > >> news:5CD5C593-A53A-4608-BF46-31697B34DB11(a)microsoft.com...
>> > >> > I get an error message: "Multi-level GROUP BY clause is not
>> > >> > allowed in
>> > >> > a
>> > >> > subquery".
>> > >> >
>> > >> > I have created everything according to your commandments, and the
>> > >> > code
>> > >> > in
>> > >> > the detail section is:
>> > >> > Private Sub Detail_Format(Cancel As Integer, FormatCount As
>> > >> > Integer)
>> > >> > Me.Mark.Visible = (Me.txtRunCount = Me.txtCountAll \ 3)
>> > >> > End Sub
>> > >> >
>> > >> > Per Erik
>> > >> >
>> > >> > "Duane Hookom" wrote:
>> > >> >
>> > >> >> Tell us how you have your report set up including the code that
>> > >> >> is run
>> > >> >> and
>> > >> >> which section.
>> > >> >>
>> > >> >> --
>> > >> >> Duane Hookom
>> > >> >> MS Access MVP
>> > >> >>
>> > >> >>
>> > >> >> "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in message
>> > >> >> news:D22CFF97-51D1-4D79-988C-DDCDD31E0C9E(a)microsoft.com...
>> > >> >> > The controls are visible in all the lines, not only in the 1/3.
>> > >> >> >
>> > >> >> > "Duane Hookom" wrote:
>> > >> >> >
>> > >> >> >> It would help if you told us wasn't working. What are your
>> > >> >> >> results?
>> > >> >> >> If
>> > >> >> >> the
>> > >> >> >> controls are all visible, what do you see?
>> > >> >> >>
>> > >> >> >> My suggestion show have displayed the control named Mark every
>> > >> >> >> 1/3
>> > >> >> >> of
>> > >> >> >> the
>> > >> >> >> record count. I think you need to change the code to:
>> > >> >> >>
>> > >> >> >>
>> > >> >> >> Private Sub Detail_Format(Cancel As Integer, FormatCount As
>> > >> >> >> Integer)
>> > >> >> >> Me.Mark.Visible = (Me.txtRunCount = Me.txtCountAll \ 3
>> > >> >> >> End Sub
>> > >> >> >>
>> > >> >> >> --
>> > >> >> >> Duane Hookom
>> > >> >> >> MS Access MVP
>> > >> >> >>
>> > >> >> >>
>> > >> >> >> "Per Erik" <PerErik(a)discussions.microsoft.com> wrote in
>> > >> >> >> message
>> > >> >> >> news:89E9A348-9496-4C22-A00F-61572952126E(a)microsoft.com...
>> > >> >> >> > Like this:
>> > >> >> >> > NR. NAVN KLUBB VEKT
>> > >> >> >> > 1 Jim Bekken Raufjøringen 9510 gr.
>> > >> >> >> > 2 Thomas Ødergård Gjøvik og Toten SFK 8105 gr.
>> > >> >> >> > 3 Kenneth Ottosen Gjøvik og Toten SFK 5440 gr.
>> > >> >> >> > 4 Jørgen Langerud Raufjøringen 5245 gr.
>> > >> >> >> > 5 Ove Lauten Gjerdrum 4745 gr.
>> > >> >> >> > ****
>> > >> >> >> > 6 Per E. Hellerud Eidsvoll JFF 4625 gr.
>> > >> >> >> > 7 Frank Hønsen Gjerdrum SFK 4525 gr.
>> > >> >> >> > 8 Odd Henning Hansen Gjøvik og Toten SFK 4505 gr.
>> > >> >> >> > 9 Johnny Ulsrudstuen Gjøvik og Toten SFK 4255 gr.
>> > >> >> >> > 10 Anders Wold Oslo Sportsfiske 3780 gr.
>> > >> >> >> > 11 Tom Pedersen Gjøvik og Toten SFK 3325 gr.
>> > >> >> >> > 12 Eric Olstad Toten JFF Lodd 2 3040 gr.
>> > >> >> >> > 13 Geir Lillejordet Gjøvik og Toten SFK 2735 gr.
>> > >> >> >> > 14 Remi A. Olsen SFK Pimpel Sør 2540 gr.
>> > >> >> >> > 15 Paal Runden Gjøvik og Toten SFK 2495 gr.
>> > >> >> >> >
>> > >> >> >> > "Per Erik" wrote:
>> > >> >> >> >
>> > >> >> >> >> Hey! First, sorry my bad English!
>> > >> >> >> >> I have created a results page of a report in Access 2007.
>> > >> >> >> >> I want to make a mark AFTER the first third of the report.
>> > >> >> >> >> So if
>> > >> >> >> >> there
>> > >> >> >> >> are
>> > >> >> >> >> 33 participants, I would like it to be characterized by the
>> > >> >> >> >> eleventh.
>> > >> >> >> >> Is
>> > >> >> >> >> this
>> > >> >> >> >> possible in a report?
>> > >> >> >> >> Hope someone can help me with this!
>> > >> >> >>
>> > >> .
>> > >>
>> > .
>> >

First | Prev |
Pages: 1 2 3 4
Prev: Detect when detail section pushes to 2nd page
Next: Report Grand Total Miscalculating Overtime