Need help with not-in query

[Michael Dean]

There is one work-around for the non-editable recordset problem that might help.



Run the D_not_G_Query as a make-table query. Suppose you call the table D_not_G_Table. Open Relationships and set up relationships between First_Name, Last_Name, and Date_Of_Birth on D_not_G_Table and D_License. Use the "Enforce Referential Integrity" option for each relationship. Create a new query with D_not_G_Table and D_License. Pull the extra fields you need from D_License, and you should be able to edit them.

I leave it to June7 to actually test this idea and tell me it doesn't work!

[daltman1967]

Well, as soon as I tried this, I found it wouldn't work as listed. It won't let met set Enforce Referential Integrity. Plus, when I tried to add the second relationship, instead of doing an arrow from one table to the other, the program added a duplicate d_license table!

This is so frustrating!

[orange]

You don't mention primary keys in this post about structures. Are there primary keys?

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?

[June7]

Michael, already with you on Make Table suggestion (in my previous post). However, don't think really need to build the relationship, just do a query. If you do set Relationship, don't worry about those quirks.

daltman, is this a one time effort just to clean up data or this going to be a repetitive exercise?

[daltman1967]

As far as the keys, I'm so used to seing the tables that I didn't list them - nor did I think it made a difference. Besides that, there's lots of table relationships already set up.

This is a repetitive exercise. Both D_License and G_License have their own, independent ID keys.

My current solution, which works (but is very slow and inelegant) is as follows:

  Set rstG = dbsAWIA.OpenRecordset("D_License")
  x = 1
  rstG.MoveFirst
  Do Until rstG.EOF
    rstG.Edit
    nm1(x) = rstG!First_Name + " " + rstG!Last_Name + " " + Format(rstG!Date_of_Birth, "mm/dd/yyyy")
    ID(x) = rstG!ID
    x = x + 1
    rstG!Include = 0
    rstG.Update
    rstG.MoveNext
  Loop
  Set rstG = dbsAWIA.OpenRecordset("G_License")
  x = 1
  rstG.MoveFirst
  Do Until rstG.EOF
    nm2(x) = rstG!First_Name + " " + rstG!Last_Name + " " + Format(rstG!Date_of_Birth, "mm/dd/yyyy")
    x = x + 1
    rstG.MoveNext
  Loop
  For t = 1 To n1
    For y = 1 To n2
      If nm1(t) = nm2(y) Then mk(ID(t)) = 2
    Next y
  Next t
  Set rstG = dbsAWIA.OpenRecordset("D_License")
  rstG.MoveFirst
  Do Until rstG.EOF
    rstG.Edit
    If mk(rstG!ID) = 2 Then rstG!Include = 2 Else rstG!Include = 1
    rstG.Update
    rstG.MoveNext
  Loop
  rstG.Close

I compare a string of First_Name, Last_Name, and Date_Of_Birth from D_License to the same strings in G_License; if there's a match, I set the "Mark" (mk) for the corresponding id # to 2. Once I've gone through the list, anything that isn't a 2 is marked as a 1. The resulting listboxes then display ONLY the records with the 1's - in other words, every record that was on the D_License list but NOT on the G_License list. If there's a neater, cleaner way to do this, I'm all ears.

[June7]

I think we are all suggesting the FU query should not be DISTINCT and should include the ID from d_License. Then make table from this query, join on the ID to d_License. Do edits, delete the temporary table.