Crosstab Reports

  • Thread starter Thread starter Scott Viney
  • Start date Start date
S

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
 
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
 
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


Scott Viney said:
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 said:
Please share the SQL view of your crosstab.
Click to expand...
Click to expand...
 
Back
Top