If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Filtering for "Not Something"
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 |
#2
|
|||
|
|||
Filtering for "Not Something"
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" |
#3
|
|||
|
|||
Filtering for "Not Something"
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. |
#4
|
|||
|
|||
Filtering for "Not Something"
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 |
#5
|
|||
|
|||
Filtering for "Not Something"
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 |
#6
|
|||
|
|||
Filtering for "Not Something"
Emma Hope wrote:
Bob, My post states i have already made the join, i apologise for not using the correct terminology. No, your initial post does not state so, but we will move on. The best way to make sure we understand the query you are using is to show us the sql statement being generated by your actions in Design View. Do this by switching your query to SQL View (View|SQL View, or the toolbar button, or the right-click context menu). If you had followed my instructions, you should see an outer join, not an inner join. 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. Which is why I told you an outer join was needed. Here is how the sql to retrieve your data should look: ... oh, my bad. I neglected to take into account the task criterion. See John Spencer's reply. .. -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. |
Thread Tools | |
Display Modes | |
|
|