GameGrin Gaming Forums
Home LANs Forums Reviews Previews

Go Back   GameGrin Gaming Forums > General Community Forums > Hardware, Software, Gadgets, Phones Discussion > Tech Chat > Software Discussion

Software Discussion Discuss Software and ask for unsupported help

Reply
 
Thread Tools Search this Thread Display Modes
Old 21-08-2009, 03:16 PM   #1
monger

Alien Controller
 
monger's Avatar
 
Join Date: Oct 2002
Location: 52.122958,-0.433802
Posts: 11,723
monger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond repute
Filter lists in MS Access

I have 2 tables, a (Product_reg) and b (Mfp_uk)

i need to export a in its current format removing any records where the email address exists in table b.

I have a query to show the contents of a, and alongside show the contents of b where the email addresses match.
Code:
SELECT *
FROM Product_reg left JOIN Mfp_uk ON Product_reg.email_address = Mfp_uk.Field1
I can make the query only show matches

Code:
SELECT *
FROM Product_reg left JOIN Mfp_uk ON Product_reg.email_address = Mfp_uk.Field1
where Field1 = email_address

I cant make the query only show mis-matches (which is basically what i need to export)

I wouldve assumed the query would just be

Code:
SELECT *
FROM Product_reg left JOIN Mfp_uk ON Product_reg.email_address = Mfp_uk.Field1
where Field1 <> email_address
but this returns nothing.

Can anyone else give me a way of doing this? I dont need to use access as such, its just one of the tables contains about 120,000 records

;
__________________
twitter.com/marchorner
monger is offline   Reply With Quote
Old 21-08-2009, 03:57 PM   #2
ExcessNeo

Bullsquid
 
ExcessNeo's Avatar
 
Join Date: Apr 2005
Location: York
Posts: 4,617
ExcessNeo has a reputation beyond reputeExcessNeo has a reputation beyond reputeExcessNeo has a reputation beyond reputeExcessNeo has a reputation beyond reputeExcessNeo has a reputation beyond reputeExcessNeo has a reputation beyond reputeExcessNeo has a reputation beyond reputeExcessNeo has a reputation beyond reputeExcessNeo has a reputation beyond reputeExcessNeo has a reputation beyond reputeExcessNeo has a reputation beyond repute
Re: Filter lists in MS Access

I'm not too sound on SQL but is something like this possible?
Code:
SELECT *
FROM Product_reg left JOIN Mfp_uk ON Product_reg.email_address = Mfp_uk.Field1
where Field1 < email_address or Field1 > email_address
__________________
Quote:
Originally Posted by Platinum View Post
or a deranged cripple in a car...
ExcessNeo is offline   Reply With Quote
Old 21-08-2009, 04:07 PM   #3
djdawsonuk

Bullsquid
Administrator
 
djdawsonuk's Avatar
 
Join Date: Jan 2007
Location: West Midlands, UK
Posts: 4,566
djdawsonuk has a reputation beyond reputedjdawsonuk has a reputation beyond reputedjdawsonuk has a reputation beyond reputedjdawsonuk has a reputation beyond reputedjdawsonuk has a reputation beyond reputedjdawsonuk has a reputation beyond reputedjdawsonuk has a reputation beyond reputedjdawsonuk has a reputation beyond reputedjdawsonuk has a reputation beyond reputedjdawsonuk has a reputation beyond reputedjdawsonuk has a reputation beyond repute
Re: Filter lists in MS Access

Could you not add something along the lines of:

Code:
WHERE Product_reg.email_address = NULL
Surely that would return only the ones without e-mail addresses?
__________________
djdawsonuk is offline   Reply With Quote
Old 21-08-2009, 04:17 PM   #4
Benneb

Poisonous Zombie
 
Benneb's Avatar
 
Join Date: Dec 2000
Location: Kent
Posts: 779
Benneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of light
Re: Filter lists in MS Access

This should be it:

Code:
 
SELECT * 
FROM Product_reg
WHERE email_address NOT IN (SELECT Field1 FROM Mfp_uk)

Last edited by Benneb; 21-08-2009 at 04:26 PM.
Benneb is offline   Reply With Quote
Old 21-08-2009, 04:36 PM   #5
monger

Alien Controller
 
monger's Avatar
 
Join Date: Oct 2002
Location: 52.122958,-0.433802
Posts: 11,723
monger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond reputemonger has a reputation beyond repute
Re: Filter lists in MS Access

thx guys
__________________
twitter.com/marchorner
monger is offline   Reply With Quote
Old 21-08-2009, 04:39 PM   #6
Benneb

Poisonous Zombie
 
Benneb's Avatar
 
Join Date: Dec 2000
Location: Kent
Posts: 779
Benneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of lightBenneb is a glorious beacon of light
Re: Filter lists in MS Access

Edit: scratch all that.
Code:
SELECT A.*
FROM Product_reg as A LEFT JOIN Mfp_uk as B ON A.email_address = B.Field1
WHERE B.Field1 IS NULL
will return exactly what i said earlier. Not sure which one would be faster though.
I suspect it would be this one as it only has to run one query.

Last edited by Benneb; 21-08-2009 at 04:47 PM.
Benneb is offline   Reply With Quote
Reply

Bookmarks

Tags
access, filter, lists

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Swear filter Colio Web Site: Updates & Problems 3 24-11-2007 03:59 PM
Tribes 2: Buddy filter Penno2 General Chat 3 29-04-2001 10:22 AM
mailing lists.. how to? SpEsH General Chat 3 10-11-2000 12:20 AM


All times are GMT +1. The time now is 11:11 AM.


More on gaming

Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2016 GameOn Networking Ltd, T/A GameGrin