In expression builder- Sum Top 3

[emajka21]

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!

[NeoPa]

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.

[NeoPa]

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)?

[ADezii]

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.

[NeoPa]

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.

[ADezii]

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!

[NeoPa]

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.

[emajka21]

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.

[NeoPa]

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.

[emajka21]

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