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]

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.

[ADezii]

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.

[emajka21]

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!

[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!

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.

SELECT TOP 3 tblRunner.BibNum, RaceNum, FirstName, LastName, Sex, ([Minutes]*60+[Seconds]) AS Total_Time_In_Secs
FROM tblRunner INNER JOIN tblRace ON tblRunner.BibNum = tblRace.BibNum
GROUP BY tblRunner.BibNum, tblRace.RaceNum, FirstName, LastName, Sex, ([Minutes]*60+[Seconds])
HAVING LastName=[Enter Last Name]
ORDER BY tblRunner.BibNum, ([Minutes]*60+[Seconds]);

[NeoPa]

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

[emajka21]

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!!!

[puppydogbuddy]

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.

[ADezii]

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 fCalculateMarat honTimes().
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:
   

   George Washington 23 mins. 34 secs.
   Theodore Roosevelt 31 mins. 17 secs.
   John Davidson 27 mins. 53 secs.
   ..

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.

[ADezii]

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 fCalculateMarat honTimes().
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:
   

   George Washington 23 mins. 34 secs.
   Theodore Roosevelt 31 mins. 17 secs.
   John Davidson 27 mins. 53 secs.
   ..

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.