D
DN
A few months ago I received some invaluable help from this forum. I hope to
get that today.
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, but
for this one show, double the points and input the total in the Points field?
This query is used to make a table that, when the database is uploaded to a
windows server, the results are posted on the web via asp pages.
Any suggestions and solutions are appreciated.
DN
get that today.
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, but
for this one show, double the points and input the total in the Points field?
This query is used to make a table that, when the database is uploaded to a
windows server, the results are posted on the web via asp pages.
Any suggestions and solutions are appreciated.
DN