Is Entry In Two Tables?

[dr4ke]

Afternoon all - I'm really getting frustrated at how difficult it is to figure this out (!!).



I have two tables:

Table1: AllMailboxStats

Table2: DisconnectedMailboxes

I want to return 6 columns from Table1 (including 'DisplayName') and 2 columns from Table2 (returning results based on 'DisplayName').

What I would like is for the Query to search and if Table1.DisplayName=Table2.DisplayName then display 'TRUE' else return 'FALSE'. But every time I get close to this, it only pulls out the entries that are in both Table1 and Table2 (i.e. 472 entries, which all show TRUE as opposed to 5000 entries which have a combination of TRUE & FALSE).

One of the examples I tried was below, which didn't work - I presume because I Inner Joined them. But If I don't Inner Join it wont see Table2.

 SELECTiif([allmailboxstats].[displayname]=[disconnectedmailboxes].[displayname],"Deleted","")
FROM allmailboxstats
INNER JOIN disconnectedmailboxes
ON allmailboxstats.displayname = disconnectedmailboxes.displayname

[alansidman]

Why not run an unmatched query using the query wizard. This will show you all the cases where the displaynames do not match. Your current SQL statement will only give you cases where the displaynames match because you are joining on those fields.

Alan

[dr4ke]

Because this will only return those that don't match? I wan't a Query which shows those that do, and those that don't? Not just one or the other.

[alansidman]

Perhaps, if you posted some sample data. (upload a copy of your data base with a sample of the records, not all 5000) we could analyze and determine a proper solution for you. I do believe that you will need to run several queries and ultimately if you want all the data in one query run a union query. I do not believe that you can get this with one query.