Need help with not-in query

[daltman1967]

I'd like to do a not in query, but the examples I've found in google aren't very helpful. I want to compare:



d_license.First_Name, d_license.Last_Name, d_license.Date_of_Birth

and

g_license.First_Name, g_license.Last_Name, g_license.Date_of_Birth

In other words, I want ONLY the records from d_license where the corresponding name & date of birth are NOT found in g_license. Can anyone help me with this?? Thanks!

[June7]

Did you try using the Find Unmatched Query wizard in Access?

[Robeen]

You can try using the Unmatched Query Wizard.

I cooked up two quick Tables - Table4 & a copy of Table4 [Table4_Copy].
I deleted a few rows from Table4_Copy.

Then I used the wizard & used the unmatched query.

This is the resulting SQL:

 
SELECT Table4.CustomerName, Table4.CountLocation
FROM Table4 LEFT JOIN Table4_Copy ON Table4.[CustomerName] = Table4_Copy.[CustomerName]
WHERE (((Table4_Copy.CustomerName) Is Null));

[Robeen]

Oops!
I did it again! I guess i was still typing when June replied.
I did the same thing earlier today to pbaldy!
Sorry.

[June7]

Not a problem. You went to more effort and the OP has more info to consider.

[daltman1967]

Many thanks. Once I added my second & third fields, the result came out just as I'd hoped. I didn't know that function was there; I was trying to build it myself.

[daltman1967]

One last question. Now that I have the query that shows the data I want...

...how do I use it?? I mean, I want to display (and EDIT) only these specific records. DAO says that the query is read-only, even if I hard-code it in the "open recordset".

[June7]

It tells you which names you need to search for in Table4 and edit. How many are there? I presume you are try to track down misspellings. If your data allows misspelled names, I suspect data structure is flawed. Why do you have two tables that need to be compared anyway?

The SQL you show doesn't include a unique ID field from Table4 in the SELECT. If you include an ID then you could do an INNER join of Table4 and the FindUnmatched query on the unique ID and view only those records.

[daltman1967]

June7: I really don't understand your reply.

I have two tables that I'm dealing with here - d_license and g_license. They both have in common field names such as First_Name, Last_Name, and Date_Of_Birth. These are the fields I'm interested in.

My query was meant to list those people who are listed in d_license but NOT in g_license. I then want to edit the resulting list. As I said above, I've achieved the list - but I can't edit it.

My query is as follows:

SELECT DISTINCT D_License.Last_Name, D_License.First_Name, D_License.Date_of_Birth, D_License.Include
FROM D_License LEFT JOIN G_License ON (D_License.[First_Name] = G_License.[First_Name]) AND (D_License.[Last_Name] = G_License.[Last_Name]) AND (D_License.[Date_of_Birth] = G_License.[Date_of_Birth])
WHERE (((G_License.First_Name) Is Null))
ORDER BY D_License.Last_Name;

This gives me EXACTLY what I was looking for, insofar as the list of names. However, it does NOT give me the three extra fields I need - Phone_Number, Called, and Code. I can't include those fields in the query, because then it wouldn't return DISTINCT (I have multiple records for some individuals; they came in for more than one d_license class).

What I had hoped to do was to take the resulting query and loop through it, setting Include to equal 2; I could then view the whole table, limiting to those records with Include=2.

However, I can't edit the query - it says read only. I need to do this SOMEHOW, and I still need help. Any ideas??

[June7]

I did a test of what I had in mind and it won't work. Including the Find Unmatched query in a join still results in a non editable dataset.

The few times I have used Find Unmatched I simply made note of the records I needed to fix, sometimes by printing the query.

Other than using VBA to accomplish this, I have no other ideas.

[orange]

Can you show us the table designs for both g_license and D_license -- all the fields?
And can you tell us exactly what you have to edit?
You didn't mention the three extra fields I need - Phone_Number, Called, and Code.
until after people had offered solutions.Help us help you by stating the problem and context.We can only respond to what you tell us/show us.

As others have said, I think this should identify the records you need to edit. (query DLicenseNOTGLicense)

SELECT DISTINCT D_License.Last_name
, D_License.First_Name
, D_License.Date_Of_Birth
, G_License.PhoneNUmber
, G_License.Called
, G_License.Code
, D_License.PhoneNUmber
, D_License.Called
, D_License.Code
FROM D_License LEFT JOIN G_License ON
 (D_License.Date_Of_Birth = G_License.Date_Of_Birth) AND
 (D_License.Last_name = G_License.Last_name) AND 
(D_License.First_Name = G_License.First_Name)
WHERE (((G_License.First_Name) Is Null))
ORDER BY D_License.Last_name;

[Michael Dean]

What is the primary key on d_license? Include that in this same query you've designed that shows the records you want. Save the query. Let's suppose you call it query1. Now close query1. Create a new query. Add query1 and d_license, and join query1 to d_license on the primary key field. Pull all the fields from d_license you want, but not any from query1. This recordset should be editable.

[June7]

Michael, that's exactly what I thought and started to suggest in post 10. So I tested the idea with my data and it failed. Including the FU query in a join on Table1 made the Table1 fields uneditable. Most annoying. Unless you have a different experience, I don't see is possible. Would have to make a table of the FU query and use the table in join.

[orange]

I don't think there are PKs, otherwise why wouldn't they be included in what was tried (that's a rhetorical question - we can all guess why).
But the "they came in for more than one d_license class" makes me think there are structural concerns as well. Let's let the poster show us more, or provide additional info, before we guess more.

[daltman1967]

Ok, here's the relevant table structure:

D_License:

Last_Name, First_Name, Date_Of_Birth, Phone_Number, Called,
Date_Called, Code

G_License:

Last_Name, First_Name, Date_Of_Birth, Phone_Number

There are other fields, but these are the only ones I'm concerned with at the moment.

Mr. Jones has taken a D_License class, but NOT the G_License class. Mr. Smith has taken BOTH the D_License class AND the G_License class.

The list should be of ALL people who have taken the D_License class, but NOT the G_License class. I'm using First_Name, Last_Name, and Date_Of_Birth to compare records, as "John Smith 10/02/1967" is different from "John Smith 01/02/1955"; the names are the same, but they're different people; I'm also using the information to provide DISTINCT records, as Mr. Jones may have taken two D_License classes.

I'm making two resulting listboxes from the data: Those who have been called, and those who have not.

The resulting lists should display something like this:

Last_Name First_Name Phone_Number Date_Called Called Code
Johnson Fred (305) 555-1212 01/17/12 Yes LM

Last_Name First_Name Phone_Number Date_Called Called Code
Smith John (305) 555-1212 ________ __ ____

etc.

I can then go through either list and select a name, then click a drop-down list of the call codes. When I do this, the On Change event of the drop down list goes through a vbcode process (a very simple one) and changes the data in D_License to reflect the change - today's date, "Yes" for called, and the call code.

As I said, only the Last_Name, First_Name, and Date_Of_Birth should be part of the "DISTINCT" query, otherwise the query would fail; however, the other data DOES need to be included....

I have a similar function to this D_not_G_Query that I was able to complete by writing a simple comparison, but within a single table. With that one, I was looking through D_License to see if Mr. Jones had taken the 24 hour class but NOT the 16 hour class.

To do this, I cycled through the list of ALL D_License records, and assigned the resulting First_Name, Last_Name, and Date_Of_Birth to local variables. I then loop through the variables to find duplicates.

If a duplicate is found (two classes taken), I set D_License!Include to equal 2; if not, I set it to 1. The resulting query was easy; display ONLY the records with a 1.

For the D_not_G_Query, I had hoped to possibly do the same; come up with the list, and then mark the D_License!Include to be 2 for the ones on the list, and 1 for the ones NOT on the list - and then the resulting query would be easy, as before. However, it seems that the JOIN is making this thing uneditable.

I hope this explains everything well enough. Perhaps I could run a second query -- select ALL records from D_License where they match the records found in D_not_G_Query. Any ideas, anyone?