In expression builder- Sum Top 3

I don't think there is.
If you only showed the top three (using the TOP predicate in the underlying SQL) then that would work for you but the report is an Access object rather than a Jet SQL object. I'm fairly sure it can't be done there. Sorry.

Access 2000, I am trying to add a "Summed" total to a report (in a grouped footer section). But I only want to sum the top 3 items. I know how to write this in sql but it seems I can't get a to a sql expression box from the report view/text box. I can get to the text box properties but that only allows me to open up the expression builder. So is there a way to sum the top 3 in expression builder?

Is there way to nest two different functions in 1 expression?

Thanks for your help!

I'm with NeoPa, in that I also don't think it can be done in the manner which you describe. It probably can be done by setting the Control Source of a Text Box in the Report's Group Footer to a Function which receives each Grouping Field as an Argument and performs the neccessary summations on the Top 3 items in each Group. I really would like to stress the word probably here. If you wanted to attempt this approach, I would need much more information.

Well that's not what I was hoping to hear. I am trying to create a database that stores the results of a progressive marathon. There are 8) 4 mile lengths and a 10 mile length. The marathon results are the 3 fastest times of the 8 (or how ever many they did, they don't have to do all 8 legs) and the 10 mile leg added together.

I have been trying to figure out what is the easiest way to do this. They want several types of reports such as an individual runner with all of their race legs and then their marathon total. Can I create a sql query that sums the top 3 and adds the 10 mile race and then build a report from that query? I would think yes but I am not sure how to write the query that would sum the top three for each individual runner and not just the top3 of ALL the runners. Any suggestions there?

My tables:

Runner:
BibNum (number) linked to race bibnum
FirstName
LastName
Sex
Age

Race:
BibNum (number)
RaceNum (number)
Minutes (number)
Seconds (number)

They also want to create a report that lists all the runners by sex then age for each individual race and over all marathon scores. I have attempted to do this numerous ways and in a couple different languages and this stupid sum the top 3 is the killer.

Any suggestions for me?

Thanks!

Well that's not what I was hoping to hear. I am trying to create a database that stores the results of a progressive marathon. There are 8) 4 mile lengths and a 10 mile length. The marathon results are the 3 fastest times of the 8 (or how ever many they did, they don't have to do all 8 legs) and the 10 mile leg added together.

I have been trying to figure out what is the easiest way to do this. They want several types of reports such as an individual runner with all of their race legs and then their marathon total. Can I create a sql query that sums the top 3 and adds the 10 mile race and then build a report from that query? I would think yes but I am not sure how to write the query that would sum the top three for each individual runner and not just the top3 of ALL the runners. Any suggestions there?

My tables:

Runner:
BibNum (number) linked to race bibnum
FirstName
LastName
Sex
Age

Race:
BibNum (number)
RaceNum (number)
Minutes (number)
Seconds (number)

They also want to create a report that lists all the runners by sex then age for each individual race and over all marathon scores. I have attempted to do this numerous ways and in a couple different languages and this stupid sum the top 3 is the killer.

Any suggestions for me?

Thanks!

This is all I had time to experiment with, but hopefully it will point you in the right direction. The following Parameter Query will return the 3 fastest times, (Total Seconds), for a specific individual for any number of 4-mile races. In addition to the 3 fastest times, it also outputs the BibNum, RaceNum, FirstName, LastName, and Sex. This should, at least, be a good starting point. I'm really not sure if what you are requesting is even possible. Good luck and keep in touch.

What, in the Race table, indicates a 10 mile race?

Race Number 10 will indicate the 10 mile race. So in the form when entering time I have the following race number options: 1,2,3,4,5,6,7,8 and 10.

I appreciate all of your help in this! I have less than one month to get this working. Not only do the races start mid august, but I am using this as my graduate project. So me graduating is depending on this. It didn't sound so hard in the beginning but now that I have been working on this for over a month (i took a detour and started writing it in asp/c# which I sort of have working, but I think I made it too complicated.) Anyways I thought I would come full circle and try it in a simpler version again in just access. I'm driving myself crazy because i keep running into unforseen problems and have to change my plan.

Again thanks for your help!!!

NeoPa/ADezii,
I may be wrong or misunderstand what is being asked....but why can't DSum be used to obtain totals over the domains for which the Sum can't be computed? I have seen sum, and dsum as alias in the same select statement.

I don't think there is.
If you only showed the top three (using the TOP predicate in the underlying SQL) then that would work for you but the report is an Access object rather than a Jet SQL object. I'm fairly sure it can't be done there. Sorry.

Hello NeoPa:
The more I look at this, the more I agree with you in that this can't be accomplished in a Report Footer via SQL. The only logical solution that I see is to:

1. Create a Multi-Line Text Box in the Report Footer.
2. Set the Control Source of this Text Box to a Public Function, say fCalculateMarathonTimes().
3. Within this Function, the 3 fastest times for a 4 mile race for each unique individual are summed and the result of their 10 mile race is added on to the summation.
4. The Function will then return a concatenated string listing of all individuals with their accumulated times, (3 fastest 4-mile + 10 mile), such as:
   
   Expand|Select|Wrap|Line Numbers

   1. George Washington 23 mins. 34 secs.
   2. Theodore Roosevelt 31 mins. 17 secs.
   3. John Davidson 27 mins. 53 secs.
   4. ..
5. I am also referring the OP to this post, but I wanted to get your expert opinion first. I also think that this approach should be implemented after all other possibilities have been exhausted. This may be the only way out since the SQL route in combination with a Report footer seems futile.
6. BTW, this may be a little more difficult than I realize, in which case there will be no implementation at all (LOL).
7. Please get back to me on this.

Race Number 10 will indicate the 10 mile race. So in the form when entering time I have the following race number options: 1,2,3,4,5,6,7,8 and 10.

I appreciate all of your help in this! I have less than one month to get this working. Not only do the races start mid august, but I am using this as my graduate project. So me graduating is depending on this. It didn't sound so hard in the beginning but now that I have been working on this for over a month (i took a detour and started writing it in asp/c# which I sort of have working, but I think I made it too complicated.) Anyways I thought I would come full circle and try it in a simpler version again in just access. I'm driving myself crazy because i keep running into unforseen problems and have to change my plan.

Again thanks for your help!!!

Please refer to my Reply to NeoPa's earlier Post. One way or another we'll try to get this to work for you - we love these kinds of challenges!

Dezii's Reply to Forum Leader NeoPa

There seems to be a little trouble with the Hyperlink, so I'm also posting the entire text.
Hello NeoPa:
The more I look at this, the more I agree with you in that this can't be accomplished in a Report Footer via SQL. The only logical solution that I see is to:

1. Create a Multi-Line Text Box in the Report Footer.
2. Set the Control Source of this Text Box to a Public Function, say fCalculateMarathonTimes().
3. Within this Function, the 3 fastest times for a 4 mile race for each unique individual are summed and the result of their 10 mile race is added on to the summation.
4. The Function will then return a concatenated string listing of all individuals with their accumulated times, (3 fastest 4-mile + 10 mile), such as:
   
   Expand|Select|Wrap|Line Numbers

   1. George Washington 23 mins. 34 secs.
   2. Theodore Roosevelt 31 mins. 17 secs.
   3. John Davidson 27 mins. 53 secs.
   4. ..
5. I am also referring the OP to this post, but I wanted to get your expert opinion first. I also think that this approach should be implemented after all other possibilities have been exhausted. This may be the only way out since the SQL route in combination with a Report footer seems futile.
6. BTW, this may be a little more difficult than I realize, in which case there will be no implementation at all (LOL).
7. Please get back to me on this.

NeoPa/ADezii,
I may be wrong or misunderstand what is being asked....but why can't DSum be used to obtain totals over the domains for which the Sum can't be computed? I have seen sum, and dsum as alias in the same select statement.

DSum(), has two problems :

1. How to tell it which three to select for summing.
2. Efficiency. SQL has a built-in optimiser. Essentially you are running a mini-query every time you run the DSum(). That would be for every record in the outer query. The overhead when using Sum() on a query linked into the main query using the FROM clause is a small fraction of this, except for very low record queries.

PS. ADezii, I haven't forgotten your posts, but will need to find a little time later to give them the attention they deserve.

Race Number 10 will indicate the 10 mile race. So in the form when entering time I have the following race number options: 1,2,3,4,5,6,7,8 and 10.

Can you confirm that the Race table will only have the nine races in it then (always)?

Well that's not what I was hoping to hear. I am trying to create a database that stores the results of a progressive marathon. There are 8) 4 mile lengths and a 10 mile length. The marathon results are the 3 fastest times of the 8 (or how ever many they did, they don't have to do all 8 legs) and the 10 mile leg added together.

I have been trying to figure out what is the easiest way to do this. They want several types of reports such as an individual runner with all of their race legs and then their marathon total. Can I create a sql query that sums the top 3 and adds the 10 mile race and then build a report from that query? I would think yes but I am not sure how to write the query that would sum the top three for each individual runner and not just the top3 of ALL the runners. Any suggestions there?

My tables:

Runner:
BibNum (number) linked to race bibnum
FirstName
LastName
Sex
Age

Race:
BibNum (number)
RaceNum (number)
Minutes (number)
Seconds (number)

They also want to create a report that lists all the runners by sex then age for each individual race and over all marathon scores. I have attempted to do this numerous ways and in a couple different languages and this stupid sum the top 3 is the killer.

Any suggestions for me?

Thanks!

I think I have found a viable, and efficient, solution to your dilemma. The solution involves the creation of an Unbound Sub-Report in the Group Footer Section of the Main Report. The underlying Data Source for this Sub-Form would be a Table whose Records would be dynamically created prior to the actual Report opening. A Public Function would calculate the sum of the 3 fastest 4-mile times then add the 10-mile time for each runner. Once this data is calculated, it would write it to the previously mentioned Table and sort the data with the fastest overall times listed first. When the Main Report opens, this Unbound, independent Sub-Report would list all the runners by First and Last Names along with their calculated Marathon Times with the fastest time being listed first. All logic and calculations are performed within the Public Function. If you wish to go ahead with this approach, please let me know, and please be 100% sure that this is want you want to do since this will involve several hours to actually get this logic in code. I don't mind spending the time, but I would like to be sure that the end result is what you actually want and that it will be used. If you do agree to this, there is additional information that I would need along with sample Marathon data. I don't need a decision right away, as a matter of fact someone else may come up with a better solution, but if you do decide to go this route, I'm available to assist you.

Still haven't forgotten the replies due to you ADezii and still pushed for time ATM.
What I'm looking at for this though, is a solution in SQL that a report can be built on. Just waiting for OP's confirmation.

Still haven't forgotten the replies due to you ADezii and still pushed for time ATM.
What I'm looking at for this though, is a solution in SQL that a report can be built on. Just waiting for OP's confirmation.

I completely understand, NeoPa. Interesting problem, the thorn in the side is the TOP 3 fastest times per runner and not overall, as well as implementing the solution in a Report Footer. I'm pretty sure I can devise a solution as described in the previous Post but I'm just laying back until someone comes up with a better solution or the OP wants to take this approach. To your credit, I figure that if you can't come up with an workable SQL approach to this problem, one simply does not exist!

I completely understand, NeoPa. Interesting problem, the thorn in the side is the TOP 3 fastest times per runner and not overall, as well as implementing the solution in a Report Footer. I'm pretty sure I can devise a solution as described in the previous Post but I'm just laying back until someone comes up with a better solution or the OP wants to take this approach. To your credit, I figure that if you can't come up with an workable SQL approach to this problem, one simply does not exist!

You're right ADezii. I tried and failed :(
I sort of have a lingering suspicion that Rabbit may know of a way but my experience with subqueries is in the FROM clause, which can't work due to the TOP 3 predicate as you say. I could do a single one without using the subquery in the FROM clause but in the SELECT clause instead, but if it is possible to do this all in the select clause it is very complicated and I've done enough on this one. Over an hour on one thread is too much.
I'd be interested in a SQL solution so I'll continue to monitor. Even a code solution wouldn't be too straightforward as the recordset needs to be able to drive a report.
Anyway, enough for now. I have work in the morning.

PS. Thanks for the vote of confidence, but Rabbit's found answers where I've failed before, although I like to think I know my way around SQL pretty well now.

I appreciate everyones help and I apologize for it taking me a couple of days to get back to you. On top of this I have 2 other classes and a full time job so the past two days I have been working on other things. Since this sounds so complicated and I do have it "working" in ASP/C# let me talk to the running clubs director and see which way she would prefer it, a stand alone access database or a website that everyone can get to (that being the good and the bad thing about it.) It is the security that has me stumped in the c#/asp program and has me doubting if that was the best way to go.

As for NeoPa's question: Can you confirm that the Race table will only have the nine races in it then (always)?

This is all the table will have. This will only be 8) 4 mile legs and 1) 10 mile leg.

Again, thank you all for your help. I will try to get together with her today or tomorrow and I will post her decision.

Thanks for the update.
However, I'd proceeded along those lines already and petered out after an hour or two. If anything comes to me or I "see" a way through, then I'll post it, but please continue to breathe normally in the mean time ;)
I'm sorry to let you down. I hate it when that happens, but there it is - at least for now.

No worries, the race director loved the website... I just have to figure out the best way to do security...