Show result whether in two tables or not

[dr4ke]

Morning,

I have two tables (Table1 'MBXStats' & Table2 'DisconnectedMBXs'). In order to establish whether a mailbox is disconnected, the two tables are compared and if the 'displayname' appears in both tables it's disconnected. However, I want to be able to see all mailboxes (disconnected or not) with a statement that advises its status...

I.e. I wrote a SQL statement (below) which works fine if I 'show the table' in design view; but it only shows those mailboxes in the DisconnectedMBXs table. If I remove the table in design view, then all entries appear duplicated (only 1 user shows).

 IIf(Not IsNull([DisconnectedMBXs].[disconnectdate]),1,0)

[RuralGuy]

You should be able to get the results you want with a Query and an outer join. No IIF needed.

[dr4ke]

Not sure how a Outer Join would work? Surely if I try and do any type of join it will only provide the information that is in both tables...

[orange]

For records in MBXStats and Not In DisconnectedMBXs

Select  mailboxId from MBXStats 
where mailboxId Not In
 (Select mailboxId From DisconnectedMBXs)

For records in both tables

Select  mailboxId from MBXStats 
where mailboxId  In
 (Select mailboxId From DisconnectedMBXs)

You will have to use your own field names for mailboxId -- whatever you called in each of the tables.

[RuralGuy]

Not sure how a Outer Join would work? Surely if I try and do any type of join it will only provide the information that is in both tables...

Outer Joins will show *all* the records in one table and only records in the other where the join field matches.

[dr4ke]

Thanks, I managed to get what I want:

First I brought both tables together

 SELECT *FROM allmailboxstats LEFT JOIN disconnectedmailboxes ON allmailboxstats.displayname=disconnectedmailboxes.displayname;

Then I did the working out:

 SELECT FullQueryTest.allmailboxstats.ID, FullQueryTest.allmailboxstats.DisplayName, FullQueryTest.OU, OULookup.[Business Unit], FullQueryTest.maxsendsize, FullQueryTest.prohibitsendreceivequota, FullQueryTest.HiddenFromAddressListsEnabled, FullQueryTest.IsValid, FullQueryTest.DisconnectDate, FullQueryTest.DisconnectReason, IIf(IsNull(DisconnectDate) And ((maxsendsize)<>"0 B (0 bytes)") And ((HiddenFromAddressListsEnabled)=No),1,0) AS Active, IIf(IsNull(DisconnectDate) And ((maxsendsize)="0 B (0 bytes)"),1,0) AS Disabled, IIf(IsNull(DisconnectDate) And ((allmailboxstats.HiddenFromAddressListsEnabled)=Yes) And ((allmailboxstats.maxsendsize)<>"0 B (0 bytes)"),1,0) AS Suspended, IIf(Not IsNull(DisconnectDate) And (((DisconnectDate)>=Now()-210) AND ((IsValid)=Yes)),1,0) AS Deleted
FROM (FullQueryTest INNER JOIN MailboxSizes ON FullQueryTest.prohibitsendreceivequota=MailboxSizes.Mailboxes) INNER JOIN OULookup ON FullQueryTest.OU=OULookup.OU;

The only problem is that there are duplicates, but I assume that's because the displayname is duplicated in the second table so therefore shows up twice in the query?