Using Access '02, Windows XP Pro SP2
I have a Report that Contains data that is sorted by name and have been asked to add a field that would output a rank based on a numerical (currency) field.
Example Report structure as it is now (dashes added for spacing purposes)
Name-----------Score
----------------------------
ABC------------------10
BCD------------------25
CDE--------------------5
Example Report Structure needed
Rank------Name--------------Score
------------------------------------------
---2---------ABC-------------------10
---1---------BCD-------------------25
---3---------CDE-------------------5
I just started my adventure into Access a few weeks ago and have no SQL knowledge. I have attempted to make a query to pull data, resort, and number, but I have been unable to get my head around some of the comcepts. I do not know if it is possible to add a field into a query that would auto count a rank (based on the query being sorted by the Score field)
39  7048 
Are you going to sort by rank on the report?
No I am not. The report will still continue to be sorted by name. But I need the records ranked as if they were sorted by score.
Is there any way to do this at all with Access functions, or do I have to lears SQL soon?
- SELECT Temp.Score,Temp.Name, ((Select Count(*) from TableName Where [Score] > [Temp].[Test];)+1) AS Rank
-
FROM TableName AS Temp
-
ORDER BY Temp.Name;
Thank you for the code, I am attempting to enter it now but have one question. You refer to a [Test] field at the end of the first line. I am unsure what this refers to, when I enter it in I am prompted to enter a value for [Test].
Also I apologize for the double post. I was a little anxious this morning, and thought I may be able to rephrase what I needed better.
Sorry. I forgot to rename it. I was testing the SQL using different fields names. [Test] should be [Score] and you'll have to put your Table Name in place of TableName
I also put my table name in place of the [Temp] tag. Is this correct? I get all 1's when i use [Score] in place of [Test]
No, leave the [Temp] tags alone, they're used as an alias. Only change [Test] to [Score] and TableName to your table's name.
This got the ranking field working almost exactly as I am looking for! The table contains data that many months worth of data. I use the line
WHERE (((Month([Temp].[Date]))=Month(Date()-28)))
to limit the query to the most recent month (working with data that is a month old always).
The line of code creates a ranking for the entire table and then the results are limited to the most recent month, leaving me with weird rankings (6,8,12,17,21, etc.) but I think i just need to base this query off a query that limita the data set prior to the ranking.
-Oth
I have everything modified so that the Query has the desired output. When I try to display the [Rank] field in my report I get an error message.
The help file lists the error as : Error 3612
Multi-level GROUP BY clause is not allowed in a subquery. (Error 3612)
An SQL subquery cannot list more than one field in a GROUP BY clause.
Please post your SQL.
SELECT Temp.Date, Temp.Dealership, Temp.NewVehInv, Temp.[Total New Cost], Temp.[Total Holdback], Temp.WhsFinLiab, Temp.[Cash in Bank], Temp.ConInTrans, Temp.UsedVehInv, Temp.[Total Used Cost], Temp.Liability, Temp.[Retail New], Temp.[Retail Used], Temp.Wholesale, Temp.S7Score, Temp.Bonus, Temp.SP, Temp.[Entry Order], ((Select Count(*) from [Sales Report MTD] Where [S7Score] > [Temp].[S7Score];)+1) AS Rank
FROM [Sales Report MTD] AS Temp
ORDER BY Temp.Dealership;
From our previous posts
Name = Dealership
Score = S7Score
Try this, make a query that basically pulls everything from that query you just posted and base the report off that. Now, I'm not talking about a copy of the query, rather I'm talking about a query that SELECT * FROM query.
I think the problem is that the query is pulling data from both a table and a query.
Cannot edit my last reply for some reason.....
The error only shows up when I reference the [Rank] field in my report (in the details). I do not know if this will help you find the problem.
-Oth
Cannot edit my last reply for some reason.....
The error only shows up when I reference the [Rank] field in my report (in the details). I do not know if this will help you find the problem.
-Oth
There's a 5 minute cutoff for edits.
Try out what I said in post #14 and let me know if that works.
If I make a query to pull all data from the previous query I get all 1's in my report.
The rank populates correctly if you just view the datasheet.
-Oth
Okay, try making the query a Make Table query and base the report off it.
That worked perfectly. It kind of sucks to have to take the extra step to generate the table, but I am much further along than I was before I found you guys. Many many thanks.
-Oth
I just tested it out and I see no reason why it shouldn't work. I used a more simplistic table with only 2 fields but otherwise the concept is the same.
I had a table with 2 fields.
I made a query based on the table that also adds a rank.
I made a report based on that query.
It all works, is this the same thing you did?
On a side note that may or may not have any bearing on the issue at hand. Don't name fields "Date" or "Name" or any other reserved keywords/properties/functions used by Access, it will confuse the two in certain situations and may confuse anyone else looking at the database.
That worked perfectly. It kind of sucks to have to take the extra step to generate the table, but I am much further along than I was before I found you guys. Many many thanks.
-Oth
Not a problem, good luck.
Yes, the extra step is a hassle but if you're going to make it user friendly and have a switchboard, you can basically automate everything through code.
All part of the learning process I guess. This is the first database that I have designed myself. Thanks for the tips on the Name and Date field names. I have a lot to learn about naming structures.
-Oth
NeoPa 32,556
Expert Mod 16PB
All part of the learning process I guess. This is the first database that I have designed myself. Thanks for the tips on the Name and Date field names. I have a lot to learn about naming structures.
-Oth
Try to avoid ever using spaces or punctuation characters in names too.
On the ranking issue, other than the fact that this obviously works, I would have sworn that a subquery couldn't reference a recordset name (Temp in this case) defined outside of the subquery itself. I tried it myself and it works perfectly - nice one.
Thank you Rabbit for this lesson :)
I found it online somewhere, it was for ascending so had to change it a bit for descending.
NeoPa 32,556
Expert Mod 16PB
I've always found a reference to a dataset (FROM clause) in the outer query cannot be used within the subquery. I suspect that this was due to my generally placing my subqueries within the FROM clause of the outer query though. It's very interesting that subqueries in the SELECT and WHERE clauses can run through (with different data) every time they are used (I mean for each record of the outer query).
If I were to change the field names in my tables now, after making many queries and reports, would that potentially mess things up?
I am looking to remove the spaces from my field names and rename the [Date] field to something more appropriate.
-Oth
NeoPa 32,556
Expert Mod 16PB
If I were to change the field names in my tables now, after making many queries and reports, would that potentially mess things up?
Yes it could.
I would certainly make a mental note to create new ones in future which match those criteria.
As for amending existing names, that's up to you. For me it would depend on how much of a problem would be caused by the knock-on effect.
Is there a way to disable or auto approve the prompts generated by running a make table query? I have been looking through the posts for something, but I do not know what exacty to search for.
-Oth
NeoPa 32,556
Expert Mod 16PB - Call DoCmd.SetWarnings(False)
-
{Your action query(s) here}
-
Call DoCmd.SetWarnings(True)
Where do I put that code? I only need it when runnig one specific query.
-Oth
NeoPa 32,556
Expert Mod 16PB
Do you currently have code that executes your action query(s)?
Or do you run them manually?
I was running them manually until earlier today. Today I made a form that had buttons that initiate the reports.
The query in question is ran when a specific report is opened. (On Open event)
-Oth
NeoPa 32,556
Expert Mod 16PB
If you post the code for your OnOpen event I'll show you where to put the extra lines.
Don't forget to use [code] tags.
NeoPa 32,556
Expert Mod 16PB
Actually, post the code for the button that opens the report as well as the OnOpen event procedure of the report and we'll have a look at what's going on exactly.
Are you talking VBA code? I am using the On Open field in the properties box when viewing the report in design mode (selecting report from the drop down box). As far as I can see there is not VBA code for the report.
I think this is what you want for the button : - Private Sub SalesNetMTDReport_Click()
-
On Error GoTo Err_SalesNetMTDReport_Click
-
-
Dim stDocName As String
-
-
stDocName = "Sales Net MTD"
-
DoCmd.OpenReport stDocName, acPreview
-
-
Exit_SalesNetMTDReport_Click:
-
Exit Sub
-
-
Err_SalesNetMTDReport_Click:
-
MsgBox Err.Description
-
Resume Exit_SalesNetMTDReport_Click
-
-
End Sub
I put the 2 lines of code in before and after the report was called and it ran without posting the warnings. I think I got it.
-Oth
NeoPa 32,556
Expert Mod 16PB
Thanks for the VBA routine posted. That is pretty standard so no problems there.
I don't really understand what you're saying about the OnOpen event procedure of the report so let's try this instead : - From the main database window select Reports.
- Select the report called [Sales Net MTD].
- Open it for Design.
- Make sure the Properties pane is open (Alt-Enter).
- Go down to the 'On Open' property.
- Select [Event Procedure].
- Click on the elipsis button (...).
- When the VBA code window opens just Copy (Ctrl-C) and Paste (Ctrl-V) the whole lot into a post here.
NeoPa 32,556
Expert Mod 16PB
I put the 2 lines of code in before and after the report was called and it ran without posting the warnings. I think I got it.
-Oth
That may work sometimes but is not the correct place.
Post me the other code and I'll show you the proper place :)
OH!! I wrote a tiny little macro that called the query and then called that macro in the 'On Open'. If i view the code for the report there is none. I assume it is because I am calling a macro and not a query directly.
-Oth
NeoPa 32,556
Expert Mod 16PB
In that case you have two options : - Leave the code as you already have it.
- Explore in the macro options for the command, for a setting which disables those messages
I would recommend using just VBA code in a database. Mixing the two means that you need to understand both. There's nothing I'm aware of that can be done in a macro but not in code.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: David Shadovitz |
last post by:
I need to add a column to a table. This table holds several thousand
records, has several indexes, and serves as the parent table to a few
child tables.
When I've done this in the past, it has...
|
by: Chris Cobb |
last post by:
I have a table that currently contains 718000 rows. I wish to add a
column to the table. Adding this column anywhere other than the end of
the table requires exporting data, a drop and recreate,...
|
by: Raj |
last post by:
Hi,
I am trying to add some more information to the table which already
has a lot a data (like 2-3000 records). The new information may be
adding 2-3 new columns worth. Now my questions are:...
|
by: Nunya Biznas |
last post by:
I have a two column report that needs to have the word "continued"
appear at the top of the 2nd column if the detail records are part of
the group from the previous column.
I have tried using a...
|
by: dSchwartz |
last post by:
I need help adding a column to a dataset, but its a little bit more
complicated then just that. Here's the situation: I have many xml
files in one directory, each which represent a newsletter. I...
|
by: bennett |
last post by:
I have a table with about 100,000 records whose description is:
+-----------------------+----------------------+------+-----+---------+----------------+
| Field | Type ...
|
by: Markw |
last post by:
Hi folks, I probably used the term 'report' incorrectly. Sorry for that in advance.
The below code produces a 2 column web page that shows in the first column a picture and the 2nd column shows...
|
by: TSGB |
last post by:
Hi all,
I need to make a report and to save space I need to be able to display the title(label) in a column report just once per page. Ex.
Result from record 1 Result from...
|
by: rfdjr1 |
last post by:
I'm using MS Access 2000. I have a small database to keep track of my lighthouse
collection. Right now it's only two columns. I'd like to add a third. I'd like
the new column to be the third...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
