I have a query called "Raceresults1Q" and a report called Raceresults1R. I would like to sort this query based on 3 fields, but in a specific sequence. See attached screen pic for clarity.
In the first screen pic i only have the times it took to finish the race in shortest to longest sequence.
In the 2nd screen pic i have added the overall position in race 1-10,the Category position and the gender position.
My results must be displayed in the Report called Raceresults1R. Its easy to get overall position - use =1 in control source of a textbox.
The other 2 sorting positions are my problems.
i have just reconstructed my db in a mini version, to give you the idea in access also.
Can the results in the top table be displayed in a Report format as shown in the bottom table of screen pic?
please help.
One thing i forgot to mention is that you can have between 10-16 different categories, that are all mixed up depending whether a lot of different ages entered for the race. I used 2 in this example.
The bad news is that there is probably an SQL based solution that I ain't got! Sorry, but SQL isn't exactly my area of expertise. Perhaps one of the more qualified Members in this area will point you in the right direction. The good news is that I do have a Code based solution based on the following Logic:
Create a Recordset based on the resultsdata Table.
Dynamically Update the [OverallPosition], [GenderPosition], and [CategoryPosition] Fields based on incrementing Variables dependent on specific Criteria on the Record position, [Gender], and [Category] Fields.
Open a Report whose Record Source is the resultsdata Table that has recently been Updated.
I'll Post the Code as well as a Demo (Attachment) that I used to arrive at a solution for this Thread.
Expand|Select|Wrap|Line Numbers
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim intOverallPosition As Integer
Dim intNumOfFemales As Integer
Dim intNumOfMales As Integer
Dim intNumOfSeniors As Integer
Dim intNumOfJuniors As Integer
Set MyDB = CurrentDb()
Set rst = MyDB.OpenRecordset("resultsdata", dbOpenDynaset)
Reports and queries are indeed different animals. If a report has no sorting or grouping specified it probably defaults to the order specified by the query, but generally a report will handle its own sorting and grouping separately. In the design view of a report there is a Sorting and Grouping button on the Report Design toolbar. Is that not all you need for this?
BTW. You could also manage this in the query if required. It's simply a matter of specifying the criteria you require, which can either be done in SQL, or the query design grid, whichever is more comfortable for you.
That's clever SQL from Rabbit (and likely to prove very useful over time if you use SQL). An alternative, for reports only, is to have a control on your report which has a value of 1, but has the property .RunningSum set to True. It's certainly true that Rabbit's suggestion is more elegant though.
Just knew that the SQL Gang would come to the rescue! (LOL). Please tell me if I am overcomplicating the issue, but aren't we talking about Three, Independent, Rankings based solely on Time Ascending (Post #1), to be incorporated into a single Query? I am referring to Overall Ranking, Gender Ranking, and Category Ranking via Time Ascending. The posted SQL would not cover all three of these conditions, would it? For my own experience, what would the actual SQL Statement be to affect all Rankings? Thanks guys.
It would cover all 3. You would use 3 subqueries. The overall ranking would use the subquery from the first example. And the gender and category ranking would use two copies of the subquery from the second example differing by the grouping field.
thank you to you all. Sql - is very painful to me as a novice but thx.. This seems rather complicated to me.
Mr Adezi - from your solution - it seems i should append all the records after the race into a new table and then generate the report from there with the different groupings such as category and gender. The names of the 10 or so categories that we use are fixed and is always used, so i will add them to the code.
Its a pity there is no "query" method to simplify this issue, so that it changes the results in real time, if one fixes an error after wards.
This screen pic attached is from somebody else doing timing like me, and i would like to achieve that eventually.
@neelsfer - Based on Rabbit's advice, I'll continue to attempt a purely SQL solution to this problem in my spare time. I honestly must tell you that I am not at either Rabbit's or NeoPa's skill-set when it comes to SQL, so do not be disappointed if an immediate solution is not forthcoming. You and I have come a long way with this Project, and I am not about to give up now! (LOL). In the meantime, my alternative Code based solution should work quite well. As far as Real Time Analysis, the Code would simply have to be executed at any Change, Addition, Deletion, etc...
Thx mr Adezi. As you correctly say we have come along way. I can do without that ranking, but it would be a very "nice to have".
Currently i have 3 different reports to provide the same results, as this one report would do.
i came accross this example just now. maybe one can adapt it
Sequentially Numbering Groups of Records
Another case I have run across for sequentially number records is where you want to number groups of records. Where each group starts numbering from 1 to N, where N is the number of records in the group, and then starts over again from 1, when the next group is encountered.
For an example of what I am talking about, let's say you have a set of order detail records for different orders, where you want to associate a line number with each order detailed record. The line number will range from 1 to N, where N is the number of order detail records per order. The following code produces line numbers for orders in the Northwind Order Detail table.
This code is similar to the prior self join example, except this code calculates the LineNumber as part of a subquery. This way the LineNumber calculated in the subquery can be joined with the complete Order Detail record.
The end result would be the same. There's nothing in my example that precludes you from including the rest of the fields from the record. That query can be rewritten as
The difference between the two is that my method would use 3 subqueries that select on one table while the other method would use 3 subqueries that cross join 2 tables and then inner join back to the main query. One of them would run quicker but I don't know which one that would be until I actually tried it.
Thanks to Rabbit, I am very, very, close to a solution. I'll post the SQL and an Attachment. The problem with Ranking occurs when Times are exactly the same, as indicated by the Rankings for 8:05:00 PM in the Attached Database. If no 2 Times are exactly the same, the SQL works perfectly.
Expand|Select|Wrap|Line Numbers
SELECT T1.firstname, T1.surname, T1.Time, ((SELECT Count(*) FROM resultsdata WHERE resultsdata.Time < T1.Time)+1)
AS OverallPosition,
T1.gender, ((SELECT Count(*) FROM resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.gender = T1.gender)+1)
AS GenderPosition,
Category, ((SELECT Count(*) FROM resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.Category = T1.Category)+1)
AS CategoryPosition
FROM resultsdata AS T1;
P.S. - Surprised I got this far, going to bed and will try to figure out the last remaining detail tomorrow, unless one of you guys beats me to the punch.
@Rabbit - I am really pulling my hair out on this one, and would desperately appreciate you assistance. This scenario is particularly funny, since I am bald! For the life of me, I cannot figure how to break a Tie for 'exact' Time Matches, and adjust Rankings accordingly. Thanks to your advice, I have gotten this far, but now I am up against a brick wall. The OP and I have a lot of time invested in this Project, and would hate to have it abruptly end here. Kindly download the Attachment and refer to IDs 7 and 8. As always, thanks.
Do you have a rule that explains why one runner should be ranked above another if they have the same time?
In any case this might be your solution (look at the overallPosition field):
Expand|Select|Wrap|Line Numbers
SELECT T1.firstname, T1.surname, T1.Time, ((SELECT Count(*) FROM resultsdata WHERE resultsdata.Time < T1.Time OR (resultsdata.ID=T1.ID OR resultsdata.ID<T1.ID) ))
AS OverallPosition,
T1.gender, ((SELECT Count(*) FROM resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.gender = T1.gender)+1)
AS GenderPosition,
Category, ((SELECT Count(*) FROM resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.Category = T1.Category)+1)
I actually tried similar Logic with erroneous Results as listed below. I restricted the Output to only Time and Overall Position:
Expand|Select|Wrap|Line Numbers
SELECT T1.Time, ((SELECT Count(*) FROM resultsdata
WHERE resultsdata.Time < T1.Time OR (resultsdata.ID=T1.ID OR resultsdata.ID<T1.ID) ))
AS OverallPosition
FROM resultsdata AS T1
ORDER BY T1.Time;
Query OUTPUT:
Expand|Select|Wrap|Line Numbers
Time OverallPosition
5 :16:00 PM 1
6 :06:00 PM 2
6 :25:00 PM 3
6 :30:03 PM 11
7 :08:00 PM 5
7 :17:02 PM 13
7 :21:00 PM 7
7 :56:00 PM 8
8 :05:00 PM 10
8 :05:00 PM 9
9 :45:00 PM 11
9 :55:55 PM 13
9 :56:00 PM 13
Results using current Logic, excluding First and Last Names. Notice the equal Ranking for match on Time Slot of 8:05:00 PM for all Position Ranks(3) - Lines 10 and 11.
Expand|Select|Wrap|Line Numbers
Time OverallPosition gender GenderPosition Category CategoryPosition
There are only two scenarios in which we want to COUNT the records "above" the current row for T1. When the time is less than the current row. and when the time is equal to the current row but the ID is less than T1 row.
Here's the corrected code:
Expand|Select|Wrap|Line Numbers
SELECT T1.Time, ((SELECT Count(*) FROM resultsdata
WHERE resultsdata.Time < T1.Time OR (resultsdata.Time=T1.Time AND resultsdata.ID < T1.ID) )+1)
@Jerry and Rabbit: First of all, thanks a million for your help on this puzzling matter. As far as Ties go, this question should actually be directed to the Original Poster of this Thread, namely: neelsfer. He must define exactly what the Tie Breaker is in the event of equal Time Finishes, and this Tie Breaker must be Unique as I see it (cannot be a Race Registration Date). I know that we were trying to incorporate Milliseconds into the Timings in a Related Thread, probably for this exact scenario, but this is not that simple as Milliseconds are not integrated into Access Date/Time Fields. I'm too deeply into this, which is why I need some outside opinions. Do either one of you disagree with anything that was previously stated in this Post?
@neelsfer - Need you intervention at this point on the matter of Ties.
Not a problem, Jerry. The OP and I are virtually one and the same at this point! (LOL). I was actually thinking of making a [Milliseconds] Field {INTEGER} as the Tie Breaker. This Field would exist independently of the [Time] Field as a matter of necessity. The odds of 2 runners finishing at exactly the same Hour, Minute, Second, and Millisecond would be extremely small, but again we'll just have to wait for the OP. Thanks again.
Thxs guys. If i time a mountain biking event, then the chances are almost zero to have a tie as we capture the racenumbers as they pass the finish line.
The problem comes in with Road cycling (thats now what Mr "drugfree" Armstrong does).
They make use of what is called a "bunch or group time". If a group of riders all finish TOGETHER, they are all given the same finish time, but we manually identify the first 3 riders usually for prizes.
It could sometimes be 5-20 riders. The finish sequence is then 1,2,3,4,5,6...10, as we stop them in a "shoot" in their group finish sequence, but the same time is given to everybody then.
I use a button in my program that saves that bunch time in a textbox, and then i transfer that finish time to where the subform data is captured, and i just add the racenumbers afterwards.
In downhill mountain biking ( is going like a maniac down a mountain with no brakes) the riders start and finish on their own, and you may sometimes have a tie in seconds. This is where milliseconds may help.
Almost 90% of the races i do is normal mountainbiking, so lets rather assume there are no two riders with the same time in this report..
Hope that helps
I recently posted a message asking for help with
sequential numbers. I want to create an autonnumber
reference number that reverts back to 1 at the start of
each year. GlenAppleton gave me some...
Hi All:
I know this is simple, but I just can't seem to get there:
I need to sort a table by a text field (txtDescription), then assign
sequential numbers to the field SEQUENCE in table.
...
hi guys,
i was wondering if it is possible to extract selected words in a
field to be put in different fields automatically. Do i have to write
the code in vb?
This is what im trying to do....
Hello Everyone,
At work, employees have been using a spreadsheet that I am trying to
import into an access database to make some reports. The problem I'm
having is that in the spreadsheet, they...
hi ALL,
i want a different types query for practice the sql server.i am just start learning a sql server..so suggest to me a some websites where the query is available..
thnax in advanced
hi,
for a test i want to generate different random numbers between
different ranges in a single loop. I tried to solve in the following
way but it always profile same value. I am looking for...
Hi,
Is there a way to populate sequential numbers in a field using SQL or
VBA? If yes, how?
Assume the following is my existing table:
Fruit ID
Apply
Banana
I found an article that was somewhat like what I was trying to do. The article was titled:
SQL Query - Find block of sequential numbers
Here is the article...
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...