Football Coaching Query Question

[maldrich]

I have a lot of help on some reports but I keep running into a problem when dealing with ranges.
A football field is 100 yards long. It is broken into field zones by yard lines.
For example, -1 to -10, -11 to -25, -26 to -49, 50 to 26, 25 to 11, 10 to 1.
I am trying to find some percentages for those field zones.

I know you can create an IIf statement that could give it another value, but how can I create a query asking for all plays or values when the field position lies within a certain range (ie. from the -1 to -10)?

If anyone has any ideas, I would appreciate it. I am a novice Access user and we are trying to use Access for our recruiting database and our Scouting database.

Thanks in advance.

-Mike
maldrich67@yahoo.com

 

[mresann]

Taking a break from my current rush job

Assign the following values to zones:

-1 to -10 = 1
-11 to -25 = 2
-26 to -49 = 3
50 to 26 = 4
25 to 11 = 5
10 to 1 = 6

Use this Iif Statement (yes, that is what you use) in a Field on the query, assuming that you have a field named Yardline that indicates the current field position:

Iif(Yardline <=-1 AND Yardline >= -1, 1, _
Iif(Yardline <=-11 AND Yardline >= -25, 2, _
Iif(Yardline <=-26 AND Yardline >= -49, 3, _
Iif(Yardline <=50 AND Yardline >= 26, 4, _
Iif(Yardline <=25 AND Yardline >= 11, 5, _
Iif(Yardline <=10 AND Yardline >= 1, 6, 0))))))

(NOTE: a value of zero is applied if the yardage does not comply)

You can also use the SWITCH function which may be faster. Look that up in Access Help

 

[ScottGem]

I have a lot of help on some reports but I keep running into a problem when dealing with ranges.
A football field is 100 yards long. It is broken into field zones by yard lines.
For example, -1 to -10, -11 to -25, -26 to -49, 50 to 26, 25 to 11, 10 to 1.
I am trying to find some percentages for those field zones.

I know you can create an IIf statement that could give it another value, but how can I create a query asking for all plays or values when the field position lies within a certain range (ie. from the -1 to -10)?

If anyone has any ideas, I would appreciate it. I am a novice Access user and we are trying to use Access for our recruiting database and our Scouting database.

Thanks in advance.

-Mike
maldrich67@yahoo.com

With the limited number of ranges, Switch might be best. But you might also use a Custom function to return a Range that can be used for grouping:

PUBLIC Function YardRange(intYard as Integer)

SELECT Case intYard
Case BETWEEN (-1) - (-10)
YardRange = "-1 to -10"

Case BETWEEN (-11) - (-25) 
Yard Range = "-11 to -25"

etc
End Select
End Function

or you can assign a numeric value and have a lookup table to show the ranges.

 

[maldrich]

Scott-

Thanks for the help on the IIf function.
I did look up the Switch function and it does look quicker.
Would I use the Switch function in the field on a query?

 

[maldrich]

Scott-

Here is the IIf statement I tried to apply:

IIf(FP <=-1 AND FP >= -10, 1, _
IIf(FP <=-11 AND FP >= -24, 2, _
IIf(FP <=25 AND FP >= 11, 3, _
IIf(FP <=10 AND FP >= 1, 4, 0))))

I entered the above statement exactly as you see it and I get an error that the expression contains an invalid syntax, but I can't figure out what it is.

I also tried the Switch function and this is what I put in the field on the query:

Expr1: Switch([FP]>="1" And [FP]<="10","1",[FP]>="11" And [FP]<="25","2",[FP]>="-24" And [FP]<="-11","3",[FP]>="-10" And [FP]<="-1","4")

When I try to run the query it says that there is a syntax (comma) error. But I don't know what it is.

I am currently coaching arena/indoor football, so we only have a 50 yard field. So I am testing my Access stuff with this team.

If you have any ideas, please let me know when you get a chance.

Thanks- Mike

 

[ScottGem]

The Switch functions looks OK, I tried it and it worked. But why are using Text fields for this data? If you are using number fields then you don't need the quotes. I would try using fewer parameters first. Try it first with only 2 tests, then keep adding.

 

[maldrich]

Scott-
As you know, I am quite the novice, so I was unaware of the difference with the quotes.
Also, I am building my Query in design view. I am putting the Switch statement into a Field, then dictating the table to pull it off of. Is that the right place for this statement, or should it be in SQL view? If so, whoa.

 

[raskew]

Hi -

Take a look at this Switch() function to see if it might be helpful:

Function Zoneage(Posn As Integer) As Integer
   If Posn < -49 Or Posn > 50 Then
      Exit Function
   Else
      Zoneage = Switch(Posn < -25, 3, Posn < -10, 2, Posn < 0, 1, Posn > 25, 4, Posn > 10, 5, True, 6)
   End If
End Function
'-1 to -10 = 1
'-11 to -25 = 2
'-26 to -49 = 3
'50 to 26 = 4
'25 to 11 = 5
'10 to 1 = 6

Best wishes,

Bob

 

[maldrich]

Bob-

Thank you for your help on the code. You may have seen my other problem from earlier in my posts, but I am a novice when it comes to Access. So when you show me that code, the only place I can find to put it is in VB. And the only place I know how to get to it is through the forms page.

How do I actually take the code you gave me, and make it work and apply it?
Very confused. Sorry for being so dense.
-Mike

 

[maldrich]

Scott-
I put the Switch function in the "Criteria" box under the proper field heading and took out the quotations because it's a number.
Now I can get the query to actually run, which is a lot farther than I have been. But now how do I call on the Values that I assigned each field zone?

 

[ScottGem]

You should be enterring the Switch function on the Field row in a new column in Query design mode. You should show the table with the FP field in the table pane.

As for Bob's function you would enter that in a global module. Then call it the same way you would any function in your query or elsewhere.

As for the quotes, then are only necessary for text fields, not numbers.