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..
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;
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).
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.
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.
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?
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]
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')
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 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.
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 :(
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?
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);
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?
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.
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.
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.
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?
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.
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"
