Crosstab Reports

[Scott Viney]

Duane Hookom,

I want to thank you for your help so far. I have now got the date form and
crosstab query listing the dates for a week at the top and the jobs with job
numbers down the side.

Have started to create the report so I can print out a timesheet. But am
stuck with how to reference the data for the details section.

Whenever I open the report it asks for the start date. When I do this, in
the field list I get the list of dates for the report as well as the other
fields.

If I add these dates from the field list to my report details section, i get
the hours worked value. But how do you get the values relative to the date
you type for the report when I have finished designing it. IE So its
dynamic like the query??

CHeers,

Scott V

 

[Duane Hookom]

Please share the SQL view of your crosstab.

 

[Scott Viney]

Ok, here is the SQL for the query.

PARAMETERS [Forms]![frmDate]![txtStartDate] DateTime;
TRANSFORM Sum(tblTimeSheet.BillableHours) AS SumOfBillableHours
SELECT tblProjects.ProjectName, tblProjects.ProjectNumber,
Sum(tblTimeSheet.BillableHours) AS [Total Of BillableHours]
FROM tblProjects INNER JOIN tblTimeSheet ON tblProjects.ProjectID =
tblTimeSheet.ProjectID
WHERE (((tblTimeSheet.BillingDate) Between [Forms]![frmDate]![txtStartDate]
And ([Forms]![frmDate]![txtStartDate]+6)))
GROUP BY tblProjects.ProjectName, tblProjects.ProjectNumber
PIVOT tblTimeSheet.BillingDate;

Many thanks,

Scott V

 

[Duane Hookom]

Try this
PARAMETERS [Forms]![frmDate]![txtStartDate] DateTime;
TRANSFORM Sum(tblTimeSheet.BillableHours) AS SumOfBillableHours
SELECT tblProjects.ProjectName, tblProjects.ProjectNumber,
Sum(tblTimeSheet.BillableHours) AS [Total Of BillableHours]
FROM tblProjects INNER JOIN tblTimeSheet ON tblProjects.ProjectID =
tblTimeSheet.ProjectID
WHERE (((tblTimeSheet.BillingDate) Between [Forms]![frmDate]![txtStartDate]
And ([Forms]![frmDate]![txtStartDate]+6)))
GROUP BY tblProjects.ProjectName, tblProjects.ProjectNumber
PIVOT "D" &
DateDiff("D",[Forms]![frmDate]![txtStartDate],tblTimeSheet.BillingDate)
IN ("D0","D1","D2","D3","D4","D5","D6");

--
Duane Hookom
MS Access MVP

Ok, here is the SQL for the query.

PARAMETERS [Forms]![frmDate]![txtStartDate] DateTime;
TRANSFORM Sum(tblTimeSheet.BillableHours) AS SumOfBillableHours
SELECT tblProjects.ProjectName, tblProjects.ProjectNumber,
Sum(tblTimeSheet.BillableHours) AS [Total Of BillableHours]
FROM tblProjects INNER JOIN tblTimeSheet ON tblProjects.ProjectID =
tblTimeSheet.ProjectID
WHERE (((tblTimeSheet.BillingDate) Between
[Forms]![frmDate]![txtStartDate]
And ([Forms]![frmDate]![txtStartDate]+6)))
GROUP BY tblProjects.ProjectName, tblProjects.ProjectNumber
PIVOT tblTimeSheet.BillingDate;

Many thanks,

Scott V

Please share the SQL view of your crosstab.