Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

609 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