PFW: query -- OR, iif? 
Author Message
 PFW: query -- OR, iif?

I need to do a query where a field in one table indicates the validity
of information in another.

In more detail: I'm working on a sports registration program.  Events
can be offered for either Men or Women, or in some cases they're Combined

The competitors database contains the competitors actual sex (2 values,
obviously :-) ).  The list of event contains a flag indicating whether
the event is "ordinary" or Combined.  Competitors can sign up for more
than one event, so theres a 1->M link to a list of events.

I want to create a list of all the events which actually are signed up
for.  (Some events may not actually get participants of both sexes; some
may get NO participants.) However, for the combined events, I want to
show a single event, rather than splitting by sex.

For most events I simply query for a list of participants, and add the
competitors sex to the key.  However, for the combined events, I want
to put the flag indicating that it's a combined event into the key instead,
so it looks like a single event.

Ideally, I'd just add

        calc iif(<combined event>,<Combined value>,_Competitors_Sex>) as Sex

however, iif isn't legal in Queries.

I also tried doing a query with:

        ... | Event Sex                         | ...
        ... | = <combined> Combined Value       | ...
        ... | NOT <combined> _Competitors_Sex   | ...

However, it treats the above as an AND (not OR) query and generates an empty
table (a and not a = false).  (It must interpret OR queries in that syntax
only when not in the same field.)

Any ideas?

Donn Terry

Mon, 18 Nov 1996 00:08:58 GMT
 [ 1 post ] 

 Relevant Pages 

1. PFW query query

2. Access attach to PfW 5.0 tables or Counter field in PfW 4.5

3. Upgrading from PFW v1.0 to PFW v5.0

4. upgrading IIF-9.21-UC2 to IIF-9.21-UC3 and select became

5. Insert query PfW 5.0

6. pfw 4.5: conditional calcs in queries

7. PFW: printing reports from queries

8. HELP: must convert IIF( ) from Access queries to T-SQL

9. IIF function in sql statement equal to what is used in Access-Query

10. Migrate MS-Access to SQL-Server - iif construction in aggregation query failes

11. IIF function in query

12. IIF Function simulation in update queries??

Powered by phpBB® Forum Software