Selection problems in a query

[ecawilkinson]

Hi all,

To start, I am an experienced 4GL programmer, so I understand programming concepts, but cannot seem to do some very basic things in Access. Let me explain in graphic detail.
I want to make a query that selects depending on the contents of 2 fields from a single table. The problem seems to be that the second filter depends on the results of the first. anyway. The 2 fields concerned are Members.Steward and Members.Activist, the other fields are just information to put on a report and/or label print. Members.Steward has only 4 possible values : "Steward", "Deputy Convenor", "Convenor" or "Contact". I want anyone who is "Steward", "Deputy Convenor" or "Convenor". Easy. But then, I only want "Contact"s that have a non-null value in Members.Activist. In pseudo-code :
If Members.Steward is non-null and Members.Steward <> "Contact" then
select
else
if Members.Steward = "Contact" and Members.Activist is non-null then
select
endif
endif

I am unsure of the correct syntax or where I would put the code. I have tried getting all non-null values from Members.Steward then using an If or Iif in the criteria cell of Members.Activist but to no avail. Unforatunately, I only have a minimal knowldedge of SQL, so cannot attack the problem from that direction.
Whatever I try does not work. I know the solution must be staring me in the face, but after a wasted day I have decided to put my pride aside and ask for help, any of which would be much appreciated.

Thank you,
Chris

 

[sfreeman@co.mer]

I'm not sure that your pseudo-code matches your description. You want anyone in the steward field, and if that steward is a contact, then it cannot be null? If so,

Try this:

SELECT
tblMembers.Steward
, tblMembers.Activist

FROM
tblMembers

WHERE
(((tblMembers.Steward)="Steward"))
OR (((tblMembers.Steward)="Deputy Covenor"))
OR (((tblMembers.Steward)="Covenor"))
OR (((tblMembers.Steward)="Contact")
AND ((tblMembers.Activist) Is Not Null));

HTH

 

[Pat Hartman]

You'd think with all those parentheses, the expression must be correct. As it happens it is but the parentheses had nothing to do with it. It is the default order of precedence which dictates how this expression will be evaluated. When you combine AND and OR operators in the same expression, you really need to know how the expression will be evaluated. I modified the parentheses so that they properly separate the expression to avoid confusion.

tblMembers.Steward="Steward"
OR tblMembers.Steward="Deputy Covenor"
OR tblMembers.Steward="Covenor"
OR (tblMembers.Steward="Contact"
AND tblMembers.Activist Is Not Null);

ecawilkinson,
Let the QBE builder build your SQL. Select the tables you want to get data from. Draw join lines to connect the tables on the correct fields. Add criteria to the criteria line. All criteria on the same line will be "ANDed". Criteria on different lines will be "ORed".

to reproduce the above expression in the QBE pane, you'll need 4 criteria rows

[U]Steward                  Activist[/U]
"Steward"
"Deputy Convenor"
"Convenor"
"Contact"                 Is Not Null

 

[ecawilkinson]

Thanks

Hi,

thanks to both of you who replied. The SQL supplied by both worked fine. I used the criteria cell method in the end and it worked perfectly. I feel a BIT STUPID but also the scales have lifted from my eyes. My problem was that I had all the "ORs" for Steward in the same criteria cell, so could not make any exceptions for "Contact". Well, I know now. I am feeling my way round Access and getting to know a totally different way of working. I knew though that the answer had to be fairly straight forward.

Thanks so much again.
Chris

 

[Pat Hartman]

I feel a BIT STUPID

Welcome to the club. I have a permanent dent in my forhead where my palm hits it as the lights go on

I had all the "ORs" for Steward in the same criteria cell

- you could put the first three in one cell but because the fourth has additional criteria, it needs to be on a separate criteria line to be evaluated properly.