Crosstab query

[B BN B]

Hello All,

I am trying to perform a crosstab query and trying to figure out what to put in the row and column and value headings..

I am trying to find the average training hours per employee for 7-2009 to Present

So my fields are:
Agency UID-Need to know total employee
Contact Hours-Total contact hours and divide them
MonthYear

Any suggestions?

 

[vbaInet]

What is 7-2009?

What have you tried that isn't working? Did you follow a tutorial?

 

[B BN B]

7-2009 is month and year

Ive created the query and i ran it.. Agency UID which are user names...But there are duplicates which wouldnt give me an accurate number of employees (How would i remove duplicates?) I need to know the training hours per employee per month/year beginning from 7/2009-Present.



Brandie

 

[vbaInet]

Using the DISTINCT keyword:

http://www.techonthenet.com/sql/distinct.php

Save this as a separate query and base your Crosstab query on this query.

 

[B BN B]

How would I put it in Access? Im using Access 2007

 

[vbaInet]

It's explained in the link provided.

 

[sxschech]

To add the Distinct you will need to go to SQL view. Near the upper left corner of the main MS-Access Window (below the ribbon) is a button that says View and has a little black triangle under it. Click on the triangle and choose SQL view. From there you can type in the DISTINCT word as in the examples from the link. Alternatively, you can also right click on the query title bar and choose SQL view from there. Once you've added the DISTINCT, then you can right click on the query title bar again and choose Design view.

 

[B BN B]

Ok now I have the contact hours counted per employee..Now I need to find the Average number of Contact Hours per Employee. I did however do this in a crosstab query..Any suggestions?

 

[vbaInet]

We're making progress.

So you can have multiple Row Headings, one Column Heading and one Value in a crosstab query.

You set Avg in the Value field.

What have you tried?

 

[B BN B]

We're making progress.

So you can have multiple Row Headings, one Column Heading andone Value in a crosstab query.

You set Avg in the Value field.

What have you tried?

Hello, I was out on Friday so I wasn't able to reply.
I havent really tried anything...Do i need to start another Row because I only have 2 rows and if so what is that string for Avg?

 

[vbaInet]

I havent really tried anything...

Help me, help you! Give it a go and see how you get on:

http://support.microsoft.com/kb/304348
http://allenbrowne.com/ser-67.html

 

[B BN B]

Ok I ran the query trying to change it to Avg in the crosstab..i received this error Scaling of decimal value resulted in data truncation...Can you tell me what that means?

 

[vbaInet]

What is the data type of the field where you put Avg?

 

[B BN B]

the datatype is Number and I have decimals in that field

 

[vbaInet]

Can you not have a fixed number of decimal places? Are there some records where that field is empty or Null?

 

[B BN B]

Can you not have a fixed number of decimal places? Are there some records where that field is empty or Null?

I dont understand your first question. However there are no empty fields..The empty fields were replaced with .5

 

[vbaInet]

I will rephrase. Within your table, does the Number field always have a value? That is, it's never empty.

Also, try setting the Decimal Places to 2 and the Size to Fixed.