Firstly I'd strongly recommend you change the name of the Date field in your
table to something like ProjectDate. Date is the name of a built in function
which returns the current date, and should be avoided as an object name.
I assume from your example that the report is for one week, so base it on a
query with a parameter for the Week Commencing date (the Monday):
PARAMETERS [Week Commencing:] DATETIME;
SELECT DISTINCT
Vendor, Project,
FORMAT([Week Commencing:],"ddd m/d") AS Day1,
(SELECT Hours
FROM YourTable AS T2
WHERE T2.Vendor = T1.Vendor
AND T2.Project = T1.Project
AND T2.ProjectDate = [Week Commencing:]) AS Day1Hours,
FORMAT([Week Commencing:]+1,"ddd m/d") AS Day2,
(SELECT Hours
FROM YourTable AS T3
WHERE T3.Vendor = T1.Vendor
AND T3.Project = T1.Project
AND T3.ProjectDate = [Week Commencing:]+1) AS Day2Hours,
FORMAT([Week Commencing:]+2,"ddd m/d") AS Day3,
(SELECT Hours
FROM YourTable AS T4
WHERE T4.Vendor = T1.Vendor
AND T4.Project = T1.Project
AND T4.ProjectDate = [Week Commencing:]+2) AS Day3Hours,
FORMAT([Week Commencing:]+3,"ddd m/d") AS Day4,
(SELECT Hours
FROM YourTable AS T5
WHERE T5.Vendor = T1.Vendor
AND T5.Project = T1.Project
AND T5.ProjectDate = [Week Commencing:]+4) AS Day4Hours,
FORMAT([Week Commencing:]+4,"ddd m/d") AS Day5,
(SELECT Hours
FROM YourTable AS T6
WHERE T6.Vendor = T1.Vendor
AND T6.Project = T1.Project
AND T6.ProjectDate = [Week Commencing:]+4) AS Day5Hours,
FORMAT([Week Commencing:]+5,"ddd m/d") AS Day6,
(SELECT Hours
FROM YourTable AS T7
WHERE T7.Vendor = T1.Vendor
AND T7.Project = T1.Project
AND T7.ProjectDate = [Week Commencing:]+5) AS Day6Hours,
FORMAT([Week Commencing:]+6,"ddd m/d") AS Day7,
(SELECT Hours
FROM YourTable AS T8
WHERE T8.Vendor = T1.Vendor
AND T8.Project = T1.Project
AND T8.ProjectDate = [Week Commencing:]+6) AS Day7Hours
FROM YourTable As T1;
Group the report by Project. Its then simply a case of putting a text box
bound to the Project column in the group header and laying out the controls
bound to the Vendor, Day# and Day#Hours columns returned by the query 0n two
lines in the report's detail section. The Total for the week would be an
unbound text box with a ControlSource of:
=Day1Hours + Day2Hours + Day3Hours + Day4Hours + Day5Hours + Day6Hours +
Day7Hours
Alternative methods to the above would be to join 7 instances of the table
in a query rather than using subqueries, refencing the parameter in the join
criteria so that each instance of the table returns the hours for one day of
the week; to use the DLookup function in place of each subquery; or to base
the report on a simple query which returns DISTINCT rows for the Vendor and
Project for the week in question and to compute the hours in unbound controls
in the report with the DLookup function.
Ken Sheridan
Stafford, England
