Nested IIF's no good...what do i do?

  • Thread starter Thread starter binger
  • Start date Start date
B

binger

Hi all,

Here's my scenario (football pool)...
I've got 4 selections each week in the "selections" table.
I've got 17 selections each week for the "winners" table.

For each item in "selections" table, I want to scan the 17 selections
in Winners table to see if the winner is in that table or not.

The resulting table would be something that contains 4 values with
"yes, no, no, yes" - meaning that the 1st and 4th picks were found in
table "winners".

I've tried nesting IIF's - but I think it errors our way before I get
close to 17 nested IIF's.

Anyone got any good ideas? Is this a candidate for a VB function
(which I am not familiar with) or some other sql?

Thanks for anyones input!
Binger
 
Here's my scenario (football pool)...
I've got 4 selections each week in the "selections" table.
I've got 17 selections each week for the "winners" table.

For each item in "selections" table, I want to scan the 17 selections
in Winners table to see if the winner is in that table or not.

The resulting table would be something that contains 4 values with
"yes, no, no, yes" - meaning that the 1st and 4th picks were found in
table "winners".
Click to expand...

No IIF's or code needed at all!

Create a Query joining Selections to Winners by the team ID. Select
the JOIN line and change it to "Left Outer Join" - "show all records
in Selections and matching records in Winners".

If you just select the team name from Winners, it will be the team
name if they won, and NULL if they didn't. Alternatively you could use
a calculated field:

WasAWinner: IIF(IsNull([Winners].[Team]), "No", "Yes")
 
John Vinson said:
Here's my scenario (football pool)...
I've got 4 selections each week in the "selections" table.
I've got 17 selections each week for the "winners" table.

For each item in "selections" table, I want to scan the 17 selections
in Winners table to see if the winner is in that table or not.

The resulting table would be something that contains 4 values with
"yes, no, no, yes" - meaning that the 1st and 4th picks were found in
table "winners".
Click to expand...

No IIF's or code needed at all!

Create a Query joining Selections to Winners by the team ID. Select
the JOIN line and change it to "Left Outer Join" - "show all records
in Selections and matching records in Winners".

If you just select the team name from Winners, it will be the team
name if they won, and NULL if they didn't. Alternatively you could use
a calculated field:

WasAWinner: IIF(IsNull([Winners].[Team]), "No", "Yes")
Click to expand...

BTW - i ended up creating a function that worked....but I CANT CALL a
query that uses a function in ASP (or at least its much more
complicated than I can handle)...so I'm back to trying to do it in
queries without functions...
 
John - thanks for the info...not sure if this is exactly what I'm
looking for...let me explain a bit more....

Table 1 has player name, and picks 1,2,3, and 4 (4 columns per row of
data).
The winners will have 17 columns of data. I need for EACH of the
columns in table 1 to scan through all 17 columns of data to see if
there is a match or not. Based on what you suggest, would that mean I
would have to join Pick1 (table 1) 17 times to see if it matches, and
then Pick2 17 times, etc....through pick4 ?
Click to expand...

Well, because you have an improperly normalized table structure, your
query is difficult. If you had 17 ROWS of data it's a simple join as
suggested. You have a "spreadsheet" design instead; you've embedded a
one to many relationship in a single record, and you're thereby paying
the cost of doing so.

You may be able to join Table2 to Table1 *SEVENTEEN TIMES* - add the
table repeatedly to the query; make all 17 join lines Left Outer
Joins. But a proper table structure would make life a lot easier!
 
fyi -what i ended up doing to essentially normalize the 'winners'
table was joing the 17 columns into 1 field, then use the "instr"
function to search through the text to see if the selection value was
included in the combined string....
Click to expand...

Yow! Glad it worked.

A "Normalizing Union Query" is handy when you have a table with
repeating fields and need to use the data in it to populate a
normalized table; something like

SELECT ID, Field1 AS Winner FROM wideflat
UNION ALL
SELECT ID, Field2 FROM wideflat
UNION ALL
SELECT ID, Field3 FROM wideflat
....
UNION ALL
SELECT ID, Field17 FROM wideflat;

Base an Append query on this query to move the data.
 
Back
Top