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

  • Thread starter Thread starter Adam Armstrong
  • Start date Start date
A

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
 
Adam

Already responded to in your post in a different group. If you feel it
necessary to post in more than one group (rarely is), please add all groups
to your Newsgroups: address line when you post a single post. That way, the
response in one group shows in all.
 
Back
Top