A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

To MVP John Spencer - Regarding Report



 
 
Thread Tools Display Modes
  #1  
Old May 11th, 2010, 12:04 PM posted to microsoft.public.access.reports
Per Erik
external usenet poster
 
Posts: 15
Default To MVP John Spencer - Regarding Report

Hey!

Sorry my bad English!

My Name Is Per Erik Løkken, and comes from Norway. I have been in contact
with MVP Duane Hookom regarding an issue with a report in Access 2007.

She tells me that you've had a post regarding the ranking query:

“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.”

My report is made with a subquery:
SELECT A.VEKT, A.NAVN, A.KLUBB, A.KLASSE, (SELECT Count(*) FROM HERRER AS B
WHERE B.VEKTA.VEKT)+1 AS Rank FROM HERRER AS A;

I'm using SQL RANK because I want a proper ranking.
Like this:
Nr. Name Weight
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.
As you can see the No. 2 and 3 of the same weight (340 gr), and they will
then be ranked No. 2 together.

The next will then rank No. 4.

And so I want to make a “mark” for the first third of the report, such as
described below. There is marked with ****.

This is because it is 1 / 3 to be rewarded.
I've tried to do this with conditional formatting, but it did not work.


1 Jim Bekken Raufjøringen 9510 gr.
2 Thomas Ødergård Gjøvik og Toten SFK 8105 gr.
2 Kenneth Ottosen Gjøvik og Toten SFK 8105 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.
9 Anders Wold Oslo Sportsfiske 4255 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.

Can you help me with this?

Regards,

Per Erik Løkken -
http://www.sports-reference.com/olym...-lokken-1.html

  #2  
Old May 11th, 2010, 02:45 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default To MVP John Spencer - Regarding Report

The ranking query would look like this

SELECT A.VEKT, A.NAVN, A.KLUBB, A.KLASSE,
1 + Count(B.Vekt) as Rank
FROM HERRER as A LEFT JOIN HERRER As B
ON A.VEKT B.VEKT
GROUP BY A.VEKT, A.NAVN, A.KLUBB, A.KLASSE

The next step would be to determine how many total records are returned and
use that along with ranking to turn a control containing the ****** string
visible.

You could add an INVISIBLE control (txtTotalCount) to the report's header with
its control value set to
=Count(*)

Then in the detail section you could use code to calculate when you have
reached one third and make the control that holds the ***** string visible
Something like the following might work - although
Me.txtMark.Visible = Round(Me.txtTotalCount/3) = Me.Rank

By the way Duane is a HE not a she.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Per Erik wrote:
Hey!

Sorry my bad English!

My Name Is Per Erik Løkken, and comes from Norway. I have been in contact
with MVP Duane Hookom regarding an issue with a report in Access 2007.

She tells me that you've had a post regarding the ranking query:

“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.”

My report is made with a subquery:
SELECT A.VEKT, A.NAVN, A.KLUBB, A.KLASSE, (SELECT Count(*) FROM HERRER AS B
WHERE B.VEKTA.VEKT)+1 AS Rank FROM HERRER AS A;

I'm using SQL RANK because I want a proper ranking.
Like this:
Nr. Name Weight
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.
As you can see the No. 2 and 3 of the same weight (340 gr), and they will
then be ranked No. 2 together.

The next will then rank No. 4.

And so I want to make a “mark” for the first third of the report, such as
described below. There is marked with ****.

This is because it is 1 / 3 to be rewarded.
I've tried to do this with conditional formatting, but it did not work.


1 Jim Bekken Raufjøringen 9510 gr.
2 Thomas Ødergård Gjøvik og Toten SFK 8105 gr.
2 Kenneth Ottosen Gjøvik og Toten SFK 8105 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.
9 Anders Wold Oslo Sportsfiske 4255 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.

Can you help me with this?

Regards,

Per Erik Løkken -
http://www.sports-reference.com/olym...-lokken-1.html

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:12 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.