combining two existing queries (1 Viewer)

[memilanuk]

I'm guessing that I'm missing something blindingly obvious here, but I could use a nudge (okay, shove) in the right direction.

I have a table with various member information... one field being 'Date Expires' and two more being 'Regular' and 'Life' (both Yes/No). What I want is to be able to show all the current active members, including both the Life members and all the Regular members with a 'Date Expires' after a certain date.

One query, which uses 'Life' = Yes as the criteria, works fine. It returns the 120 Life members that I would expect. This is the SQL version of that query:

SELECT Members.[Last Name], Members.[First Name], Members.[Mid Init], Members.[Card Number]
FROM Members
WHERE (((Members.Life)=Yes))
ORDER BY Members.[Last Name] DESC , Members.[First Name] DESC , Members.[Mid Init] DESC , Members.[Card Number] DESC;

The second query uses Regular = Yes and 'Date Expires' > DateSerial(Year(Date(),1,1) to return any Regular member with 'Date Expires' after 01/01/2010 (about 40 total):

SELECT Members.[Last Name], Members.[First Name], Members.[Mid Init], Members.[Card Number], Members.[Date Joined], Members.[Date Expires]
FROM Members
WHERE (((Members.[Date Expires])>DateSerial(Year(Date()),1,1)) AND ((Members.Regular)=Yes))
ORDER BY Members.[Last Name] DESC , Members.[First Name] DESC , Members.[Mid Init] DESC , Members.[Card Number] DESC;

I'm not really sure at this point what I need to do. Most of my attempts have failed in one fashion or another; I can't seem to combine the two queries above and get the expected ~160 records. Seems like it'd be easier via SQL than via the Query Builder, but I keep getting lost in all the ()

TIA,

Monte

 

[pbaldy]

I suspect a criteria of:

WHERE (A And B) OR C

would do the trick, where C is the single criteria from the first query and A And B is the criteria from the second. If you get it into SQL view, get rid of all the stupid extraneous parentheses that the Query Builder adds, so that you end up with the above.

 

[memilanuk]

Looks like that did it... I wasn't sure if all the () were something necessary for the way Access interpreted SQL. The final SQL statement to pull all the desired records ended up looking like this (beginning and end trimmed for brevity):

WHERE (Members.[Date Expires]>DateSerial(Year(Date()),1,1) AND Members.Regular=Yes) OR Members.Life=Yes

Guess I got spoilt using Notepad++ for other stuff where it highlights the matching parentheses/brace/bracket. I may have to copy the SQL over to there for editing in the future.

After the fact... I opened the query back up in 'Design' mode - so THAT is what the 'or' hiding over on the side is for. Sheesh...

Thanks,

Monte

 

[gemma-the-husky]

i must say i always try to use visual queries, rather than type SQL, for reasons like this.

other than union queries, i think a visual query can reproduce anything you can do in SQL - and functions and form references are easier ot get right in a visual query, i think.