IIf statements

[Guest]

I have created a query where I need to calculate points. The SQL View is this:

SELECT Entries.Place, IIf([Entries]>1,([Entries]-[Place])*0.5,0) AS Points,
IIf([Entries]>3 And [Place]=1,1,0) AS Bonus,
IIf([Points]+[Bonus]>7,7,[Points]+[Bonus]) AS Final, Classes.Entries,
Entries.ClassID, Classes.ClassNum, Horses.HorseName, 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 (((OwnersandRiders.NWHAMember)="yes") AND ((Shows.Year)="2005"))
ORDER BY Entries.Place, Classes.Entries;

As you can see I have an IIf statement in the query. This single statement
works great for all those who are in first place where the max points allowed
is 7. I am completely stumped at how I am supposed to construct this IIf
statement for those who are in 2nd place where the max points allowed are 5,
or 3rd place where max points allowed are 4, or 4th place where max allowed
points are 3, or 5th place and max allowed points are 2 or 6th place where
max allowed points are 1.

Can all of this be calculated within this one query or will I need a query
for each and then try to add everything up. I have been struggling with this
for days and my brain is practically fried.

If anyone has any other ideas or can physically show me how to construct
this I would be very grateful.

 

[Ken Snell [MVP]]

Don't know exactly what your query is doing, but take a look at the Choose
function to give you the correct number of bonus points:

Choose([Place], 7, 5, 4, 3, 2, 1)

If Place might be a number greater than 6, then use something like this to
give zero points for those situations:

Nz(Choose([Place], 7, 5, 4, 3, 2, 1), 0)

 

[Guest]

Thanks for your reply.
I am not familiar with using functions. I would not know where to begin
using this function in relation to the results of my query. The results of
this query are used to make a table that is accessed via a web page.

I guess my question is how do I repeat this IIf statement to obtain the
calculated results for each place with its own max points?

Do I need 6 seperate queries or can this be done within 1 query?

Don't know exactly what your query is doing, but take a look at the Choose
function to give you the correct number of bonus points:

Choose([Place], 7, 5, 4, 3, 2, 1)

If Place might be a number greater than 6, then use something like this to
give zero points for those situations:

Nz(Choose([Place], 7, 5, 4, 3, 2, 1), 0)

--

Ken Snell
<MS ACCESS MVP>

I have created a query where I need to calculate points. The SQL View is
this:

SELECT Entries.Place, IIf([Entries]>1,([Entries]-[Place])*0.5,0) AS
Points,
IIf([Entries]>3 And [Place]=1,1,0) AS Bonus,
IIf([Points]+[Bonus]>7,7,[Points]+[Bonus]) AS Final, Classes.Entries,
Entries.ClassID, Classes.ClassNum, Horses.HorseName, 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 (((OwnersandRiders.NWHAMember)="yes") AND ((Shows.Year)="2005"))
ORDER BY Entries.Place, Classes.Entries;

As you can see I have an IIf statement in the query. This single
statement
works great for all those who are in first place where the max points
allowed
is 7. I am completely stumped at how I am supposed to construct this IIf
statement for those who are in 2nd place where the max points allowed are
5,
or 3rd place where max points allowed are 4, or 4th place where max
allowed
points are 3, or 5th place and max allowed points are 2 or 6th place where
max allowed points are 1.

Can all of this be calculated within this one query or will I need a query
for each and then try to add everything up. I have been struggling with
this
for days and my brain is practically fried.

If anyone has any other ideas or can physically show me how to construct
this I would be very grateful.

 

[Ken Snell [MVP]]

For example, let's take a look at this one IIf expression from your query:

IIf([Points]+[Bonus]>7,7,[Points]+[Bonus]) AS Final


This could be modified to this:

IIf([Points]+[Bonus]>CInt(Nz(Choose([Place], 7, 5, 4, 3, 2, 1),
0)),CInt(Nz(Choose([Place], 7, 5, 4, 3, 2, 1), 0)),[Points]+[Bonus]) AS
Final

--

Ken Snell
<MS ACCESS MVP>

Thanks for your reply.
I am not familiar with using functions. I would not know where to begin
using this function in relation to the results of my query. The results
of
this query are used to make a table that is accessed via a web page.

I guess my question is how do I repeat this IIf statement to obtain the
calculated results for each place with its own max points?

Do I need 6 seperate queries or can this be done within 1 query?

Don't know exactly what your query is doing, but take a look at the
Choose
function to give you the correct number of bonus points:

Choose([Place], 7, 5, 4, 3, 2, 1)

If Place might be a number greater than 6, then use something like this
to
give zero points for those situations:

Nz(Choose([Place], 7, 5, 4, 3, 2, 1), 0)

--

Ken Snell
<MS ACCESS MVP>

I have created a query where I need to calculate points. The SQL View
is
this:

SELECT Entries.Place, IIf([Entries]>1,([Entries]-[Place])*0.5,0) AS
Points,
IIf([Entries]>3 And [Place]=1,1,0) AS Bonus,
IIf([Points]+[Bonus]>7,7,[Points]+[Bonus]) AS Final, Classes.Entries,
Entries.ClassID, Classes.ClassNum, Horses.HorseName, 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 (((OwnersandRiders.NWHAMember)="yes") AND ((Shows.Year)="2005"))
ORDER BY Entries.Place, Classes.Entries;

As you can see I have an IIf statement in the query. This single
statement
works great for all those who are in first place where the max points
allowed
is 7. I am completely stumped at how I am supposed to construct this
IIf
statement for those who are in 2nd place where the max points allowed
are
5,
or 3rd place where max points allowed are 4, or 4th place where max
allowed
points are 3, or 5th place and max allowed points are 2 or 6th place
where
max allowed points are 1.

Can all of this be calculated within this one query or will I need a
query
for each and then try to add everything up. I have been struggling
with
this
for days and my brain is practically fried.

If anyone has any other ideas or can physically show me how to
construct
this I would be very grateful.

 

[Guest]

Good grief, it is working perfectly. I am blown away. Thank you so much for
making my life a lot easier now. What a relief!!!
Thanks again!!!!

For example, let's take a look at this one IIf expression from your query:

IIf([Points]+[Bonus]>7,7,[Points]+[Bonus]) AS Final


This could be modified to this:

IIf([Points]+[Bonus]>CInt(Nz(Choose([Place], 7, 5, 4, 3, 2, 1),
0)),CInt(Nz(Choose([Place], 7, 5, 4, 3, 2, 1), 0)),[Points]+[Bonus]) AS
Final

--

Ken Snell
<MS ACCESS MVP>

Thanks for your reply.
I am not familiar with using functions. I would not know where to begin
using this function in relation to the results of my query. The results
of
this query are used to make a table that is accessed via a web page.

I guess my question is how do I repeat this IIf statement to obtain the
calculated results for each place with its own max points?

Do I need 6 seperate queries or can this be done within 1 query?

Don't know exactly what your query is doing, but take a look at the
Choose
function to give you the correct number of bonus points:

Choose([Place], 7, 5, 4, 3, 2, 1)

If Place might be a number greater than 6, then use something like this
to
give zero points for those situations:

Nz(Choose([Place], 7, 5, 4, 3, 2, 1), 0)

--

Ken Snell
<MS ACCESS MVP>

I have created a query where I need to calculate points. The SQL View
is
this:

SELECT Entries.Place, IIf([Entries]>1,([Entries]-[Place])*0.5,0) AS
Points,
IIf([Entries]>3 And [Place]=1,1,0) AS Bonus,
IIf([Points]+[Bonus]>7,7,[Points]+[Bonus]) AS Final, Classes.Entries,
Entries.ClassID, Classes.ClassNum, Horses.HorseName, 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 (((OwnersandRiders.NWHAMember)="yes") AND ((Shows.Year)="2005"))
ORDER BY Entries.Place, Classes.Entries;

As you can see I have an IIf statement in the query. This single
statement
works great for all those who are in first place where the max points
allowed
is 7. I am completely stumped at how I am supposed to construct this
IIf
statement for those who are in 2nd place where the max points allowed
are
5,
or 3rd place where max points allowed are 4, or 4th place where max
allowed
points are 3, or 5th place and max allowed points are 2 or 6th place
where
max allowed points are 1.

Can all of this be calculated within this one query or will I need a
query
for each and then try to add everything up. I have been struggling
with
this
for days and my brain is practically fried.

If anyone has any other ideas or can physically show me how to
construct
this I would be very grateful.

 

[Ken Snell [MVP]]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Good grief, it is working perfectly. I am blown away. Thank you so much
for
making my life a lot easier now. What a relief!!!
Thanks again!!!!

For example, let's take a look at this one IIf expression from your
query:

IIf([Points]+[Bonus]>7,7,[Points]+[Bonus]) AS Final


This could be modified to this:

IIf([Points]+[Bonus]>CInt(Nz(Choose([Place], 7, 5, 4, 3, 2, 1),
0)),CInt(Nz(Choose([Place], 7, 5, 4, 3, 2, 1), 0)),[Points]+[Bonus]) AS
Final

--

Ken Snell
<MS ACCESS MVP>

Thanks for your reply.
I am not familiar with using functions. I would not know where to
begin
using this function in relation to the results of my query. The
results
of
this query are used to make a table that is accessed via a web page.

I guess my question is how do I repeat this IIf statement to obtain the
calculated results for each place with its own max points?

Do I need 6 seperate queries or can this be done within 1 query?

:

Don't know exactly what your query is doing, but take a look at the
Choose
function to give you the correct number of bonus points:

Choose([Place], 7, 5, 4, 3, 2, 1)

If Place might be a number greater than 6, then use something like
this
to
give zero points for those situations:

Nz(Choose([Place], 7, 5, 4, 3, 2, 1), 0)

--

Ken Snell
<MS ACCESS MVP>

I have created a query where I need to calculate points. The SQL
View
is
this:

SELECT Entries.Place, IIf([Entries]>1,([Entries]-[Place])*0.5,0) AS
Points,
IIf([Entries]>3 And [Place]=1,1,0) AS Bonus,
IIf([Points]+[Bonus]>7,7,[Points]+[Bonus]) AS Final,
Classes.Entries,
Entries.ClassID, Classes.ClassNum, Horses.HorseName, 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 (((OwnersandRiders.NWHAMember)="yes") AND
((Shows.Year)="2005"))
ORDER BY Entries.Place, Classes.Entries;

As you can see I have an IIf statement in the query. This single
statement
works great for all those who are in first place where the max
points
allowed
is 7. I am completely stumped at how I am supposed to construct
this
IIf
statement for those who are in 2nd place where the max points
allowed
are
5,
or 3rd place where max points allowed are 4, or 4th place where max
allowed
points are 3, or 5th place and max allowed points are 2 or 6th place
where
max allowed points are 1.

Can all of this be calculated within this one query or will I need a
query
for each and then try to add everything up. I have been struggling
with
this
for days and my brain is practically fried.

If anyone has any other ideas or can physically show me how to
construct
this I would be very grateful.