Need help with doing something other than simple query

[baseball]

Access 2007 on Win 7 64-bit

Want to analyze baseball data from the point of view of results with runners on base by teams and individuals. I know how to do this but there are 30 teams and many batters. Also, there are eight states of runners on base (no one on base; a runner on 1st, 2nd, or 3rd; two runners on 12/13/23; runners on all three bases. This is time consuming for one season and I have decades of data to analyze. Is there some way of streamlining the process so I don't have to keep rewriting queries. This would all be very simple to do in Excel but recent seasons have 1.8+ million records each to process.

One thing that could be a drawback is that the year and the home team's identifier need to be split out from the [gameID], as in" Home: Left([gameID],3) and Year: Val(Mid([gameID],4,4)). I'm not sure this fits in with Crosstabs, Pivot Tables, and things like that.

Basically, I'd like a query where I could click on a field and choose a Year, a team (or all teams), the various base runner states and get the number of occurrences for each state and the sum of the [Runs Batted In] for each state. Is this feasible?

While SQL is not a strong suit, I think I know enough to expand upon a basic solution.

 

[ranman256]

No, you don't have to rewrite queries.
either make a parameter query that asks you the date
or
use a form so the query reads off a text box... Select * from table where [runDate]= forms!frmMain!txtDate

 

[baseball]

Thanks. I didn't know about parameter queries. They could simplify some of the things I do but I think that I'll also look into forms with drop-down boxes, which I haven't tried.