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.VEKT>A.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/olympics/athletes/lo/per-erik-lokken-1.html

0
Utf
5/11/2010 11:04:01 AM
access.reports 4434 articles. 0 followers. Follow

1 Replies
898 Views

Similar Articles

[PageSpeed] 30

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.VEKT>A.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/olympics/athletes/lo/per-erik-lokken-1.html
> 
0
John
5/11/2010 1:45:25 PM
Reply:

Similar Artilces:

Historic currency exchange rates for Schedule C reporting
I'm trying to get a Schedule C report out of Money but cannot correct the exchange rate it is using. I have an account with entries in GBP for 2008, but that are converted to USD for my Schedule C report. However, pretty sure the exchange used is supposed to be either some annual average as mandated by the government or the daily rate at the time. Money is just using today's rate, which is obviously causing inaccurate and varying results each time I run the report. Is there any way to tell money to use the rate on the date of the transaction, or give it the rate to use for a r...

FRx 6.5 Reports with FRx 6.7 Report Server
Can FRx 6.5 reports work with FRx 6.7 Report Server without being upgraded to FRx 6.7? Any Help would be appreciated. ------=_NextPart_0001_2CE4EA95 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Andrew, Thanks for the question about FRx 6.5 and 6.7. I would recommend having your entire FRx environment operating on the same version. When using different versions of FRx in one environment it can create problems. Let us know if you have any questions. David Treleaven Microsoft Online Partner Support When responding to posts, please "Reply to Group" via you...

Please help very urgent to give ageing report
Please give me an sql to generate the report from statement table, I have three column in my table 1. Month 2. year 3. Amount I want to show 1. if the month is current month it should be shown as ' Current' 2. If the previous month it should be shown as '30' 3 if before previous month it should be '60' 4 if before '60' it should be '90' 5. otherwise it should be '120' Please help Have a look at the Partition() function. A good helpfile for it is at: http://accessmvp.com/Arvin/PartitionFunctionHelp.txt --...

Can't see reports in MSCRM
Hi All, I have a problem is that i can't see the reports in Microsoft CRM. I can browse to report server and can reports from there. I have checked from registry and the reportserverurl is pointing to the correct Reportserver url. I don't get any err when i click on reports under workplace in CRM, its just a blank screen without any report? I have checked from report manager that the the Org_Name folder has the Publisher for Microsoft CRM role assigned to Network service account and Browse for CRM Reports rols assigned to ReportingGroup group. When i tried to publish reports u...

Combine multiple records in one field in Access report
I'm having trouble designing a report. I have a report that sorts the data according to "Change Order Number", however, a change order can have multiple "Trend Numbers", "RFI Numbers", and other records associated with it and I want all of the "Trend Numbers" (and these other records) to fit on one line. For example, "Change Order Number 1" has 2 "Trend Numbers" associated with it, and the report currently shows this information in two rows with all of the other information for Change Order 1 repeating. I've done hide...

Client Report
How do I get the report not to display what is in column 1 (ID) and just show column 2 (Parent Name) krc547 wrote: >How do I get the report not to display what is in column 1 (ID) and just show >column 2 (Parent Name) Either delete the id text box or make it invisible. If you used a wixard to create the report, then open the report in design view to change it. -- Marsh MVP [MS Access] ...

Reason Code Reports
How can I run a report regarding reason codes? I have made it so that you need to give a reason before making a return. If someone makes a return, I want to know why...how can I run this sort of report? -- Thank You,; Vince Just run a sales detail report and unhide them. You can filter by them also. Werd. mt "Vince" <Vince@discussions.microsoft.com> wrote in message news:7FE67148-C812-4B99-98B0-B0D03E7A5584@microsoft.com... > How can I run a report regarding reason codes? I have made it so that you > need to give a reason before making a return. If someone ma...

Account Payable Detail report
Hola Family I will like to know if someone can help me, I need to know the path to run an Account Payable Detail report. This file should contain the lowest level of detail. Each record should include the General Ledger account number to which the transaction is being charged. I am using Dynamics GP 10 with SQL 2005. Thank you in advanced. -- LOS Hola Carlos, The GL Distribution Detail in Purchaing Posting Journal can be access via Reports>>Purchasing>>Posting Journals (GL GL Distribution Detail). When you create/modify a report options, you can use the ...

Crystal Report Viewer Error
When trying to run reports through the CRM I get the following error: CrystalReportViewer: Information is needed before this report can be processed. This shows up in the popup window where the results of the report would normally show before you would press the print button. -- Chris Meredith Chris, This error is caused by 1)Installing Microsoft CRM on a computer that has an underscore in its name. OR 2)Security issues related to domain and Microsoft CRM setup. Microsoft has explained the causes and how to resolve this error in the following article. http://www.microsoft.com/download...

Report that display percentage below certain level
I got a spreadsheet contain inventory number from remote site. I need to run a report to display when inventory below a certain percentage. I try to use privot table to display the infomration. But it doesn't display everything below 40%. Is there anyway you can enter an percentage and display all remote site that below certain percentage (Eample: like 35%) on a report? Thanks. TSS, A pivot table groups like items, and makes a calculation on each group -- not what you need. You need a filter (Excel's way of saying "query" in database parlance). If your table...

Automatic email of reports
Hi all Can reports be generated and sent automatically to given mail ids on a regular basis ? Pls advice via Crystal Reports, not an option with current out of box version v1.2. You can try externally controlling it via SDK approach. Good luck. Frank Lee Workopia, Inc. http://www.workopia.com/Links.htm San Francisco, CA "Parasuraman" wrote: > Hi all > > Can reports be generated and sent automatically to given > mail ids on a regular basis ? > > Pls advice > > > ...

CRM report Error - Account Overview
Hi there, Is it possible if anyone could help me with the following problem.The Account Overview Report does not appear for one of the clients. When the report is opened the following error message appears: Error: Subreport could not be shown. Any ideas? Thank you in advance Lebo ...

Show harvest report error when installing app on Terminal Server.
I have an application that I want to install on a terminal server to publish, but I am having difficulties. The scenario follows as below. The application is dependant on an Oracle install. What I am doing is installing the Oracle client first then I load the program. Then to print Crystal reports, I just map a network drive to the location where these premade Crystal reports reside. This works just fine and dandy on all my workstations. So, I tried to load this on a Citrix Server to publish this out, and all of the sudden when I go to run a report, I get an error that says ...

Customize link not working in Reports
Money 2002: Version 10.0 When I open a report (any report) and then choose the Customize... link on the left side, I do not get the Customize Dialog box that I used to see. Money is basically unusable until I hit the ESC (Escape) key. Any ideas? If you are using Money 2005, go into Tools/Reports and see whether you are using Essential or Advanced Reports. My guess is that you need to switch from Essential to Advanced. This is new in Money 2005. "LongTime User" wrote: > Money 2002: Version 10.0 > > When I open a report (any report) and then choose the Customize....

Report Server Error 09-19-07
I have completed my CRM install but each time I attempt to access the Report Manager I get the error message: The Scriptbibliotek '/aspnet_client/system_web/1_1_4322/WebUIValidation.js' cannot be found. Manually move the data or use the command 'aspnet_regiis -c' in order to install new. Sorry if the error is not completely correct but I´m translating from German. I have run the command 'aspnet_regiis -c' and received the message that the version started and then finished but every time I go back to Report Manager the same error pops up. Need a little...

crystal reports 9.2.2 02-03-04
I have been working on customizing reports. After I upgraded crm to 1.2 I can no longer access my reports. I tried to install the crystal repors enhancement for 1.2. I am getting an error that I am using crystal reports 9.2.0 and I need to upgrade to 9.2.2? I had crystal reports 9.0 professional edition installed on my computer also. I removed that and then tried to install the crm 1.2 crystal reports enhancement and I am getting the same errors. Please help. Thank you It seems that CRM requires a very specific version of crystal reports ie 9.2.2 Microsoft have told me that you ...

No Reports in CRM 3.0 Install
Hi everyone, I have been working on installing CRM 3.0 (fresh install) on a Small Business Server and I have been having trouble getting the reporting services to work correctly. I have been able to get the reports section to pull up now but no reports are listed in the grid. All the other functions appear to be working and I can even start to add reports but it puzzles me. Doesn't Microsoft provide sample reports to begin with? Has anyone else had this issue and if so, how do I get the reports to show up? Surely they gave us standard reports with the program?!?!?! Thanks! on ...

Exporting Reports
I am trying to export or publish a report into word. The report has about 80 stores within it. Each store starts at a new page. Some stores have many pages to them. I want to export the report out to word, but I want each store to have it's onw location. For example: Laurel will go to a new document and Annapolis will go to it's own word document. Right now I publish the report and all of the stores are in one document and I have to copy and paste them into there own document. Is this possible? Lisa S. It is possible to choose the data that goes into a particular report, and to...

Memory for rendering a report page
Does Access limit the amount of memory needed to render a report page? If so, is it possible to increase the default? Type a question (i.e., Access specifications) on help filed of access window. -- HTP Adnan If I have helped and/or answered your question, please let me know by replying or clicking question answered (below positioned ‘Yes’ button). "anthony" wrote: > Does Access limit the amount of memory needed to render a report page? > If so, is it possible to increase the default? > anthony wrote: >Does Access limit the amount of memory needed to render a r...

selecting the 2nd column of a combo box in a report?
I have a combo box that uses data from a table with the fields (setupid, physician setup, md_PIN). the bound column = 1. The physicians sign on using their name (signing_md) and PIN (signing_md_pin). thanks to advice from this website I have...If signing_md_pin.value = dlookup("md_pin". "[setup table]","[setupid] =" [signing_md] then ... in the event procedure. When I try to get the physician name on the report (signing_md) it returns the physician number (setupid) instead of the name. How do I get the 2nd column of the combo box to the report?Thank you....

Help me send an email with just one record from a report
PLZ Somebody help me with email just one record... Here is what I currently have DoCmd.SendObject acSendReport, "RptArSheet", "Snap", "", "", "", "", "", False, "" It send the whole report when I just want the current record of the report.... but i dont know how to do that plz help Thanks Mike -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200706/1 You need to filter the report in some way so that the report only contains one record. You can either use ...

Service packs for vc7.1 2003? And how to report "Compiler Fatal Error"?
Have there been any service packs to .NET 2003 vc7.1? I haven't come across any, but perhaps I don't know where to look. There was a SP every year or so for vc6 until 2002 or so, so I would expect there to be one or more service packs for vc7.1. I've tried many times to use the IDE's Help + Check For Updates, but get an error message when I try this. I got a "Compiler Fatal Error" with code using vc7.1. The code has a syntax error which is detected and reported by vc6 and vc8. Is there a place to report this vc7.1 problem, or is this a "who cares"? Does Mi...

report date range
I have a report that will display data from an inventory transaction history form. There I would enter in the date and several other criteria. After doing so I filter the form to my selection and then print my report. I wanted the date range that is on the form to print on the report. I hvae done this in several other reports and never had a problem until now. I'm getting #NAME. The form is still open upon viewing the report. On the report I have a text box with the logic of: ="Report Date Between: " & [Forms]![frmInvTransactionHistory]![BegtDate] & " and &...

VBA to default printer in Crystal Report
is it possible to set default the printer for crystal report to be the same as the printer setup in GP (Files>Print Setup)? eg: is GP is using Printer1, then when calling a crystal report using VBA, the printer will be set to Printer1. If GP is using Printer2, then crystal report will be defaulted to Printer2. thks in advance. Unfortunately that isn't going to work for a couple reason: 1. The biggest reason is there isn't any way to determine what the printer is in Dynamics. Not even with Dexterity. It just wasn't something that was exposed. So I can't think of ...

Creating Reports 05-08-07
I have been investigating the different methods of creating a new report in crm and have found a few different methods. What is the easier and time efficient method for creating a new custom report? On May 8, 3:26 pm, Nate <N...@discussions.microsoft.com> wrote: > I have been investigating the different methods of creating a new report in > crm and have found a few different methods. What is the easier and time > efficient method for creating a new custom report? Pre-filtering the reports per the SDK is probably going to be your best bet. You can upload them into CRM and ...