I'm trying to make a query that cross-checks 2 databases for a matching ID, which is defined as "text".

I want to insert the entries that don't match into a new table, which I will call "Non-Members". One database contains members (around 2,000) of an organization (Roster), one database contains 53,000 people (People Table). I'm trying to make a database that will output a table of the non-members' email addresses. Every column in both databases is defined as text, and the computer I'm working with will not allow me to change that property. Here is what I have so far: (and it doesnt work...)


INSERT INTO [Non-Members_tbl] ( Last, First, Home, Work )
SELECT [People Table].[MAS_LAST] , [People Table].[MAS_FIRST] , [People Table].[MAS_HOEMAL] , [People Table].[MAS_WOEMAL]
FROM [People Table], Roster
WHERE [People Table].[MAS_ID] <> ( [Roster].[Member ID] );


Instead of "<>", I have tried "NOT IN", "NOT LIKE", and still no luck. The ID's are defined as Text, like I say, so I'm pretty sure that's why '<>' isnt helping me. Any suggestions?

This is for work, not school, but I would appriciate any help you guys can throw me. I've been reading google/online SQL course sites for a couple days now trying to figure out the solution.

10/10/2006 10:17:57 AM

Your code looks like it should work fine (although the SQL version I use, doesn't use the same syntax so I can't help you with syntax issues). Some SQL versions use "!=" as their "not equal to" operator.

Is it giving you any errors? If so what's the error? If it's not giving you errors, then there might be a problem somewhere else. Try searching for where first names are the same or something and see if it's able to find matches.

10/10/2006 10:58:52 AM

Test the query in the database tool you're using. If that works without errors then it's in your other code.

10/10/2006 11:13:03 AM

The above code gives me "Invalid Argument" with no highlighting or explaination of where the error lies. Access help is worthless on error explainations...

Also, I'd already found your suggestion, and have found that my version uses the "<>" for !=

I'm trying to get to your suggestion (first, last name comparision) but just discovered that the People table (53,000 entries) has no primary key! I think the computer I'm on won't let me define one, either, because after I do so, even if I make it a new field, it won't let me save it because "Invalid Argument". GREAT. I guess I'm going to try and export it somehow to a new table or csv>new table.

Would that be a possible reason this isnt working? No primary key? Am I correct in using "<>" to compare a string and not a number?

10/10/2006 11:27:21 AM

OK, set up a new database with separate primary keys for both tables (just an auto number field)

Now, the above code gives "data type mismatch" even though ALL the fields are defined as TEXT, except for the primary key columns, which are not referenced.

I'm giving it a run using "NOT LIKE" instead of "<>" and I'll let yall know how that goes eventually

10/10/2006 11:40:12 AM

------------------------------------------------------------------------------------------------------------------------------
UPDATE:


I have created a micro-cosmic version to illustrate the problem I'm having. Please check out the
image below, which contains sample data of EVERYTHING I'm questioning. The highlighted (in
black) table is the resulted output from the query at the bottom. Using both "NOT LIKE" and "<>"
operators yields the same results. THANK YOU for any advice or guidance-- I will be happy to provide
this test DB if you want to tinker with it yourself. Remember ALL columns are text data,
and there are no primary keys defined. I can't convert People Table's ID's to numbers because the
size of the actual table, and the nature of the data. ALL of the Member Table's ID's are numbers
defined as text, however. I hope that's clear.... see below:







[Edited on October 11, 2006 at 5:13 PM. Reason : add]

10/11/2006 5:10:25 PM

The output table SHOULD RESEMBLE:

A100       Jenkins
B222       JimmyJoe
C333       JeffBob
D102       Jeremy

10/11/2006 5:16:05 PM

You can't do it by the ID's like that.

You need to either do something linked on the name, or flag items in the non-members table as members by using an update query where PPL ID = MEM ID therfore indicating they are a member.

10/11/2006 6:32:54 PM

AHHHHH, Aaron, I think you may be on to it. I'm going to try that first thing when I get to work in the morning, I'll report back results.

Good to know you're still around-- you remember we lived in Lee with the burro, right?

haha, good times

10/11/2006 6:36:24 PM

Yeah, I remember that summer very well...
scared to sleep for fear of my asshole being rapped...

10/11/2006 6:39:25 PM