Complex Aggregate Query

Posted on 2004-10-25
Last Modified: 2008-02-07
I currently use the following query to basically show how often we win/lose/tie based on the number of points we score.

Count(IIf((theirscore < ourscore), 1, Null)) AS WIN,
Count(IIf((theirscore > ourscore), 1, Null)) AS LOSS,
Count(IIf((theirscore = ourscore), 1, Null)) AS TIE,
FROM game g, team t
AND g.theirid = t.teamid
AND g.finalscore > 0
group by ourscore

I then paste this in Excel to calulate the win ratio for each line [WIN/(WIN+LOSS+TIE)].  I'd like to add that to the above query.  That's the easy part.  Now the hard part.  For each line item, I'd also like to include the win ratio for the line, the line above, and the line below all added together.  

Here's an example of what I want to see.

3            1       2          0         .333     .571  <=[(1+3)/(1+3+2+1+0+0)]
6            3       1          0         .750     .600  <=[(3+1+2)/(3+1+2+1+2+1+0+0+0)]
7            2       1          0         .667     .667  <=[(2+3+1)/(2+3+1+1+1+1+0+0+0)]
9            1       1          0         .500     .600  <=[(1+2)/(1+2+1+1+0+0)]

Normally I'd loop through the result set to get this info, but I'm using an access DB via the web, so it's not quite that easy.  I know I'm forgetting some details, so if you have any questions, let me know.
Question by:snekse
    LVL 1

    Expert Comment

    To my knowledge this can't be done unless you know the ID of the previous and next record already.

    If this is the case you would have to create a function that queries the values from the specified records in your query, adds them together returns the answer you want for each row in your query.

    If you want more information than this I'll try to prduce a function for you.



    Author Comment

    What do you mean by "create a function"?  In access?  Or just in the SQL statement?

    This can't be done via sub-queries?
    LVL 1

    Accepted Solution

    All this assumes that you know the record IDs in advance i.e. record before is (say) ID - 1 and the record after is ID + 1. The function become more complex if it isn't.

    You will need to create a function in access (VBA) that is called from within a SQL statement


    SELECT *, fnGRPAVG([ScoreID] -1, [ScoreID] + 1) AS GRPAVG FROM game g

    This will call the fnGRPAVG function that you will have written to which will calculate the GRPAVG figure for each record.

    NOTE: This will be a complex function that will contain 2 queries to retrieve that data for the record before and the record after. This information will then be used to calculate the GRPAVG amount and get passed back to your original query.

    This would be an extremely difficult thing to do with sub queries as you need more than one piece of information from two other records. It will also be difficult to handle the first record where there is no "before" and the last where there is no "after".



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    877 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now