A+, Darrell.
I'm not John but, try the following:
SELECT *
FROM Cont_List_From_Phil
WHERE id NOT IN
(SELECT [CONTACT ID] FROM [Activities & Statuses for Contacts] WHERE [Status/Event] = "yard sign 2015");
You need the brackets around the table name and field names in the subquery because you have spaces and special characters in the field and table names.
You don't need the brackets in the main query because you don't have spaces or special characters in table name or field name.
Darrell
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, September 22, 2015 12:36 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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
(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
The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (5) |
Tidak ada komentar:
Posting Komentar