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
|
|||
|
|||
IIf help
I have a query that uses IIf statements to calculate a point system from
current horse shows based on place and number of entries. This is the query: SELECT Entries.Place, IIf([Entries]1,([Entries]-[Place])*0.5,0) AS Points1, IIf([Entries]3 And [Place]=1,1,0) AS Bonus, IIf([Points1]+[Bonus]CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),[Points1]+[Bonus]) AS Points, Classes.Entries, Entries.ClassID, Classes.ClassNum, Horses.HorseName, OwnersandRiders.[FirstName] & " " & [LastName] AS Owner, Horses.NWHATRNumber, Horses.Title, Horses.Titles, Classes.NWHAHP, OwnersandRiders.NWHAMember, Shows.Year FROM Shows INNER JOIN (OwnersandRiders INNER JOIN (Horses INNER JOIN (Classes INNER JOIN Entries ON Classes.ClassID = Entries.ClassID) ON Horses.HorseName = Entries.HorseName) ON OwnersandRiders.OwnerRiderID = Entries.OwnerRiderID) ON (Shows.Name = Classes.ShowName) AND (Shows.ShowID = Classes.ShowID) WHERE (((Classes.NWHAHP)"NA") AND ((OwnersandRiders.NWHAMember)="yes") AND ((Shows.Year)="2005")) ORDER BY Entries.Place, Classes.Entries; These calculations are based on horse shows with single points earned. The last horse show of the season is double points. Any ideas on how to adapt the above query to calculate as it does now (single points for all shows except for the last one), but for this one show, double the points and input the total in the Points field? I run this query, the run a crosstab query to sum the records, then run a make table query. The table is used to retrieve records from a web page. Any suggestions and solutions are appreciated. Thanks, Debbie |
#2
|
|||
|
|||
How do you know which is the "last" horse show of the season? If you can
figure out how to identify that Show, then you can set up a few more queries based on the one you have below. First, you'd need to alter your query to return the ShowID. Then, you'd write one query to return all this data for all shows EXCEPT the end of season show (assuming your current query is called ShowPoints, let's call it NormalShowPoints): SELECT Place, Points1, Bonus, Points, Entries, ClassID, ClassNum, HorseName, Owner, NWHATRNumber, Title, Titles, NWHAHP, NWHAMember, Year FROM ShowPoints WHERE ShowID [End of season Show ID] You then write another query to return exactly the same thing, except doubled (EndOfSeasonShowPoints): SELECT Place, Points1 * 2 as Points1, Bonus * 2 as Bonus, Points * 2 as Points, Entries, ClassID, ClassNum, HorseName, Owner, NWHATRNumber, Title, Titles, NWHAHP, NWHAMember, Year FROM ShowPoints WHERE ShowID = [End of season Show ID] You then put the output of both those queries together in a UNION query, AllShowPoints: SELECT * FROM NormalShowPoints UNION SELECT * FROM EndOfSeasonShowPoints You can then use this query as the basis of your crosstabs and things. I suspect that you should be able to write another query to return the ShowID of the end of season show - if that's the case, then you adapt the first query above to use an OUTER JOIN where the EndOfSeasonShowTable.ShowID is NULL and the second query to use an INNER JOIN to this table. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200509/1 |
#3
|
|||
|
|||
David,
This was a great solution!!!! I had fashioned a workaround with exporting data, copying data into a spreadsheet and then importing back to run the crosstab and make the table. This solution solved my problem beautifully. Thanks for your easy to follow directions. And, this was just in the nick of time since the show is next week and I will be working on it then. I used a test db with some bogus data and it appears to be working as needed. I was getting worried that no one was going to take a chance on offering up a possible solution. Thanks again, Debbie "David S via AccessMonster.com" wrote: How do you know which is the "last" horse show of the season? If you can figure out how to identify that Show, then you can set up a few more queries based on the one you have below. First, you'd need to alter your query to return the ShowID. Then, you'd write one query to return all this data for all shows EXCEPT the end of season show (assuming your current query is called ShowPoints, let's call it NormalShowPoints): SELECT Place, Points1, Bonus, Points, Entries, ClassID, ClassNum, HorseName, Owner, NWHATRNumber, Title, Titles, NWHAHP, NWHAMember, Year FROM ShowPoints WHERE ShowID [End of season Show ID] You then write another query to return exactly the same thing, except doubled (EndOfSeasonShowPoints): SELECT Place, Points1 * 2 as Points1, Bonus * 2 as Bonus, Points * 2 as Points, Entries, ClassID, ClassNum, HorseName, Owner, NWHATRNumber, Title, Titles, NWHAHP, NWHAMember, Year FROM ShowPoints WHERE ShowID = [End of season Show ID] You then put the output of both those queries together in a UNION query, AllShowPoints: SELECT * FROM NormalShowPoints UNION SELECT * FROM EndOfSeasonShowPoints You can then use this query as the basis of your crosstabs and things. I suspect that you should be able to write another query to return the ShowID of the end of season show - if that's the case, then you adapt the first query above to use an OUTER JOIN where the EndOfSeasonShowTable.ShowID is NULL and the second query to use an INNER JOIN to this table. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200509/1 |
Thread Tools | |
Display Modes | |
|
|