Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    totals query (2002)

    I have a totals query that adds up individual scores and lists the totals for each team. How can I work with these totals in a form. What I want to do is look at the totals and if two totals are the same I want to be able to indicate which total should be listed first in a report.
    My problem is that if you base a form on the totals query you can't do anything with the numbers like make one .5 less so the order of the totals will be different.

    Any ideas.

    Thanks

    Paul

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: totals query (2002)

    Since totals are calculated values, you cannot edit them.

    Can you define explicit criteria to decide how to order the result? If so, you can probably create a query based on the totals query and specify the desired sort order there, for example first by score, then by team name or something like that.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: totals query (2002)

    Hans
    Could you explain a little more. My totals query has two fields - teams and teamscores. I tried creating another query based on the totals query and I'm trying to add another field that I can enter some type of other data that I can use to sort. It won't let me. Hoe can I get another field to sort on?

    Thanks
    Paul

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: totals query (2002)

    Do you have some kind of rule to decide which of two teams with the same score should be listed first, or do you want to decide on an ad-hoc basis?

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: totals query (2002)

    No rule. A decision will be made if two teams end up with the same score. I thought I could put a checkbox and that would indicate which team goes first.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: totals query (2002)

    <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

    You could add a Yes/No fieldto the table that lists the teams. Create a new query based on the teams table and the totals query, joined on the appropriate field (TeamID or something like that). Add the TeamID or Team Name, the score and the Yes/No field to the query design grid. Sort on the score and on the Yes/No field. Save this query.

    Create a form based on the new query, a continuous form is probably best. The Yes/No field will be displayed as a check box by default. You may want to run an update query to clear the Yes/No field when opening the form, so that you start with a clean slate each time. You can then tick the check box where appropriate.

    Set the record source of the report to the new query, and use the Sorting and Grouping window to sort on the score first, then on the Yes/No field.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •