Complex Aggregate Query

Posted on 2004-10-25
Medium Priority
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
  • 2

Expert Comment

ID: 12406447
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

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

This can't be done via sub-queries?

Accepted Solution

PFranks earned 2000 total points
ID: 12440319
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".



Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

594 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