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]
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
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
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
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
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
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
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
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')
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]....
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
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]....
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 RelatedI am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I'm using access 2003.
I want to get access to create amazon shipments of 15kg in weight made up of a variety of products (the shipment goes to amazon)
So I have a products table with all the weights per unit for each SKU in it.
I've just created a simple 'form', where I input the SKUs I want to send to Amazon & the quantity (this updates a temporary table), therefore I end up with this in my temporary table....
SKU QTY
XX01 15
YY01 10
...& so on.
I've made a relationship from the SKU of the temp table (the table where I'm inputting my data to), back to the product table (where the weight per unit is held).
ok, what I seek, is that each time I enter a SKU & Quantity via my form, to see some onscreen data which tells me the ongoing sum of all SKU weights I've used. Therefore I guess my question is...what functionality in Access should I be using that allows me to input data & yet also provides me with onscreen real time data based on some 'behind the scenes' calculations? From my limited understanding, a form is to get data into Access, a report is to get data out....but I want to get data into Access & see the result at the same time!
I have used DateSerial to calculate a future date in Microsoft Access form, but it wont save the calculated date on a table (I need the calculated date on a table so that I can generate a phone list sorted by dates).
I have tried to use the formula (=DateSerial(Year([StartDate]),Month([StartDate]),Day([StartDate])+21) in Defaul Value, without avail, and while the formula works in the Countrol Source, it wont save it to a table because it wont accept the formula and link together, so that I can do a report, or search on it.
If anyone can help I would be so greatful
Thank you
Nic
I have a form that contains two text fields i.e.; [Scat_text] and [STyp], which each can be changed by the users. I would like to combine the results of the two fields together and validate if the results exists in a query. What would be the best way to accomplish this?
View 1 Replies View RelatedAll using access 2010. Here's the situation. I built a searchform according to datapigtechnologies video. Used a query with criterias on fields i want to search on the form: ex.
Code:
Like [frm]![frmMyform]![MyField]
It worked fine i thought until i ran the same query outside the form without the criteria field. I filtered the query for the same result and the one used on the search form did not return all data records for that particular result set. Ex. on the searchform; I select the fields I wanted to filter then ran the query. I come up with only 9 records when it should be 18 I get with running the exact same query without the criteria and manually filter the results.
My question is that can we do multiplication of data of two columns and result is automatically displayed in third columns in datasheet view.? Is it possible ?
View 1 Replies View RelatedI there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies View RelatedAll:
I'd like to make the result of my text box flash (in my form) if the its value is less than 30..For example. If A - B < 30, then the result whatever it is as long as it is less than 30..Is this possible in Access? thanks in advance.. :
Hello guys,
I Have a table call Hq_Cases in oracle database,I created same table in access 2000,and I created a SELECT QUERy in access ,now when I run this query it shows me empty table,so I want to know how I can bring values from oracle into my access database.
Any suggestion is appreciable.
Thanks
vsap
Hi,
Im getting an odd result returned & I don't know why.
expression:=[lookup]*[yards]+[drop]:=0.296*30+-11.838
Result:=1.20E-27
If I replace value [drop] with -11.838 then the result is correct (-2.958)
ie:lookup]*[yards]+-11.838
The porblem seem to be when [drop] is neg.
how can i get the result from a query in to a variable, i want to use this result in a new query?
View 3 Replies View RelatedI have a SQL query in a string of a VBA procedure
(e.g. MyQuery = "select sum(Amount) from Investment")
and I want the result to be shown in a TextBox of my form Me.
I know several ways to run the query and to send the result into the TextBox (e.g. with QueryDef and RecordSet). However I guess Access should offer a more direct way to do such a simple operation.
Does anyone has a solution to do it the simplest way ?
Thanks.