Access Report

[Johna4969]

I need to create a report using data in a date field as a header. The field is a week ending date with a corisponding hours worked for an employee. The report needs to list the fields as follows;

Week Ending 01/05/08 01/12/08 01/19/08 etc
John Doe 40 40 25 etc
Jane Doe 45 20 40 etc

I have seen some posts about doing this with a query but no clear explanation on how to do it.

Any help would be appreciated.

[Rabbit]

You're looking for a crosstab query.

[Johna4969]

You're looking for a crosstab query.

OK I built a cross tab query now I need to be able to prompt the user for a date range on the week ending but the Between expression comes back with an error

Between [Start Date] And [End Date]

The Microsoft Jet database does not recognize "[Start Date]' as a valid field

[Rabbit]

What's the whole SQL?

[Johna4969]

What's the whole SQL?

Not sure what your looking for this is an Access DB and I am using the expression on the same field I am making column headers with.

[Rabbit]

In the query builder, right-click the title bar of the query builder window and select SQL view. Copy and paste what's in there.

[Johna4969]

In the query builder, right-click the title bar of the query builder window and select SQL view. Copy and paste what's in there.

Sorry Rookie Here it is

TRANSFORM Sum([Resource Hours].Hours) AS SumOfHours
SELECT [Resource Hours].[Employee Name]
FROM ([Shop Codes] INNER JOIN [Dep Code] ON [Shop Codes].[Shop Code] = [Dep Code].[Shop Code]) INNER JOIN ([Work Center] INNER JOIN ([Resource Detail] INNER JOIN [Resource Hours] ON [Resource Detail].[Employee Name] = [Resource Hours].[Employee Name]) ON [Work Center].[Work Center] = [Resource Detail].[Work Center]) ON [Dep Code].[Dep Code] = [Work Center].[Dep Code]
WHERE ((([Resource Hours].[Week Ending]) Between [Start Sate] And [End Date]))
GROUP BY [Resource Hours].[Employee Name]
PIVOT [Resource Hours].[Week Ending];
 

[Rabbit]

You'll have to define the parameters under Query > Parameters.

[Johna4969]

You'll have to define the parameters under Query > Parameters.

Will that give me the ability to prompt the user?

[Rabbit]

Will that give me the ability to prompt the user?

Yes it will.

[Johna4969]

Yes it will.

Thanks for your help this will work great.

[Rabbit]

Not a problem, good luck.