By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,345 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Compare between 2 queries - how to?

P: n/a
Hi:
I'm trying to compare between 2 queries I have run and saved in Access.

The DB contains a list of names, emails.

Query 1 = Returns all names and emails. I named this query "allemails"

Query 2 = Returns all the names with good emails. I named this query
"goodemails"
How do I run a comparison query between the 2 queries to return the bad emails?
Meaning, say "allemails" returned me 10 emails and "goodemails" returned me 6,
there should be a difference of 4.

Please help!!

Thanks.
----------------------------------------
pls reply to speedoflight at cosmo.com
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
It sounds like your 2 queries are based on the same table. If this is
the case then you must have at least 2 fields in this table, a textfield
for the email and a yes/no field for if the email is good or bad

fldEmail fldGoodBad
aa*@a.com 1 (or true)
bb*@b.com 0 (or false)
cc*@c.com 1
dd*@d.com 0
ee*@e.com 1
ff*@f.com 1
gg*@g.com 1
...

In one query if you want to return the emails that are not good, you
simply type 1 in the the criteria section fof fldGoodBad field to return
only the good emails or type 0 if you want to return the bad emails. If
you want to compare the good emails with the bad ones in the same query
you could write a query like this one - it is a little more
sophisticated than the queries above:

SELECT t1.emails, t1.goodbad, t2.emails, t2.goodbad
FROM tblEmails AS t1 LEFT JOIN [select * from tblEmails where goodbad =
false]. AS t2 ON t1.emails = t2.emails;

This query returns all the good and bad emails in t1 and only the bad
emails in t2. Note: t1 and t2 are alias's for the same table. t1
represents the the whole table with good and bad emails and t2
represents the same table but only the bad emails.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #2

P: n/a
Create a new query using Query 1 and LEFT Join Query 2 then select all of
Query 1 where Query 2 is NULL.

"speedoflight" <speedoflight@_REMOVETHISTOREPLY_cosmo.com> wrote in message
news:S4******************@www.newsranger.com...
Hi:
I'm trying to compare between 2 queries I have run and saved in Access.

The DB contains a list of names, emails.

Query 1 = Returns all names and emails. I named this query "allemails"

Query 2 = Returns all the names with good emails. I named this query
"goodemails"
How do I run a comparison query between the 2 queries to return the bad emails? Meaning, say "allemails" returned me 10 emails and "goodemails" returned me 6, there should be a difference of 4.

Please help!!

Thanks.
----------------------------------------
pls reply to speedoflight at cosmo.com

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.