Sorry about the delay in responding.
I have no solution for your problem. You might look at building additional
queries for the other information and then linking the queries together by
some unique field(s). OR you might look at using sub-reports in your main
report.
It kind of depends on how you wish to display the data in the report.
If you do use queries linked together you are probably going to need to
ensure that the queries return data for the same set of employees. If you
don't you could end up dropping employees from the report. That could mean
that yo need to change your INNER JOINS to LEFT (or Right) JOINS in order to
ensure that each employee is returned. The problem here is that if you
apply criteria to the table on the right of a left join then you negate the
left join. You would need to use a subquery to return the records or nest
queries.
Query One saved as qOT
PARAMETERS Weekendingdate DateTime;
SELECT EmpID, LeaveGranted, OTDLDate
FROM OverTimeTracking
WHERE Authorized = 4 and
OTDLDate
Between DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])
and DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])+6
Query Two uses query one in place of the OvertimeTracking table
TRANSFORM Max(qOT.LeaveGranted) AS MaxOfLeaveGranted
SELECT Employees.SSI
FROM Employees LEFT JOIN qOT
ON Employees.SSI = qOT.EmpID
GROUP BY Employees.SSI
PIVOT Format(OTDLDate,"ddd") IN
("Sat", "Sun", "Mon", "Tue","Wed","Thu","Fri")
Now you need to do something similar for the other sets of data you need
and FINALLY you would need to combine the three queries you have created
into one with
SELECT qWeek.SSI,
FROM (qWeek INNER JOIN qQtrToDate
ON qWeek.SSI = qQtrToDate.SSI)
INNER JOIN qPrior ON
qWeek.SSI = qPrior.SSI
There may be something simpler, but I am not seeing it at present.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Nick said:
Thank you John, I do not know what I did incorrectly but I could not get
the
query to work after pasting what you gave me however, I pasted SQL into a
new query and it worked fine. As for your first question, I am trying to
create a query that will total figures prior to the week requsted of the
current quarter, the total for the week(which I have) and then totals for
quarter to date. I assumed I would need a different query to accomplish
this.
Do I build another query with the pramameter given or can I expand on what
I
have? The report will have to show amount of leave given in a quarter
prior
to the week requested and then total leave after the week has been
completed
I already have the week totals.
Again Thank You!.
John Spencer said:
First, you will have to clarify. A report can only have one query or
one table as its source. So how would you have multiple queries as the
source of a report?
I would expect to see a query that looke like
PARAMETERS Weekendingdate DateTime;
TRANSFORM Max(OvertimeTracking.LeaveGranted) AS MaxOfLeaveGranted
SELECT Employees.SSI
FROM Employees INNER JOIN OvertimeTracking
ON Employees.SSI = OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED)=4)) and OTDLDate
Between DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])
and DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])+6
GROUP BY Employees.SSI
PIVOT Format(OTDLDate,"ddd") IN
("Sat", "Sun", "Mon", "Tue","Wed","Thu","Fri")
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Nick wrote:
After pasting SQL in PIVOT, I am receiving Error 3142. This query one
of
sevral on a report, will have to change all to read as this one does?
PARAMETERS Weekendingdate DateTime;
TRANSFORM Max(OvertimeTracking.LeaveGranted) AS MaxOfLeaveGranted
SELECT Employees.SSI
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED)=4))
GROUP BY Employees.SSI, OvertimeTracking.aUTHORIZED
PIVOT "Lea" & DateDiff("d",[oTDLdaTE],[Weekendingdate]) In
("Lea0","Lea1","Lea2","Lea3","Lea4","Lea5","Lea6");
:
If you switch the crosstab query to SQL view you will see the actual
SQL
statement that the Design view builds. Copy that and paste it to a
posting and we can try to modify it.
Of course, you could try modifying it yourself in accordance with the
prior posting. If you do the work yourself, you are likely to retain
the technique and advance your skill level significantly.
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
