Queries :: TOP N Subquery (Scores) For Each Member In Race League
Jan 5, 2014
I have a race league, I want to select the top 10 point scores for each member.
I have read the Allen Browne article (and many others) and tried many variations on his code but cannot get this working.
I face two issues
- The ORDER by clause has no effect, points are not sorted with largest first
- Access being unable to differentiate between scores with the same value and returning additional records. I have added an "Event" field to make the record unique, but this does not seem to work.
Query code is
SELECT qLeague.Member, qLeague.Event, qLeague.Points
FROM qLeague
WHERE qLeague.Points IN
(SELECT TOP 10 Points
FROM qLeague AS Dupe
WHERE Dupe.Points= qLeague.Points
ORDER BY Dupe.Member, Dupe.Points DESC
)
ORDER BY qLeague.Member ASC, qLeague.Points;
This returns more than 10 results per member:
Member Event Points
Alex Peters SDMC North Weald Sprint 3
Alex Peters HCAAC Debden May 3
Alex Peters GB/Harrow TAMS NW Sprint 4
Alex Peters HCAAC Debden Sprint 5
Alex Peters Llys y Fran Hillclimb 6
We are developing a complaints tool. Each completed complaint needs to be signed off by 3 leads and I'm hoping to display the progress of this in a form. Obtaining the first is simple and I was able to do that relatively quickly. The subsequence ones are now giving me a headache as it doesn't seem I can reference the initial subquery field in the others.Here's what I have so far:
Code: SELECT COMPLAINT_TBL.COMPLAINTID, COMPLAINT_TBL.CASENUMBER, COMPLAINT_TBL.COMPLAINTANTFORENAME, COMPLAINT_TBL.COMPLAINTANTSURNAME, BUSINESSUNIT_TBL.BUSINESSUNIT, COMPLAINT_TBL.FINALRESPONSEDATE, (SELECT TOP 1 [SIGNOFF_TBL].[SIGNOFFDATE] FROM [SIGNOFF_TBL] WHERE [SIGNOFF_TBL].[COMPLAINTID] = COMPLAINT_TBL.COMPLAINTID ORDER BY [SIGNOFF_TBL].[SIGNOFFDATE] DESC) AS FIRSTSIGNOFF,
I am creating an access database for the results of my triathlon times and I am having trouble with the race results. Based on some information I found here, I am using number fields for the swim, bike and run times because I want to do calculations and also sort them and it doesn't sort properly if they are text.I have created separate fields for the hours, minutes and seconds for each of them.
Now I am trying to make a calculated field to convert the numbers to the following..For a swim time, I want to convert it to the time per 100m. I have the calculation for that, but the result gives me the decimal portion and not the actual seconds portion and I am stuck. Here is what I have so far :
So as you can see I have added up all the number to get the total number of minutes and then converted to the minutes per 100m (the race is 750m). And the result gives me 3.31. But the .31 is a decimal and I want to have seconds which is 18 seconds. I know how to do the calculation on paper but I don't know how to change my formula to fix this. On paper I have to multiply .31 x 60. But how do I refer to the decimal portion of the number and modify my formula? how to input race times as duration in an access database.
Extended Cost] and salestran.[Extended Price] for each salestran.[SKU Code/Number], Count the number of unique customers, salestran.[Customer Code/Number], per SKU for any transactions equal to or after salestran.[Transaction Date] 11/1/2014.
Problem is, doing a regular count on the Customer field returns an incorrect value. It counts the number of times the sku appears within the given date range, ie 6 transactions with 2 customers, my count says 6. I need a distinct count on the customer, for the above example I want to see 2. Here is the code I have so far which yields an error of "At most one record can be returned.." distinct count in my Select statement along with the other fields I want to see, ie Summary data and SKU.
SELECT Salestran.[SKU Code/Number], (SELECT COUNT(cd.[Customer Code/Number]) AS Count FROM (SELECT Distinct [SKU Code/Number], [Customer Code/Number] FROM Salestran) as cd GROUP BY cd.[SKU Code/Number]) AS [Number of Customers] FROM Salestran WHERE (((Salestran.[Transaction Date])>=#11/1/2014#)) GROUP BY Salestran.[SKU Code/Number];
I need to select a record with the latest (max) date along with associated values that go along with that record. However, some records have the same date. Initial query sorts by Asset_ID and Date (Desc)...A second query against the initial query then groups by Asset_ID, taking the Max Date and First Switch and Port...Results show the latest date but with a different Switch and Port
I'm doing a subquery to select the top 5 of products for each supplier. The selection needs to be done randomly on the products for each supplier. For this I have made the following query (based on Allen Browne's example):
SELECT tblProducts_temp.SupplierID, tblProducts_temp.GTIN FROM tblProducts_temp GROUP BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN HAVING (((tblProducts_temp.GTIN) In (SELECT TOP 5 Dupe.GTIN FROM tblProducts_temp AS Dupe WHERE Dupe.SupplierID = tblProducts_temp.SupplierID ORDER BY RND(Dupe.GTIN) DESC))) ORDER BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN;
The query returns a random number of products, but not a top 5. So for supplier X one time 3 products and the next time for supplier X 7 products.The query without the RND function, so just the top 5 works fine:
SELECT tblProducts_temp.SupplierID, tblProducts_temp.GTIN FROM tblProducts_temp GROUP BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN HAVING (((tblProducts_temp.GTIN) In (SELECT TOP 5 Dupe.GTIN FROM tblProducts_temp AS Dupe WHERE Dupe.SupplierID = tblProducts_temp.SupplierID ORDER BY Dupe.GTIN DESC))) ORDER BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN;
I have a one-to-many query which I would like to add a subquery to eliminate all but the TOP 1 of a field.
Here is the SQL:
Code: SELECT QrySitesBatteries.SiteKey, QrySitesBatteries.SITEID, QrySitesBatteries.Battery FROM QrySitesBatteries WHERE (((QrySitesBatteries.Battery)=[Forms]![FMHome]![Battery])) OR ((([Forms]![FMHome]![Battery]) Is Null)) ORDER BY QrySitesBatteries.SITEID;
And I would like to only show the TOP 1 of the SiteKey field.
So, I think I have to add the subquery before the ORDER BY, but how to do it?
At most one record can be returned by this subquery. (Error 3354)
This is my SQL:
SELECT Students.[Student ID], Students.[Student Name] FROM Students WHERE (((Students.[Student ID])=(SELECT [Student ID] FROM [Student_Sports] WHERE [Sporting Team] = [Forms]![SV_Teams]![txtTeam])));
I have a table of accounts and a table of rates. There is a one-to-many relationship between them (i.e. each account can have multiple rates, each with their own - unique - effective date)
I'm trying to build a query which will show me all of the accounts in the accounts table and the most recent rate (based on the effective date) for each of those accounts.
This is as far as I've gotten with the SQL :
Code: SELECT [tblAccounts].[AccountID], [tblAccounts].[AccountNumber], [tblAccounts].[AccountName], [LatestRate].[IntRate], [LatestRate].[EffectiveDate] FROM [tblAccounts] LEFT JOIN (SELECT TOP 1 [tblRates].[AccountID], [tblRates].[IntRate], [tblRates].[EffectiveDate] FROM [tblRates] ORDER BY [tblRates].[EffectiveDate] DESC) AS LatestRate ON [tblAccounts].[AccountID] = [LatestRate].[AccountID]
But this can't work because the [LatestRate] subquery can only ever return one record (i.e. the most recent rate across all of the accounts)
I need the most recent rate for each of the accounts in the main query
(FYI - I use an outer join as it is possible for no rate to be available in the rates table for a given account, in which case I want to return the null value rather than omit the account from the resulting dataset...)
I've created the following but it keeps coming up with the error message You tried to execute a query that does not include the specified expression 'ICE Team' as part of an aggregate function.
SELECT ztSub.[Master Sheet].[ICE Team], ztSub.[date], Count(ztSub.[Count]) FROM (SELECT [Master Sheet].[ICE Team],[Master Sheet].[Visit Date (planned for)] AS [date],Count([Master Sheet]![Visit Date (planned for)]) AS [Count] FROM [Master Sheet] UNION SELECT [Master Sheet].[ICE Team],[Master Sheet].[Date retasked to?] AS [date], Count ([Master Sheet]![Date retasked to?]) AS [Count] FROM [Master Sheet] ) AS ztSub GROUP BY ztSub.[Master Sheet].[ICE Team];
Is there an easy way of doing this? I have a database that I want to automatically place racers as they finish. I have four race divisions. All racers no matter the division start at the same time. I have all of this figured out, but what is getting me is the finish place (1st, 2nd, etc.) Is there a way on a continuos form to automatically place the racers? How this works is...Main form has racer info., on this form is a stop button, and after the button is clicked, it will show his/her finishing place in their division which is subform of the main form. Here is the reason I want this. Someone may have registered wrong and they are in the wrong division, and we have to change them to the correct division after they have finished and already been placed. I want to be able to move them to the right division, and then my subform requery and they placed in the correct position in the new division. Sorry this is so lenghty. Plain and simple can I make a subreport have an unbound field that shows each records place the recordset? Thanks for your help, and like I said sorry this is so long.
I am creating a DB for race results and have a field which calculates the elapsed time based on the start and finished times. What I want to do now is populate a field "position" with the finishing position relative to other competitors elapsed times in the race. But I cant see how to do this.
I understand I would probab;y have to use some sort of query but not sure how to create it.
First of all I consider myself to have Intermediate knowledge of Access. I am comfortable building tables, queries, reports, macros, etc. but get a little lost when needing to manually code something in a query.
I need to create a database to document quality reviews of certain reports the plant creates. Typically each report gets reviewed by 2 to 6 people and each section is scored. So lets say the database table has the following fields
I need a query that will average each of the Section Scores and Total Score so I can build a monthly report showing the report and the average grade for each section and the average total grade.
I am new to using access but have managed to build a database that I use to find qualifiers for horse racing based on stats I import for all the racers in the days racing. I have a query which shows the date, time, track, horse and trainer but some races have more than 1 selection so I want to add a field that counts the number of qualifiers per race. I am finding it hard to do a countif, date AND time & track are all the same within that query.
I also want to add another show the total runners in the race but this will have to look into the tables where the query is run from. Hopefully I can figure this out when I find out how to solve my original problem.
Hi everyone, im new to this forum as well as MS Access. Over the past week or so Ive been reading through a lot of tutorials and watched a few videos on how to master Access. I've learned a handful of things, however its not enough, so i need some help.
Im trying to create a simple database for our local community football/soccer league. It's nothing fancy, just simple information to keep things tidy and organised.
The main features include Keeping a track of all results and outputting an up-to-date league standings, so it can be printed and available for everyone to see.
Few details: in soccer, there are 3 possibile outcomes of a match (win,draw,lose). Each win earns a team 3 points, draw 1, lose 0. Of course, the standings table will be sorted by the amount of total points each team has uptodate (descending order). Oh, and the goals scored - goals conceded should also be summed up for each team and displayed.
In theory, it sounded easy to me, but when i actually started doing it, i got stuck right at the beginning... :confused: So, here's what i got so far: tblResults table that collects the following information: Date, TeamAName, TeamBName, TeamAGoals, TeamBGoals (the last two collect how many goals team a and b scored)
...well, thats pretty much it... lol :o
I've been experimenting with queries for a while but feel without some help i wont get it sorted... Would appreciate if anyone could point me in the right direction... :)
I am looking to create a database that collects data from past tests, predict probable future score, and compare to goal score. Currently I am studying for the Bar Exam and want to track (in all 7 subjects):
-my current scores for practice exams -prediction of what my score will be on exam day -comparison of actual and goal score -comparison of predictive and goal score -all of the above, separated by different types of Tests for each subject
Below is an example of the type of data :
Ex: Civil Procedure--06/15/15-Kaplan questions-> 6/10 (60% practice score)-> (predict gain 1 point 70%)-> (actual exam goal 20/28=71%) Civil Procedure --06/15/15--past MBE questions->7/10 (70% practice score)->(predict gain 1 point 80%)-> (actual exam goal 20/28=71%)
Also from this data I want to generate graphs
Would this be possible in Access? Should I use excel instead?
I am trying to create an append query that will update employee time cards for holiday hours without having to enter each time card individually.
* I have an employee table which has the employee id, first name, last name and so on.
* I have a timecard table which holds the employee id, date and time card ID.
* I have a time card hours table which holds the time card detail ID, time card id, work order id, job code id, reg hours, ot hours, holiday hours, etc
* I have a time card expense table which holds the time card expense id, time card id, work order id, inventory item id, cost, total, etc
I need to create a form with a multiselect list containing a list of the employees and a box for the date and for holiday hours. I need to be able to select the employees, enter the date, enter the holiday hours and append it to the time card table AND the time card hours table.
For example I need to append and create new records from the form: the employee id (from the multi-select combo list) and date (txtDate) would pass to the time card table and the holiday hours (txtHours) needs to pass to the time card hours table.
I cannot for the life of me figure this out...it doesn't seem like it should be too hard.
Hi does anyone know of a system out there in MS Access that can do what this program does in word. i have been using this word version but i wanted to keep a history of records...
I use this for Badminton and not squash, so if anyone wants a game i am happy to play
anyway have a look and let me know how hard this would be to achieve.
I've been asked to collate some information based on a kid league, I am trying to sort out how precisely it works
the data I have is this
finish position, name, club
I need to calculate the best kiddie based on 3 of a possible 6 races
So tbls
tblResults fldSeries NAME CLUB Pos(isition)
tblScores fldID pos Points
query1 SELECT tblResults.fldSeries, tblResults.NAME, tblResults.CLUB, Sum(fldScore.Points) AS SumOfPoints FROM tblResults LEFT JOIN fldScore ON tblResults.Pos = fldScore.Pos GROUP BY tblResults.fldSeries, tblResults.NAME, tblResults.CLUB, tblResults.Pos HAVING (((tblResults.fldSeries)="220-2005") AND ((tblResults.Pos)<51)) ORDER BY Sum(fldScore.Points) DESC;
I need this to be changed to only pick up athletes with only the best three scores?
I am wondering if it is possible to calculate scores automatically based on the number of tick boxes the users have selected? If yes, how do I go about doing this feature?
I have created a database that records the scores of each player etc and puts them in league table like structure.
Within this database which I would like to extend I would like to know if its possible to have a query to calculate a league table for the teams if home team overal score is greater than away team home team get 2 points for win 1 for draw no points of loss and same if away team won.I also want to be able to change it to a web database and add club address details like contact list to it.
I run a soccer league where we track players attendance for each game. I currently do it on a spreadsheet where each game date is a column and each player is a row. We also track which team they play on at each game (they can play on different teams different weeks). I currently have a second tab in the spreadsheet to record which team a person plays on each week.
Setting up a table of fields for this is relatively easy. The problem comes to data entry. I want to be able to visually see the data like I can in a spreadsheet (names in rows, dates in columns, intersections containing either team name or whether attended) and whilst a cross-tab query gives me the layout, I cannot input data in a cross-tab query.
Lets start by saying I am not a programmer but by modifying examples found in books I have over the past few years built an order processing data base for our business. An expert would think what a mess but it works for ME. The problem being I would like my staff to be able to use it.There is one problem which I can get around but they would not be able to. The database has two tables (well more than that but its two I am talking about here) One which holds details of 3000 + Roads. The other has House or Firms names and directions. On the order form I have two Combo boxes one to enter the Location (House Name) another for the Road Name. When you enter a House name the Not In List event looks to see if we have delivered to that location before, if so displays the full address. name,number,road,town in the combo box fed from a query which uses an EXPr to re join the data fields to display as one. If the location has not been used before a yes/no box comes up. If yes a new form opens to enter the details and choose the road/town from the road list.Then you are returned to the order form. The problem is let say you start to enter "Orchard Cottage" up comes Orchard Cottage,London Rd,Larkfield. Want you want to enter is another Orchard Cottage in say Ditton. Because you already have one in the list the combo box will not ask if you want to enter a new one. I can get around it by putting an extra letter in the name, then once the order had been finished go to the location table and take the letter out. No good for the Staff as you can see. Shame the rest works fine. Anyone have an answer ?
