Combining tables which may not have all matching employees

[Guest]

This may be a bit complicated, so bear with me if you can. I will understand
if nobody can help as this has kept myself and my colleague stumped for a
while.

We have different tables and queries from different systems all pulled
together into one Access database. Both of us are now using Access 2003
(company gave me a recent Office upgrade). There are four main aspects upon
which we are scoring employees: Attendance, Schedule adherence, Quality, and
average call handle time. The idea is we'd like to be able to pull data each
week on their performance from the past week. We have the time frame requests
all set up. We are prompted for the time frame and it works fine, we get only
the data from the selected time frame.

The problem we are having is that not all employees will show up in all four
of the categories. For example, if an employee is here, but works on a
special project instead of taking phone calls, they will show up in the
attendance report for the time frame, but will not even show up in the
adherence, quality, or average call handle time reports.

These reports are all pulled in from other sources. We cannot change them to
better fit our database. We are stuck with them. What then happens is when we
do a combined result to pull all four aspects together in one query, it dumps
anybody not showing up in all four aspects.

That is a problem because we want them to count towards their team for the
aspects in which they did show up. In the above example, we'd want said
employee to count towards the team's attendance average, but show as a null
for the other averages and therefore not count one way or the other. (The
alternative is to assign them a 0, which would unfairly pull their team
average down just because they did their job). So, in the query that just
shows employee stats (not the team leads) we'd want said employee to show up,
but have nulls everywhere except attendance.

What we get now is it just dumps them. We've tried using iff clauses, but
that has not helped. We don't really know what to say in the iff clause that
wouldn't require the employee to at least show up in the data.

Well, now I am slightly worried that the length of my post will cause folks
not to want to read it, but I really couldn't shorten it much without leaving
out important information about the issue. I hope one of you can help, but
I'm not even sure if it is possible to do what I am asking.

 

[Tom Ellison]

Dear Paul:

I'm not put off by the length of your post. It is at least clear.

I'm assuming you have a table that does list every employee. If you base
your queries on this table and then LEFT JOIN (instead of INNER JOIN) to the
tables with the different specifics, then all employees will appear in each
query. Those employees without information in any of the tables with these
specifics will still appear, but the values coming from those tables of
specifics will be null.

This can be specified in the query design GUI as well, by bringing up the
list of join types. Right click on the line representing the JOIN between
the appropriate pair of tables.

 

[John W. Vinson/MVP]

These reports are all pulled in from other sources. We cannot change them

to
better fit our database. We are stuck with them. What then happens is when
we
do a combined result to pull all four aspects together in one query, it
dumps
anybody not showing up in all four aspects.

Open your query in design view. Select each of the Join lines in turn (I'm
assuming you're joining the employee table to four related tables; if not...
do so!). Change the join properties on the window that comes up to Option 2
(or 3): "Show all records in Employees and matching records in Attendance"
for example.

This is called an "outer join" and will let you display records even if
there is data missing in one or more tables.

John W. Vinson/MVP