Re: Joining data from 6 tables so I can produce a report.
- From: "Jeff Boyce" <JeffBoyce_IF@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 28 Jan 2006 05:36:30 -0800
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.
--
Regards
Jeff Boyce
<Office/Access MVP>
"Adam Armstrong" <a_p_s@xxxxxxxxxxxxx> wrote in message
news:43db6e5a$1_1@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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
.
- References:
- Joining data from 6 tables so I can produce a report.
- From: Adam Armstrong
- Joining data from 6 tables so I can produce a report.
- Prev by Date: Joining data from 6 tables so I can produce a report.
- Next by Date: Find the 3 largest entry's per customer
- Previous by thread: Joining data from 6 tables so I can produce a report.
- Next by thread: Find the 3 largest entry's per customer
- Index(es):
Relevant Pages
|
