Running a query that runs two seperate totals from the same column displayed side by side

[jucooper1]

I am a football coach and I am trying to use MS Access for all of my scouting and analysis. For other coaches out there, we do use Hudl, but I am looking for something more powerful, and simpler once set up. Anyway, this is what I am trying to do. I have one table, which is all of the data exported from Hudl (our video software program), edited in excel, then imported into access. Everything is in one table. The specific field I am trying to work with are Week, Opponent, Type (run/pass). What I want to do is run a query that displays The Opponent, with a column (Run) that counts the number of runs from the Type field, and another column (Pass) that counts the number of passes. The Week field is used to sort, but does not need to be displayed. This is what I want it to look like ideally:



Opponent Run Pass RunPct PassPct
School1 37 13 74% 26%
(I keep trying to space this correctly, but it won't let me. Each number should obviously be under its respective column)

I can get the query to return the result of how many runs each game, or how many passes. I can also get them to display both, in separate rows, but I want it to look like above so I can then total the columns to keep a running count as the season goes on.

Any suggestions? Not new to Access, but not experienced either.

[pbaldy]

Something like:

SELECT Opponent, Sum(IIf(Type = "Run", 1, 0)) As TotalRun, Sum(IIf(Type = "Pass", 1, 0)) As TotalPass
FROM TableName
GROUP BY Opponent

if that works, you can probably create something that comes up with the percentages.

[jucooper1]

That worked! I'll work on the %s and see what I can come up with, but that is less important. Thanks!

[pbaldy]

No problem. I don't think the percentages will be hard, post back if you get stuck.

[jucooper1]

After showing me how to do the sum, I was able to figure out the percentages. Thanks!

[pbaldy]

Happy to help!