Filtering for "Not Something"
in [Queries]
|
From: Emma Hope on 30 Jun 2008 05:55 I have one table which contains all my contacts i.e. fields ContactID & Name and a second table containing tasks, i.e. fields ContactID & Task. So for example: Table1 ContactID Name 1 Fred Smith 2 Joe Bloggs 3 Mickey Mouse 4 Minnie Mouse 5 Donald Duck Table2 ContactID Task 1 Sent Email 2 Sent Email 3 Telephoned I want to run a query that shows me all those who have not had a particular task, i.e. for the above example, ContactIDs 3, 4 & 5 have NOT been 'Sent Email' but when i run a query joined one to many (arrow goes from Table1 to Table2) from Table1 to Table2 (on ContactID) & filter for NOT "Sent Email", i only get contactID 3 show up (i.e. the one that has a record on Table2) i want ContactID 3,4 & 5 to show up. Can anyone help please! Thanks Emma
From: Bob Barrows [MVP] on 30 Jun 2008 06:18 Emma Hope wrote: > I have one table which contains all my contacts i.e. fields ContactID > & Name and a second table containing tasks, i.e. fields ContactID & > Task. > > So for example: > Table1 > ContactID Name > 1 Fred Smith > 2 Joe Bloggs > 3 Mickey Mouse > 4 Minnie Mouse > 5 Donald Duck > > Table2 > ContactID Task > 1 Sent Email > 2 Sent Email > 3 Telephoned > > I want to run a query that shows me all those who have not had a > particular task, i.e. for the above example, ContactIDs 3, 4 & 5 have > NOT been 'Sent Email' but when i run a query joined one to many > (arrow goes from Table1 to Table2) from Table1 to Table2 (on > ContactID) & filter for NOT "Sent Email", i only get contactID 3 show > up (i.e. the one that has a record on Table2) i want ContactID 3,4 & > 5 to show up. > Right-click the arrow joining the tables (it's tricky - persist until you right-click exactly on that line) and choose Jon properties. Then click the button that says something like "select all records from Table1 and only records from Table2 that satisfy the join" and click OK. Run the query and you should see all the records from Table1 now, with Null in the unmatched Task field. Congratulations, you've just created your first outer join. Up to now you've been working with simple inner joins. Now it's simply a matter of filtering out the matched ones. Go back to Design View and in the Criteria row under Task, enter "Is Null" (without the quotes, of course). -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM"
From: Emma Hope on 30 Jun 2008 07:21 Bob, My post states i have already made the join, i apologise for not using the correct terminology. I am past this stage in my abilities and use joins regularly. If you make a simple db & add the data i have given as my example, you will see the problem, even with an inner join. With the inner join, i see ContactID 3 only and not ContactID 4 & 5 which is what i need. Any other ideas? Emma P.S. I have tried is null as well but this doesn't show what i need either.
From: Rick Brandt on 30 Jun 2008 07:28 Emma Hope wrote: > Bob, > > My post states i have already made the join, i apologise for not > using the correct terminology. > > I am past this stage in my abilities and use joins regularly. If you > make a simple db & add the data i have given as my example, you will > see the problem, even with an inner join. With the inner join, i see > ContactID 3 only and not ContactID 4 & 5 which is what i need. > > Any other ideas? > Emma > > P.S. I have tried is null as well but this doesn't show what i need > either. Have you tried... WHERE ContactID Not In(SELECT ContactID FROM Table2 WHERE Task = "Sent Email") -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
From: John Spencer on 30 Jun 2008 08:02
You can do this in two queries. First Query - find every contact that HAS a record with Sent EMail as the task. SELECT [Contact Id] FROM Table2 WHERE Task = "Sent EMail" Save that query as qGotEmail. Now use the Unmatched query wizard to find records in table 1 that don't match records in the query. The query will look something like the following. SELECT Table1.* FROM Table1 LEFT JOIN qGotMail ON Table1.[Contact ID] = qGotMail.[Contact Id] WHERE qGotMail.ContactID is Null A one query method uses Not IN in the where clause. Not IN is slow with large sets of records. SELECT Table1.* FROM Table1 WHERE [Contact ID] NOT IN ( SELECT [Contact Id] FROM Table2 WHERE Task = "Sent EMail") In the query grid, -- Add Table1 -- Add the fields you want to see -- Under the contact id set the criteria as NOT IN (SELECT [Contact Id] FROM [Table2] WHERE Task = "Sent EMail") John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Emma Hope wrote: > I have one table which contains all my contacts i.e. fields ContactID & Name > and a second table containing tasks, i.e. fields ContactID & Task. > > So for example: > Table1 > ContactID Name > 1 Fred Smith > 2 Joe Bloggs > 3 Mickey Mouse > 4 Minnie Mouse > 5 Donald Duck > > Table2 > ContactID Task > 1 Sent Email > 2 Sent Email > 3 Telephoned > > I want to run a query that shows me all those who have not had a particular > task, i.e. for the above example, ContactIDs 3, 4 & 5 have NOT been 'Sent > Email' but when i run a query joined one to many (arrow goes from Table1 to > Table2) from Table1 to Table2 (on ContactID) & filter for NOT "Sent Email", i > only get contactID 3 show up (i.e. the one that has a record on Table2) i > want ContactID 3,4 & 5 to show up. > > Can anyone help please! > Thanks > Emma |