Queries :: How To Rank By Categories

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 Replies


ADVERTISEMENT

Queries :: Rank Fields Of A Query Based On Another Field

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

Tables :: Structuring Categories And Sub Categories

Dec 27, 2012

Suppose I receive boxes and store them in a room. Each box can contain items that are either shaped as cubes or spheres and I have to allow for the addition of more shapes in the future, but only one shape can be in each box. Each box contains a random number of shapes and two boxes may, or may not, contain the same number of shapes as another.Cubes can be either Red or Green, Spheres can be either Blue or Yellow.

I wish to set, and have the ability to change, how many cubes and how many spheres, on a percentage basis, I wish to have (e.g., 60% Cubes and 40% spheres).I also wish to set, and have the ability to change, what percentage of cubes I want to be Red and Green and which percentage of Spheres I wish to be Blue and Yellow (e.g., Cubes -Red 70%, Yellow 30% )

e.g. -- I have several boxes totaling:

Cubes
Red 30
Green 40

Spheres
Blue 10
Yellow 20

My goal is to produce three reports.The first report would provide the Target and Actual number of Cubes and Spheres expressed as a Value and %. The #'s would simply be calculated from my desired % . So, if I wanted to have 60% cubes and I had 100 Shapes, my target # would be 60. If I had 200 Shapes it would be 120. (i.e., The actual number of shapes I have is simply a factor of how many I receive and I have no control over this.)

The First Report would look like this

Target % - Actual % - Target # - Actual #

Cubes 60% 70% 60 70
Spheres 40% 30% 40 30

The second and third reports would be the same format but would break down cubes into red and green and spheres into blue and yellow.I can accomplish the Report that breaks down the shapes but I am running into problems with the next level as I need the total number of shapes and the the total number of only Cubes or Spheres to insert into the expressions.

View 2 Replies View Related

Getting Post Between Rank X And Rank Y

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

Queries :: School Database - Student Rank In Individual Subject

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

Queries :: Counting Table Categories In Query

Feb 5, 2015

I am facing the difficulty to solve the calculation of my Employees payment table in query, employees having three type of payments 1. Leave 2. Air ticket 3. EOSB so I have created three different queries named Airticket_Accruals, Leave_Accruals, EOSB_Accruals for these tables.

The problem is I need to include how many payments have been done to every employee in total to my every individual query (airticket,leave & EOSB) than I will less the accrual that will give me the balance I need to pay them.Field for Payment table is as follows and it has relation with Employees table with Emp_ID field.

View 2 Replies View Related

Rank Query Gap???

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

Help With SQL To Rank Records

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

How To Rank Data Result

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

Help With Rank Query- Everything Gets Ranked The Same!

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

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 1 Replies View Related

Rank Column In Query

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

How To Use Rank Function In Ms Access

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

Average Rank Query

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

General :: Remove Rows With One Duplicate Field In A Rank?

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

Categories And Sub-categories

Jan 23, 2005

Hi,

I'm very new to Access, having used it for the first time today :)

I've got a database to write for a media database as a learning example and I've run into my first real problem...

I've got a table for users and I've got to allow users to be able to own one or more media types, e.g. CD, DVD, video, record, etc. Of these types, there are sub-categories for most of them. For example, a DVD may be a horror film, action film or music video, and a CD may be hip-hop, rock or blues, etc.

I thought I could have a reference data table containing a category ID and category value, together with a reference data category table which simply listed the categories. This would work fine if a user could only have one type of media but of course they can have several. So, I thought about having a "composite category" instead which would contain a category, whether it contained a sub-category and a sub-category ID. I'm trying to get my head round this and wondered if this must surely be a fairly common issue to solve. Does anyone know of any pointers or any examples I could do with looking at?

Sorry if I haven't explained it too well :o

View 2 Replies View Related

Categories

Oct 28, 2005

Okay here is my silly little issue, my boss decided to add sub categories, just when I get the database set-up.
Table 1
Category
Category_ID
Category_Name

Table 2
Sub_Category
SUb_Category_Name

Table 3
Contacts
Company
Contact
Adress
Ect Ect

I would like to be able to have each contact grouped by category, then sub category..but I am not sure how to get the relationship to work.

View 2 Replies View Related

Multiple Categories

Aug 9, 2006

Hi guys,

I am implementing a new database (Access 2000) for my company.

It contains contact details of a subscription base.

Four tables are present MEMBER, ORGANISATION, EVENTS and CATEGORY.

My problem is; people attend events, and i want to record this in the database. Each event has an event ID field, and an event description field.

When recording the events which people attend, i could just use the event ID field, but problem is that people can obviously attend more than one event..

Also;

People belong to a certain category

Again i have a category ID field, but people can belong to more than one category.

Any ideas on how to record this info??

View 4 Replies View Related

Newbie - Categories

Jan 3, 2007

Hi,

I am working on a database to list all the inkjet cartridges we sell. Within the printer table, I want to list which category they are on our website. For example, Epson C20 Printer would be in Ink & Toner > Epson > Inkjet > Stylus C & CX Series.

I what some way to enter these in a number of fields, say Cat1, Cat2 etc. I havent a cluw what would be the best way to do this? Has anyone got any ideas? I am thinking about some kind of drill down but wouldnt know where to start on this. Any help would be gratefully received?

Cheers

J

View 3 Replies View Related

Categories And Subcategories

Apr 1, 2005

I'm wondering how to design an Access database with the same type of structure as "Yahoo Shopping!". The Yahoo site allows search for women's clothing or men's clothing or all clothing...please explain how the relationships are structured.

Is it just one category table with queries that display results of a search of multiple categories? How do I structure it in Access queries and forms?

Thank you!

View 5 Replies View Related

One Record Multiple Categories

Jan 20, 2005

Hi

I've built a contacts database which consists of one table containing contacts and services provided and a table with a list of categories, each has a PK autonumber id field. My problem is that individual contacts could fall into multiple categories, could somebody tell me how to do this.

Many Thanks
Anita

View 5 Replies View Related

Ranking / Top 10 Under Multiple Categories

Sep 18, 2007

In a table I have:

- Category (A, B, C)
- Family (A1, A2, A3, B1, B2, etc)
- Item
- Sales

I'm looking for a way to get the top 10 items in each category + family based on sales (ie: top 10 in A-A1, then A-A2, then A-A3, etc). When I try using the top function, it doesn't treat each category + family combination as unique. Any ideas?

View 5 Replies View Related

36 Categories How To Update? Version: 2003 (11.0)

Jul 12, 2005

Goodmorning,

Got a problem here with Access 2003: got 36 categories (example: volume from 0-0.1=category 1 vol from 0.1-0.2=category 2 etc) and a whole bunch of records (in a table) stating volume per record (0.111, 0.23456 etc). For each record I need to know which category it is. Now in order to prevent me from writing 36 update queries for each category (if vol>0 and <0.1 then category = 1 etc) there must b a smarter/faster way to do this. What is it?

Thx,
Lobhaan

View 4 Replies View Related

Autofill 3 Text Categories In Subform

Nov 11, 2004

I have a form for recording site details and have been trying to autofill three of the four categories.

What I have is a Site Index Table that contains the site ID, Name, Description and Type. I want the results to
be recorded in an Affect Table that will also contain text data associated with these fields that I will enter
in manually.

I then have a form with the four fields; the site ID and Type are both bound to the Affect Table that records all the data
associated with each site. The Name and Descritpion source their information from a seperate query of the Site Index Table.

So what I want to do is to select the site ID on the form from a combo box I've created, and once selected it will automatically fill in the relevant text data into the Name, description and type categories on the form. I've been trying scripts with the DLookup function but have continued to encounter errors. If anyone can help PLEASE help me as i'm a novice when it comes to Access

Thanks

View 3 Replies View Related

Company Db With Multiple Product Categories

Jul 12, 2005

Hi!

I need to create a database of the info of a few hundred companies. Each company (food producers) specializes in various product categories, not just one. This is my problem/question: each company entry should then have a possibility to have as many product categories as needed. How can I do this without using "free text" fields? The website user should obviously also be able to search the db - by clicking checkboxes with the categories maybe?

(+Also: When the admin user needs to add the info of a new company (with, say, 9 product types) into the database, what kind of an SQL clause I need to INSERT INTO the db? For example if the product categories where laid out as checkboxes, and the user would just click the ones he wants.)

View 8 Replies View Related

General :: Separate A List Box In Two Categories

Nov 28, 2012

I would like to separate my list box in two categories.Indeed it is composed by 2 types of criteria but arranged in the same List box (I can't create another List box, it's forbidden).how can I proceed?I've thought to insert a dash but it has to appear all the time and I don't know if it's possible to do that.

I've also thought to enter a title IN the list box above each category but it has to appear all the time too..when I click on one item in the List box it becomes highlight.May be code the 2 titles I could add for being always selected (highlight).I don't want to have to select the 2 titles every time!!!

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved