How do I create a report in Access when one table has less info?

[Guest]

I have two tables with similar data. One table, however, is missing data the
other has. In this case, stats for employees who were here one week, then
quit or were fired before the next week. I am trying to create a report
showing trends for employees. In other words, how their stats were in week 1
vs. how their stats were in week 2.

The problem is, when I do a query or report combining the two tables, it
just deletes employees not existing in week 2's data since it does not have
them to match up. Problem is, I need those employees to also be listed for
the report I am trying to do. We are trending by their supervisors, meaning
that we want to see all info from week 1 and all info from week 2 to see if a
specific supervisor's team improved overall, even if that improvement came as
result of the missing employee.

I have been unable to do this any way I tried. I tried doing two separate
queries, one query with all the information, etc. but no matter what I try,
it dumps any employees from the report not existing in both tables. Is there
any function to get it to instead figure them in for week 1, then as nulls
for week 2?

 

[Larry Linson]

Open the Query in the Query Builder, rightclick on the join line between the
two tables, and check "All the records in table ... and only those that
match in table....". If you also want the reverse, you will need multiple
queries connected via the UNION or UNION ALL operator in SQL (you can't do
UNION in the QueryBuilder grid view, but only in SQL view).

On the other hand, it sounds as if you have separate tables for each week --
that amounts to the table identification itself being data, is unnormalized,
and will cause you to waste time and effort and experience frustration. It
may, in fact, already be doing so, considering this question.

Larry Linson
Microsoft Access MVP

 

[Guest]

Thanks! Through your suggestion, and the ideas for how to do it I had, my
superiors thought it too complicated to be worth it. Thanks for the help. It
helped me prove my point to them that I had another opinion agreeing with me.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy