IIf help

[DN]

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

 

[David S via AccessMonster.com]

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.

 

[DN]

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