View Of Max Values From Many Tables

I have about 14 tables that I am trying to tie together in a view to
see a user's status in our company.

Joe User:
Email - Active
ADP - Active
Portal - Inactive
etc.

We need to track application access across the company, so we have
tables for each application that contain user info(username,
password(encrypted), start date, end date, changed date) so that we
can track who has what, and when they were given access as well as
when it was taken away.

Every application table has a userID attached to it.

What I would like to do is to take the userID field and look for their
application access across the company. To do this, i'll have to look
for the max value in each table because someone could be given access,
have it taken away, and be given it again. People move all over the
place here, so we have to be able to track who has what, when, and at
what building.

I started out with trying to left outer join the tables together, but
it didn't work. I tried doing something along the lines of:
select
esarfAppEmail.emailID,
esarfAppEmail.esarfUserID,
CASE WHEN esarfAppEmail.endDate IS NULL Then 'Active' else 'Inactive'
end as EmailStatus--,


Quote: