Find unmatched result query?

[omgjtt]

I am pretty new you access. <= one week : )

I have a database that contains two tables. Each table has two fields.

The data in the left column is the same for each table. It is a naming convention used for a campaign. Lets call that field tbl_Name. The second field in each table is used for tracking a campaign number. Lets call this table tbl_campaignNumber.

Ex:

Table 1

tbl_Name | tbl_campaignNumber

EVBB000 | 19232
EVBB000 | 19233
EVBB000 | 19234
EVBB000 | 19235


Table 2

tbl_Name | tbl_campaignNumber

EVBB000 | 19232
EVBB000 | 19233
EVBB000 | 19234
EVBB000 | 19236
EVBB000 | 19237
EVBB000 | 19238

The second table is a master list. It should have every campaign number. I am hoping someone can help me figure out how to write a query that will return campaign numbers that do not appear in table 2 but are inside table 1.

In the above example it would return - EVBB000 | 19235 since that is in table 1 but not inside table 2. I hope this makes sense : )

I have tried to do an unmatched query using the wizard and the result came back with nothing. Any help would be really appreciated.

Thanks in advance.

 

[John Big Booty]

If the above represents the reality of your DB object and field names the following should return the result you are after;

SELECT Table1.tbl_campaignNumber, Table1.tbl_Name
FROM Table1 LEFT JOIN Table2 ON Table1.tbl_campaignNumber = Table2.tbl_campaignNumber
WHERE (((Table2.tbl_campaignNumber) Is Null));

If you create a new blank query and put it into SQL view, you should be able to paste the above straight into that window, so long as all the name are as described in your OP.

 

[omgjtt]

Thanks John Big Booty,

That did the trick. Thank you very much. How long have you been working with access?

 

[omgjtt]

Also, one of my field names had a space and seemed to be causing problems. I trying adding [fieldname] around the field name and when I ran the query it looked for a parameter. I removed all spaces from my field name and it worked perfectly.

Just out of curiosity would it be possible to make the same query if there was a space in the field name? Thanks again

 

[John Big Booty]

About ten years.

Yes you should still be able to get your query to work with spaces in field names simply by enclosing the field name in square brackets, The fact that the query was looking for a parameter would tend to indicate that you had something not quiet right. However it's much simpler to avoid spaces and other special character in your field and object names. Something along the lines of TBL_TableName, FRM_FormName, QRY_QueryName, I find to be a good approach.