Report Question

[Alliance15]

I am trying to build a report and I need some help:

I am doing a man-hour report with the following info:
Project
Vendor
Date
Hours

I would like the format to be fairly similiar to an excel spreadsheet.
Project: ABC
Vendor Mon 1/1|Tue 1/2|Wed 1/3|Thu 1/4|Fri 1/5|Sat 1/6|Sun 1/7|Total|
XYZ 20 30 20 20 10
0 0 100
MNO 10 20 10 10 10
60

Like that...

I enter the date into a form like so:
Vendor|Project|Date|Hours|
XYZ ABC 1/1 20
MNO ABC 1/1 10

How can I manipulate things to make the report I want?

 

[KARL DEWEY]

Use the crosstab below and put project in group header.
TRANSFORM Sum(Alliance15.Hours) AS SumOfHours
SELECT Alliance15.Project, Alliance15.Vendor
FROM Alliance15
GROUP BY Alliance15.Project, Alliance15.Vendor
PIVOT Format([Date],"m/d ddd");

 

[Ken Sheridan]

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

 

[Ken Sheridan]

Note that if you use a crosstab query as Karl suggests you have to manipulate
the report definition at runtime to allow for the different column headings
returned in different weeks. You can find an example in the Solutions
database originally distributed with Access 97, which can now be downloaded
from:


http://www.mvps.org/access/resources/downloads.htm


Ken Sheridan
Stafford, England