If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
The IF Staement if suitable to use
we have about 100 different Players, all held in a Table.
i would like to make a Query with an IF statement if thats possible. In my database i have various Tables; Players, Course, Game, ect. and would like to make a Report with the following. each player has a different 'Value' say, for example 15.5. If the player Gender is 'male' and is playing on the 'North' course then apply the formula as *1.202. If is female then apply a different Formula, say *1.502. if playing on the 'South' course and is a 'male' apply a slightly different formula say *1.225 and if is 'female' a *1.236 formula. having worked all that out, to apply a Discount or weighting Value held in the GameName (in GameTable). hope this is not too complicated! i have placed this question with ED in the General Listing, but thought it may be better placed here Michael |
#2
|
|||
|
|||
The IF Staement if suitable to use
You could use a series of nested IIf statements, although it can get a
little harry keeping track of all of the conditions! For the 2 dimensions you've got below (Gender and Course), you'd use something like: [Value] * IIf([Gender] = 'Male', IIf([Course] = 'North', 1.202, 1.225), IIf([Course] = 'North', 1.502, 1.236)) Another option, of course, is to create your own function, and call that function in your query. Again, for the dimensions above, you'd have something like: Function CalculateMultiplier(Gender As Variant, Course As Variant) As Single If IsNull(Gender) Or IsNull(Course) Then CalculateMultiplier = 1 Else If Gender = "Male" Then If Course = "North" Then CalculateMultiplier = 1.202 Else CalculateMultiplier = 1.225 End If Else If Course = "North" Then CalculateMultiplier = 1.502 Else CalculateMultiplier = 1.236 End If End If End If End Function (I used Variant as the data type for the parameters just in case there's a chance that the field might be Null) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Michael to Ed" wrote in message ... we have about 100 different Players, all held in a Table. i would like to make a Query with an IF statement if thats possible. In my database i have various Tables; Players, Course, Game, ect. and would like to make a Report with the following. each player has a different 'Value' say, for example 15.5. If the player Gender is 'male' and is playing on the 'North' course then apply the formula as *1.202. If is female then apply a different Formula, say *1.502. if playing on the 'South' course and is a 'male' apply a slightly different formula say *1.225 and if is 'female' a *1.236 formula. having worked all that out, to apply a Discount or weighting Value held in the GameName (in GameTable). hope this is not too complicated! i have placed this question with ED in the General Listing, but thought it may be better placed here Michael |
#3
|
|||
|
|||
The IF Staement if suitable to use
Many thanks
i will work on that over the next few days "Douglas J. Steele" wrote: You could use a series of nested IIf statements, although it can get a little harry keeping track of all of the conditions! For the 2 dimensions you've got below (Gender and Course), you'd use something like: [Value] * IIf([Gender] = 'Male', IIf([Course] = 'North', 1.202, 1.225), IIf([Course] = 'North', 1.502, 1.236)) Another option, of course, is to create your own function, and call that function in your query. Again, for the dimensions above, you'd have something like: Function CalculateMultiplier(Gender As Variant, Course As Variant) As Single If IsNull(Gender) Or IsNull(Course) Then CalculateMultiplier = 1 Else If Gender = "Male" Then If Course = "North" Then CalculateMultiplier = 1.202 Else CalculateMultiplier = 1.225 End If Else If Course = "North" Then CalculateMultiplier = 1.502 Else CalculateMultiplier = 1.236 End If End If End If End Function (I used Variant as the data type for the parameters just in case there's a chance that the field might be Null) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Michael to Ed" wrote in message ... we have about 100 different Players, all held in a Table. i would like to make a Query with an IF statement if thats possible. In my database i have various Tables; Players, Course, Game, ect. and would like to make a Report with the following. each player has a different 'Value' say, for example 15.5. If the player Gender is 'male' and is playing on the 'North' course then apply the formula as *1.202. If is female then apply a different Formula, say *1.502. if playing on the 'South' course and is a 'male' apply a slightly different formula say *1.225 and if is 'female' a *1.236 formula. having worked all that out, to apply a Discount or weighting Value held in the GameName (in GameTable). hope this is not too complicated! i have placed this question with ED in the General Listing, but thought it may be better placed here Michael |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Error Message - "Method not valid without suitable object" | calmo | General Discussion | 1 | January 20th, 2006 03:56 PM |
Why windows XP is not suitable for running MS-DOS Applications? | Luis_Diaz | General Discussions | 2 | December 27th, 2005 10:47 PM |
Suitable RDBMS. | Shotodru | General Discussion | 4 | September 17th, 2005 03:14 AM |
Cant display gif file: "suitable graphics importer not found" | tomtchow | General Discussions | 0 | June 23rd, 2005 03:03 PM |
The system file is not suitable for running Microsoft Windows app. | amstutz | General Discussion | 1 | January 24th, 2005 06:46 PM |