I have a query that I have created, and will be using it as the base of a report. One of the fields requires information from an aggregate query. However the aggregate query requires a parameter from this first query to provide meaningful data.....
Purpose:
When a little league pitcher throws over a certain number of pitches, he MUST miss at least one game, regardless of how many days there are before his team's next game. I need to create a report each day for the coaches that lists all the players who are not allowed to pitch that day, as we have had too many enforcement issues with the pitch count regulations. This report will eliminate the excuse that the coach didn't know or didn't understand.
Example
John, a member of the Yankees, throws 51 pitches on March 1. He must rest 2 days AND at least one game.
If his team plays on March 2 or March 3, John is eligible again on the 4th.
If his team doesn't play until March 10, John becomes eligible March 11 (teamNextGame + 1).
For this to work I simply need to know "What is the first game for [Yankees] after [March 1]?"
For his teammate Ryan who pitched on the 3rd, I would need to know the first game for the [Yankees] after [March 3]
I'm simply reporting the very next day he can pitch, I don't care if his team actually plays that day or not (you never know with rainouts, etc).
qryIneligiblePitcherReport
(the main query, I've edited out the fields that aren't important for this question)
gameDate (this value must be passed to the [lastPitched] parameter in qryNextGame)
TeamID (I need to know the next game for THIS team)
PlayerID
numberPitches (used in the calculated field)
teamNextGame (needs to get the result from the qryNextGame for the TeamID, based on the criteria in GameDate). This is the part I'm having problems with.
Eligible - A calculated field that returns the next date that the player may pitch. It uses a custom function with GameDate, numberPitches, and teamNextGame fields as parameters. The custom function has been tested by hard coding dates into teamNextGame and works properly. A simply criteria statement will weed out the eligible pitchers.
qryNextGame (Aggregate, returning a Minimum Value)
TeamID (Group By)
TeamName (Group By - here so I can see the team names during debugging, rather than autonumbers)
gameDate (Total : Min)
crit: gameDate (same as gameDate, but has criteria >[lastPitched], and is not displayed. Total row set to WHERE. If I hard code dates in the criteria, or pass them as parameters when I run the criteria, the correct dates are shown)
I'm not too concerned about how this is done. I know next to nothing about SQL statements, other than how to cut and paste them in. I'm ok with VBA, but would need some specific code to put it.
Start Free Trial
