ACCESS FEED: Re: [MS_AccessPros] How to eliminate records

Selasa, 22 September 2015

Re: [MS_AccessPros] How to eliminate records

Hi John:

I keep getting an invalid syntax error. This is the FIRST SQL query I have ever written so forgive me my inexperience. I uploaded a database (with dummy data) so you can see the fields and structure without compromising the voters listed. It is "DB for Access Professionals.accdb". My query syntax is below as you can't save it unless it dos not contain errors. What am I doing wrong?

SELECT [Cont_List_From_Phil.*]

FROM [Cont_List_From_Phil]

WHERE [ Cont_List_From_Phil.id] NOT IN

(SELECT CONTACT ID FROM Activities & Statuses for Contacts WHERE Status/Event = "yard sign 2015");

On Sun, Sep 20, 2015 at 2:27 PM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Jan-

SELECT tblOne.*
FROM tblOne
WHERE tblOne.Id NOT IN
(SELECT ID FROM tblTwo WHERE Activity = "Yard Sign");

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Sep 20, 2015, at 9:38 PM, jan.hertzsch@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I am trying to create a query that will exclude certain records. I have joined a table with (ID) name/address to a table with ID and activity.

So table one has: record 10 John Smith 123 Main Street
Table two has: 3 records for John Smith (ID=10)
10 | donor
10 | yard sign
10 | canvasser
I want to run a query where I exclude anyone (John Smith) who already has a yard sign. If I say not 'yard sign' in my criteria, I still get John Smith because he has donor and canvasser records.

Thanks