1 2 Previous Next 29 Replies Latest reply on Jan 28, 2012 5:26 AM by tomo17

    Running totals for sports carnival

    tomo17

      Title

      Running totals for sports carnival

      Post

      Trying to get a carnival database to have running point total for each team at the bottom of a results layout.  Not quite sure how to go about it.  I have tried using sub summary on the layout or trailing summary set ups but not working.

      Here is my set up

      Table one - Athletes

      Fields - Name, Age, Gender, Athlete ID_Name, Team

      Table two - Event results

      Fields - Athlete iD_Name, House, Place, Points, Performance, Total Points (summary of points)

      The layout for results collection comes from a third table which is the event program and has event information and then uses a portal to show athletes participating and performances

      I am able to get these total points by team on another report layout but want it at the bottom of the results sheet as well so that we have an ongoing view of points.

      Do i need to/can i add in another portal?  Should i use lookup fields?  To be honest i am just stuck on where to begin to do this!!

      Many thanks for advice

      Craig

        • 1. Re: Running totals for sports carnival
          IT_User

          For the sub summaries, do you have summary fields that can be used on them?

          • 2. Re: Running totals for sports carnival
            philmodjunk

            Didn't you post this once before? Did the responses you got to your original post work for you?

            • 3. Re: Running totals for sports carnival
              IT_User

              It's the same post. No one replied except for me, hence why it got bumped up again.

              • 4. Re: Running totals for sports carnival
                philmodjunk

                Strange, I distinctly remember working on a very long post on this topic. Maybe I got pulled away from it by my real job before I could post it.

                In the future, you can always reply to your own post to "bump" it back up to the recent issues list unless the original post is dated more than 3 months old.

                You appear to have these tables and relationships:

                Athletes----<Event Results>-----Event Program

                Athletes::Athlete ID_Name = Event Results::Athlete ID_Name
                Event Program::eventID = Event Results::EventID

                "The layout for results collection comes from a third table which is the event program and has event information and then uses a portal to show athletes participating and performances"

                I would guess that you have a portal to Event Results on a layout based on Event Program. Is that correct?

                While a serial number is a better option than an athlete's name, that should work for working with events and results for individual athletes on an event by event basis. But you want to calculate a team total located below this portal. Is that a team total for the current event (more than one team member competes in same event) or is this an overall team total of all team results for the Carnival?

                • 5. Re: Running totals for sports carnival
                  tomo17

                  Thanks for the replies - i had to put this on hold for a little while but back on it now.

                  Phil - i do not recall seeing your response previously - you usually have the answers to my problems so i would like to think that i would have responded.

                  I am trying to get a team total for all events that have had results entered. I have tried the sub summaries as IT_User has suggested but only gotten event totals.

                  Your are correct in that my portal of results is on a event program layout.

                  i have serial numbers for each athlete as well if that affects the approach that i should take.

                  • 6. Re: Running totals for sports carnival
                    philmodjunk

                    Serial numbers just make for more reliable relationships as a general rule. Your athelete names may not be unique.

                    You'll need to add a means of tracking totals by team to your existing set up in order to get such a team overall total. Doing so from your event table based layout complicates this a bit as each record identifies a specific event and that doesn't really connect to whose on what team.

                    It looks to me like you need another table for teams where you have one record per team that you can link to your atheletes table. This table can compute an overall team total for each team. Then a portal to this table using the cartesian join operator (x) can display the list of teams with their overall totals.

                    Your Teams table would have at least 3 fields: TeamID (serialnumber), TeamName, cScore (calculation field)

                    You'd link this table to Athletes by TeamID (not team name). That way last minute team name changes won't mess you up.

                    cScore would be defined as: Sum ( Event Results::Points )

                    Last step is to make a second occurrence of Teams by selecting it and then clicking the button with two green plus signs (double click the new occurrence to bring up a dialog where you can change its name). Name it "AllTeams" and link it to Event like this:

                    Event::Anyfield X AllTeams::Anyfield  (You can select any field you want in each occurrence and the relationship still functions the same).

                    Now you can add a portal to AllTeams with the TeamName and cScore fields to keep an up to date list of overall scores for all your teams.

                    • 7. Re: Running totals for sports carnival
                      tomo17

                      Thanks Phil 

                      I have a teams table with the headings as you indicated and have added the cScore field

                      If you could clarify a couple of points please

                      - does this take into account that some events are teams and some events are individual (the team points dont seem to be included at this point)

                      - will i be able to place this portal onto the events entry page to give me a live overall score as each place is entered rather than going to a separate score layout (which i have already) or is this not 'doable'

                      Thanks, 

                      craig

                      • 8. Re: Running totals for sports carnival
                        philmodjunk

                        The purpose of the portal setup is to give you a live up to date score for each team.

                        From your descriptions, however, I assumed that you would be recording results for each individual athlete not for a team result such as a relay race. Recording that may take a change in the system, or you might be able to use what you have and enter "team" places by logging under one player (the team captain?) from each team.

                        • 9. Re: Running totals for sports carnival
                          tomo17

                          Coming back to this after a holiday so apologies for delay iin replying.  Phils suggested solution worked a treat for individual events score.  I have now hit a brick wall as you believed i would with trying to manage this for team events as well.  My solution would be to:

                          Create a seperate events program for the team events then have cScore = Events::Points + Team Events::Points

                           

                          I did consider having each House as an athlete in each year but this created problems when i reported on Highest points scorers for each year (champion Athlete Report)

                           

                          Can anyone make a suggestion that is more efficient?

                           

                          Many Thanks

                          • 10. Re: Running totals for sports carnival
                            philmodjunk

                            In you use a value in a field to identify all the "house" athletes, your highest point report can use a find to exclude those athletes from your report.

                            • 11. Re: Running totals for sports carnival
                              tomo17

                              I think i understand what you are suggesting but will this make it too cumbersome as the way i see this i will need to have all four houses entered for each year level and each gender to work (over 40 extra records) Each event result entry has a drop down list of athletes that fit the age/gender fields. is there a way that i can just set it up to select the house irrespective of the year group or gender?

                              Or do i need to create a new table for team event results.  I would then assume that i would have the evnt program as the parent table and the two results entry tables to be the child tables.

                               

                              Thanks again for your advice/solutions.

                               


                              Craig

                              • 12. Re: Running totals for sports carnival
                                philmodjunk

                                Can't really say from here. There are too many unknown factors. Either approach can work, but it'd take a pretty detailed look at how you need to support the Carnival and how you've structured your database. If you have the relevant data on each house already entered into the system in another table, you should be able to use a script to generate these records. That doesn't mean this is the best way to do this however.

                                • 13. Re: Running totals for sports carnival
                                  tomo17

                                  Can i have a parent table (events program) and two child tables (athlete events and team events) where entry in the parent will trigger a create new record in the child tables based on an event type field in the parent program?   ie event is an individual so creates a record in the athlete table or event is team so creates a record in the team events table.  the child tables would be linked to the parent table by event number ID.  From there i can then replicate what is already happening on the athlete events side of the database.

                                  or alternatively would using a portal be a better option in your opinion - which leads me back to the original problem of having to have 40 records.

                                  • 14. Re: Running totals for sports carnival
                                    tomo17

                                    I have set up so that i now have a team event entry table similar to the individual athlete results tabel which has given me an adequate solution.  I sthere a way that i can modify your solution for the points score to now include team points as well.  At present i am still only getting the individual points scores.

                                     

                                    Thanks

                                    1 2 Previous Next