Define A Rank In Query
Dec 27, 2007
Dear all
i have a trouble to define a rank in query, i have a table named SALES
like below
region PRoduct qty
jabar A 10
Jabar B 20
Jabar C 30
Jateng A 50
Jateng B 40
Jateng C 35
i want to give a result in my query like below
region PRoduct qty Rank
jabar C 30 1
Jabar B 20 2
Jabar A 10 3
Jateng A 50 1
Jateng B 40 2
Jateng C 35 3
anyone can help me???thanks
regards
martell
View Replies
ADVERTISEMENT
Aug 11, 2005
Hi,
I've got a table with football players, each player has a name,
id and a value. The value gets updated everytime the player
has played a match.
I don't have a rank field, and lets assume it's not possible to
add it.
I'd like to get, not the "top" players but the players from, say
rank 10 to rank 20. What I do now, is I get all the players (ordered by their value) and then loop thru the resultset.
Code:strSQL = "SELECT * FROM player ORDER BY player.score DESC;"rsPowerbabe.Open strSQL, adoConFor i = from_rank To to_rank Response.Write (rsFootballPlayers("name")) Response.Write (",") rsFootballPlayers.MoveNextNext
I think it would be more effective, if I could let access do the
hard work..
Any ideas?
Thanks in advance,
Rost
View 3 Replies
View Related
Oct 17, 2005
I have set up two queries to generate a rank order based volume generated. The query does what I want for the most part in that if two parties have the same volume, they have a common rank (this is what I want to have happen). The problem comes that at various points throughout the data set, some of the rank sequence will be skipped...example, rank 265 to 267 (skips 266) and rank 786 to 790 (skips 787, 788, 789).
How can I close the gaps?
Here are the two queries...
Query 1
SELECT VolRnkALL.group_code, Sum(VolRnkALL.avg_group_volume) AS SumOfAvg_Group_Volume
FROM VolRnkALL
GROUP BY VolRnkALL.group_code
ORDER BY Sum(VolRnkALL.avg_group_volume) DESC;
Query 2
SELECT qry_SetRankSTEP1.group_code, qry_SetRankSTEP1.SumOfAvg_Group_Volume AS Expr1, (SELECT Count(*) FROM qry_SetRankSTEP1 t1
WHERE
t1.SumOFAvg_group_volume >= qry_SetRankSTEP1.SumOFAvg_group_volume) AS Rank
FROM qry_SetRankSTEP1;
Thanks for your help
View 2 Replies
View Related
Jun 26, 2006
Hi All,
I'm having a problem with my rank query......I have some unique integers that are all being ranked the same.
Sample values in dates table, stored as numbers:
[date]
200544
200545
200546
200547
etc
I'm using the rank example from the MS website, as follows:
rank: (Select Count(*) from dates Where [date] < [dates].[date];)
I get a rank of zero for every value!
I've tried running this in the MS sample DB and it works fine.
Any insight appreciated!
Cheers,
N
View 1 Replies
View Related
Feb 20, 2008
Hi,
I am trying to add a column into a query to rank items in that query.
For example, I want this table:
Name Score
Lee 10
Simon 8
Steve 8
Mike 6
to read:
Name Score Rank
Lee 10 1
Simon 8 2
Steve 8 2
Mike 6 4
I have read the Microsoft help but everytime I try and change the alias name of the query within a query, Access automatically says it is closing??
Any ideas
Many thanks in advance
Lee
View 1 Replies
View Related
Sep 22, 2015
Right now, I have an output that looks like this:
Code:
AvgCost CompanyID Policy#
25 22 12
28 23 12
35 24 12
21 25 12
20 22 20
15 24 20
13 23 21
43 24 21
Etc.
I want to know if it is possible to get an output that adds a ranks the CompanyIds by average cost per each policy #.how companyID24 ranks, and I have the query set up where it only outputs Policy#'s that company 24 is ranked in (has sold product in).
Code:
AvgCost CompanyID Policy# Rank
25 22 12 2
28 23 12 3
35 24 12 4
21 25 12 1
[code]....
View 11 Replies
View Related
Jan 18, 2015
I have the query below that return a table like:
PLOTNR; period,Value, ID, Basal_area/ha, Basal_area/ha, perc_BA_sp
What I want to is to add another field that rank the perc_BA_sp by PLOTNR descending (thus highest perc_BA_sp values rank one etc.)
Code:
SELECT[Q:INV1-Basal_area_plot-spp].PLOTNR,
1 AS period,
[Q:INV1-Basal_area_plot-spp].Value,
[Q:INV1-Basal_area_plot-spp].ID,
[Q:INV1-Basal_area_plot-spp].[Basal_area/ha],
[Q:INV1-Basal_area_plot].[Basal_area/ha],
([Q:INV1-Basal_area_plot-spp]![Basal_area/ha]/[Q:INV1-Basal_area_plot]![Basal_area/ha])*100 AS perc_BA_spFROM[Q:INV1-Basal_area_plot-spp]
[code]....
View 1 Replies
View Related
Apr 8, 2007
Hi
I need to define this query:
I have 2 unbounded fields : ShowMonth and ShowYear (Each field represent the month and the year of Date field)
And UserName field
I need to define query that shows the dates and the UserName accordding to ShowMonth,ShowYear and UserName field (Without SQL).
I will be happy if serious answers will be post
Thanks
View 3 Replies
View Related
Apr 21, 2014
I am looking to find out if I can build a "on the fly" pass-through query based on user predefined data.
Basically I am linking to a massive database. Access link to DB2 table. In order to make the system useable for the users they need to edit the query before it's ran. If not the query over the network takes around 5 minutes every time it's accessed and options such as filters and sorting are chosen. So if a user sorts one column and filters on another the query has now ran 3 times. Once to open, once to filter, and once to sort. That just took 15 minutes.
So If there is a way to build macro or form that asks them for specific information first and then modify's the query so that it only gets ran once that would be fantastic.
Example:Table has 7 fields/columns. Usually they will do 3 things. Pull back data from either a month or quarter. Then filter a column by it's content. Then sort by date of another date/timestamp column.
View 1 Replies
View Related
Oct 18, 2013
I have a query and a form, and what I want to be able to do is have the user type in within the form the parameters for the query.
The part of the query that will hold the parameters is based on an amount (formatted as Currency), but I want the user to be able to enter >10 , =<100 or >100000 and get the correct results.
I have already set up the query and the form with unbound cells which are then referenced in the query I've tried just one cell where the user would enter >100000 or tried two cells where one cell would be for >,< etc and one cell for the value (which is formatted as currency), but that didn't work either.
The idea is that you enter the parameter and value then click on a button that runs a macro to export the query based or the user parameters, but everytime I try it I get a box appearing saying Property not Found.
View 1 Replies
View Related
Oct 30, 2007
Hi,
I have a query which brings back the following columns:
YEAR; PERIOD; STORE; PRODUCT; GP.
The table it is referring to is called 05_FULL_TABLE and I have changed the Alias for this table within my query to 05_FULL_TABLE1.
I'm trying to add a field which will rank the products by GP (Highest GP=1), within each group i.e. the rank starts from 1 again every time you hit a new year, period, or Store.
I've taken a suggested method from Microsft Support: http://support.microsoft.com/kb/208946
Seniority: (Select Count(*) from Employees Where [HireDate] < _
[Emp1].[HireDate];)
and changed it to: RANK: (Select Count(*) from 05_FULL_TABLE Where ([SumOfGP] < [05_FULL_TABLE1].[SumOfGP] AND [STORE_FKEY] = [05_FULL_TABLE1].[STORE_FKEY] AND [YEAR] = [05_FULL_TABLE1].[YEAR] AND [PERIOD] = [05_FULL_TABLE1].[PERIOD]) +1)
It is adding a rank, but it is not starting the rank again at any of the changes in store etc (which are all sorted Ascending in the query result), so I guess my "WHERE" clause isn't right. Can anyone suggest where I've gone wrong?
Many Thanks
Andrew
View 2 Replies
View Related
Jul 7, 2005
I have a table which has got the following fields:
Name and
Points
I want to create a third fieldw with the name of rank which should give automatic results of rank that is the one with the maximum points should have '1' written in his rank column. Similarly, all rank fields should be filled accordingly.
I used the help, it said i should apply the syntax.
I do not know how and where to apply that syntax
Your help is appreciated
[HassaaN]
View 1 Replies
View Related
Aug 22, 2013
I need to get the 4th column of this table:
PeriodPlayer AmountRank
201301ana 150 1
201301luis 100 2
201301raul 50 3
201302ana 125 1
201302raul 100 2
But I do not know how.
I have get up to there:
Rank: (Select Count(*) From [Tabla] Where [Amount]>[Player1].[Amount]+1
View 1 Replies
View Related
Dec 5, 2011
I trying to use Oracle Rank function in MS-ACCESS. How do I do that? Here is table and output I am looking for
Table: TaxType
Tax_no Tax_Name Start_Date Tax_Percent
----------------------------------------------------------------------
1 VAT 1/1/2008 2.3
2 VAT 1/1/2009 2.5
3 VAT 1/1/2010 2.6
4 REW 2/1/2008 1.6
5 REW 2/3/2009 4.3
6 OTH 3/1/2008 5.6
7 TGH 11/1/2009 6.7
If I pass a Date 10/1/2009 I need below result(ie maxdate of each Tax_Name with percentage)
1 VAT 1/1/2009 2.5
5 REW 2/3/2009 4.3
6 OTH 3/1/2008 5.6
I used to do this in oracle using RANK function. I do not know how to do this in MSACCESS
SELECT Tax_ID, Tax_Name, Start_Date, Tax_Percent,
RANK() OVER (PARTITION BY Tax_Name ORDER BY Start_Date desc) as Date_rank
FROM TaxType where start_date<=to_date(10/01/2010, 'mm/dd/yyyy')
View 3 Replies
View Related
Feb 6, 2013
I have a competition ranking contestants. A contestant can compete multiple times, each time with a unique registration number.
I am trying to sort by score to rank the contestants, however the contestants can only receive one rank - their highest score (not a sum of their scores, only one score). ? ?
Example:
Registration Name Score
2345 Sally 247
3456 George 230
4672 Sally 255
What I want to see:
4672 Sally 255
3456 George 230
View 5 Replies
View Related
Sep 25, 2013
I am developing database for my school. I am done with everything except the examination aspect where i need to indicate students position in every subject beside their overall position according to the sum of all the subject scores. How to indicate student position in subjects.
View 2 Replies
View Related
Oct 12, 2004
Hi, all... :)
I need to create cascade relationship on fly.
I've succeeded to create the relationship by:
ALTER TABLE tName ADD CONSTRAINT fk_id FOREIGN KEY(ID) REFERENCES tPrimary(ID)
and even succeeded to check the referential integrity by:
ALTER TABLE tName ADD CONSTRAINT fk_id FOREIGN KEY(ID) REFERENCES tPrimary(ID) CASCADE
The real problem comes when i am trying to enforce the cascades. I do not find any expression doing it.
the standarts of sql: on update cascade
or
of sql server: on update cascade go
just don't work in access :(
Thankful ahead
View 1 Replies
View Related
Jul 12, 2007
Hello,
My Access application consists of two MDB files: the application file and the database file. The application file has links to tables from the database file.
Now, I want to define relationships to optimize my database performance. Where should I define them? In the database file? In the application file? Both?
Thank you!
View 3 Replies
View Related
Apr 18, 2006
Hi!
I have a table with x columns.
In a form I can choose 4 different columns to define my search.
Now, I have this SQL-statement, that strangely doesn't work. Can someone help me with this?
SELECT a.Nr, a.Objekt, a.Applikation, a.Modul, a.Datum, a.Anmalare, a.Onskemal, a.Klart, a.Prio, a.Status, a.Ansvarig
FROM tblArenden AS a
WHERE ((IIf(Not IsNull(Forms!frmFiltrera!cboObjekt),a.Objekt Like Forms!frmFiltrera!cboObjekt & "*","*"))<>False) And ((IIf(Not IsNull(Forms!frmFiltrera!cboApplikation),a.Applika tion Like Forms!frmFiltrera!cboApplikation & "*","*"))<>False) And ((IIf(Not IsNull(Forms!frmFiltrera!Prio),a.Prio Like Forms!frmFiltrera!Prio & "*","*"))<>False);
View 1 Replies
View Related
Oct 23, 2013
I am trying to save my table and I get a message saying "Cannot define field more than once". I only have 14 fields and none of them are repeated. My field titles are: First Name, Last Name, SSN, Wage, Salary, DOB, Hire Date, Years Employed, Phone Number, Alternate Phone, Address, City, State, and Zip. What am I doing wrong?
View 1 Replies
View Related
Dec 5, 2007
Hi,
I am trying to create a db for software change requests. I have defined 3 tables:
PCR - Change request table. This table contains all info on the software bug.
Release - Software release table. This table contains info about the release date, platform i.e. SAP
Sponsor - The person funding for the change to take place. This table contains info on the sponsor such as Name.
I have identified that I need a one-to-many relationship between the tables. For example, A release will have many PCR's. A sponsor will have many PCR's they are funding.
My Primary keys are: PCR Table - PCR_ID, Release Table - Release_ID, Sponsors Table - BPM_ID.
In order to get a one to many (the many being on the PCR table) I have put two foreign keys in the PCR Table (Release_ID and BPM_ID). Both of these keys in thier own tables are autonumbers. From my undertstanding to get MS Access to relate the data I need to set the foreign keys in the PCR Table to autonumbers. Or do I? I do not want to change the primary keys in the Release and Sponsors table to datatype number as this would mean user manual input which I am trying to avoid.
Thanks in advance for the help.
Ket
View 1 Replies
View Related
May 12, 2014
How can I define a variable which can be used by another Sub and of course the value stored in it?For instance:
Private SUB A ()
DIM A1 as String
A1 ="ABC"
END SUB
PRIVATE SUB B()
PRINT A1
END SUB
View 5 Replies
View Related
Dec 16, 2004
I'm trying to add filds to a table, and when I try to save I get the error message: cannot define fields more than once. The problem is, there are no duplicate field names. I've run compact and repair (several times) with no change.
Any ideas?
Thanks,
Mary
View 1 Replies
View Related
May 1, 2014
I have one color scheme I want to use all through the database I am developing. The next examples have just one color defined, to make it simpler (a dark blue, that I would call B1)
Code:
private sub setlabel()
Dim B1
B1 = RGB (0,52,105)
me.label1.forecolor = B1
end sub
... however this means I have to repeat the color definition every sub, so I thought would be neater to define a function to set my color codes (I have 20 colors).
Code:
Function SetColor()
Dim B1
B1 = RGB (0,52,105)
End function
My objective, is when I'm working in forms, Iwould (ideally) call this function "setcolor" and just write my code for the blue. I tried the examples below:
Code:
Private sub setlabel()
SetColor()
me.label1.forecolor = B1
end sub
[code]...
Again, this is probably some definition of arguments or dimensions that I am not aware oh. How to predefine the colors in a function to give them a "short" code which I can call in any sub in the database?
View 4 Replies
View Related
Oct 28, 2014
I have a multi-user database. I would like based on the logged-in user, some buttons and controls in different forms be disabled and the others be enabled.
There is a login form that gets the username; I made also a function to define authorities for users in it.
My plan is that when user enters to the database, on load of the main menu the function calls and disables the defined controls in different forms. I tried to use this kind of codes:
Function User_1()
[Forms]![frmMainMenu].[cmdUpdateDatabase].Enabled = False
[Forms]![frmChooseReports]![cmdOrdersFollowUpReports].Enabled = False
End Function
Function User_2()
[Forms]![frmMainMenu].[cmdChooseCharts].Enabled = False
End Function
But this code only works for the forms that are opened at the moment and if one of them is closed the system gives Error.
View 2 Replies
View Related
Sep 25, 2013
I have a table which is formatted as shown:
ID, My_Var, My_Value, Notes
This table holds variables that I want to declare to use throughout my application. I have been told in another thread that tempVars are the best way to do this.
I have written the following code, which works on a limited basis:
Private Sub btnSetVAr_click()
TempVars.Add "udvVar", Me!My_value.Value
End Sub
This defines a single variable on each button press, fine to work out how the code works, but not much use. What I really need to do is when the initial menu screen loads to call a routine to assign all the variables stored in the table using a loop to do this. The idea is to make all variables values easy to edit or add to, rather than have to edit code each time we need to change them.
The variables table holds 14 records so far, such as:
My_Var, My_Value
EuroRate, 0.885
ConDisc, 0.9
MollDisc, 0.8
As well as holding numbers, they hold strings and date values.
So, on loading the initial form, use an event to assign the variables from the table using the tempVar name as the value held in "My_Var" and it's value as held in "My_Value"
View 6 Replies
View Related
