Reports - confusion over linking or is it impossible to create a report like this?

[mommyof4kids]

I have a table with pay dates (dates are unique)

I have another table listing the total number of hours available for sick, vacation, etc...

I have another table with each employees name, number of hours worked and the date they worked

On my report I am listing:

Date worked Employee Hours worked

I also need to list for this date worked the total number of hours available for sick, vacation, etc. for that particular date (not that particular employee) (kind of like a side by side report but I would like the dates to line up)



01/01/11 John Brown 40.0 5 6 7

however when I run my queries/reports it always returns duplication values on the number of hours available for sick, vacation, etc.. like:

01/01/11 John Brown 40.0 5 6 7
01/01/11 Sara Smith 40.0 5 6 7
01/01/11 Besty Blue 40.0 5 6 7

(in other words, if there are three entries for the same date, it duplicates the totals for the other columns I need to show....I just want to show for these 3 employees the available hours (5 6 7) 1 time like: (total hours available for all employees who worked that particular date)


01/01/11 John Brown 40.0 5 6 7
01/01/11 Sara Smith 40.0
01/01/11 Besty Blue 40.0

01/15/11 John Brown 40.0 9 3 4
01/15/11 Sara Smith 40.0
01/15/11 Besty Blue 40.0


Am I trying to combine too much information on the same report? I really to show it this way on the report for my manager.


(I cannot send you the database, it is private. I have changed some of the details above but same concept)

[alansidman]

Post your SQL Statement so that we can see what is happening in the Query.

[mommyof4kids]

SELECT Initials_qry.Initials, Initials_qry.LName, Initials_qry.FName, Initials_qry.MI, tbl_OTTracking.PP, tbl_OTTracking.[Effective PP], tbl_OTTracking.[Area Worked?], tbl_OTTracking.[OT Hours], tbl_OTTracking.[Total Pay], tbl_OTTracking.Justification, tbl_OTTracking.[OT Sched on Pub Sched?], tbl_OTTracking.[Date of OT], tbl_OTTracking.[MPlex Hours], tbl_OTTracking.[Metroplex $], tbl_OTTracking.[Redesign Hrs], tbl_OTTracking.[NY/NJ/PHL Redesign], tbl_LeaveDetails.[#Detail], tbl_LeaveDetails.AL, tbl_LeaveDetails.[AL-FLMA], tbl_LeaveDetails.SL, tbl_LeaveDetails.[SL-FMLA], tbl_LeaveDetails.LWOP, tbl_LeaveDetails.AWOL, tbl_LeaveDetails.[EL - Court], tbl_LeaveDetails.Military, tbl_LeaveDetails.FAM, tbl_LeaveDetails.COS, tbl_LeaveDetails.XTE, tbl_LeaveDetails.XTU
FROM (Initials_qry INNER JOIN tbl_OTTracking ON Initials_qry.Initials = tbl_OTTracking.Initials) INNER JOIN tbl_LeaveDetails ON tbl_OTTracking.[Date of OT] = tbl_LeaveDetails.DateofOT
WHERE (((tbl_OTTracking.PP)=[For which payperiod? Enter as 2011-PP11]) AND ((tbl_OTTracking.[Area Worked?])=[For which areas? (Area1, Area2, Area3, Area4, Area5)]));

[June7]

Can you provide project? Make copy, remove confidential data (address, phone, ssn), run Compact & Repair, attach to post, zip if large. Alternatively, upload to fileshare site such as box.com and post link to the file.

[mommyof4kids]

test.mdb


sample attached....

[June7]

I downloaded your project. The query in your post is not in the project. I tried to create it but it references so many fields that are not in the tables it is fruitless to try analysing. I can't get any records to return in that query. Why would OTTracking be related to LeaveDetails?