By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 921 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

Access Report

P: 6
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.
Jan 14 '08 #1
Share this Question
Share on Google+
11 Replies


Rabbit
Expert Mod 10K+
P: 12,357
You're looking for a crosstab query.
Jan 14 '08 #2

P: 6
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
Jan 14 '08 #3

Rabbit
Expert Mod 10K+
P: 12,357
What's the whole SQL?
Jan 14 '08 #4

P: 6
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.
Jan 14 '08 #5

Rabbit
Expert Mod 10K+
P: 12,357
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.
Jan 14 '08 #6

P: 6
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
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum([Resource Hours].Hours) AS SumOfHours
  2. SELECT [Resource Hours].[Employee Name]
  3. 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]
  4. WHERE ((([Resource Hours].[Week Ending]) Between [Start Sate] And [End Date]))
  5. GROUP BY [Resource Hours].[Employee Name]
  6. PIVOT [Resource Hours].[Week Ending];
  7.  
Jan 14 '08 #7

Rabbit
Expert Mod 10K+
P: 12,357
You'll have to define the parameters under Query > Parameters.
Jan 14 '08 #8

P: 6
You'll have to define the parameters under Query > Parameters.
Will that give me the ability to prompt the user?
Jan 14 '08 #9

Rabbit
Expert Mod 10K+
P: 12,357
Will that give me the ability to prompt the user?
Yes it will.
Jan 14 '08 #10

P: 6
Yes it will.
Thanks for your help this will work great.
Jan 14 '08 #11

Rabbit
Expert Mod 10K+
P: 12,357
Not a problem, good luck.
Jan 14 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.