Joining data from 6 tables so I can produce a report.

[Adam Armstrong]

Hi all

I have a problem in that I am trying to produce a report showing how
much holiday people are entitled to, how much they have taken, and also
the same for time In Lieu. I have 6 tables, called:

EmployeeDetails (Personal Details)
EmploymentDetails (Salary, hours etc)
HolidayEntitlement (Hours entitled to per year)
HolidayTaken (Records of each period taken)
InLieuAccrued (Records of each period accrued)
InLieuTaken (Records of each period taken)

Each table has an EmployeeID as a key. I am finding is that I can get
all of the information for the first three tables together just by
selecting the tables when creating a report. The problem comes when I
start trying to include data from the remaining three, as some employees
have not taken any holiday or accrued/taken any In Lieu, so it doesn't
include their records for the report. I have created three queries
which gather the information from the last three tables so that the only
fields I have left is the EmployeeID and a sum of HolidayTaken (or
InLieuAccrued etc) so that I don't get lots of duplicate information for
each time someone books a holiday. I think the problem I have is that
there isn't even a Null value to "look" for, as the records are there to
search, and so the employees with no InLieu are not selected.

I have created a form with Labels that calculate the details, but
obviously I can't create a report from a form (unless I can of course
and I just don't know how!) The only other option I was thinking about
is storing the calculations as fields in a table instead of having them
as labels on the form, but this seems to go against the grain from what
I read on this newsgroup.

I am a beginner to Access (although learning fast) so if anyone has any
ideas, I would be very grateful.

Thanks

Adam

 

[Jeff Boyce]

Adam

Open a query in design mode.

Add all the tables containing data you wish, and be sure the "Employee"
table is there.

Drag the connections between the Employee table and each of the others
(?EmployeeID?).

Now, highlight each connection/join, one at a time, and change its
properties so that it returns ALL of the (qualifying) Employee records, and
ANY of the related records from the connected table.

When you are done you will have lines with arrow heads pointing from
Employee to the other tables.

Now add the fields you'd like to see.

For those folks who have no "In Lieu" records, you'll still see the Employee
and any other data they do have.

 

[Adam Armstrong]

Jeff,

Worked perfectly. Thank you!

Adam