Hi All
I have Application on MS-access to manage the job orders which are design 
by engineers , i have table for job orders which include the date of 
design finish by engineer , how can i create crosstab query to display 
the monthly productivity of engineers :
Row heading of query should be engineers
Column heading of query should be 12 month of year
value should be the total job orders which are done by engineer in each 
month
My problem was in column heading when i choose it as a date of design 
finish the query show all date in the table as a column but i want just 
monthly productivity 
I hope i explain my problem clearly 
please anyone can help me
Best Regards 

Hi Waheed,

Your xtab SQL statement could be:

TRANSFORM
    Count(qryDesignsByDateAndEngineer.DesignID) AS CountOfDesignID
SELECT
     qryDesignsByDateAndEngineer.EngineerName
FROM
     qryDesignsByDateAndEngineer
GROUP BY
     qryDesignsByDateAndEngineer.EngineerName
PIVOT
     Format([DateDesignFinish],"mmm")
IN("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


The SQL statment for the query I based the xtab on is:

SELECT
    [tblEngineers].LastName & ", " & [FirstName] AS EngineerName,
    [tblJobs].DateDesignFinish,
    [tblJobs].DesignID
FROM
    tblEngineers
INNER JOIN
    tblJobs
ON
    [tblEngineers].EmployeeID=[tblJobs].EmployeeID;

Hope that works for you.

Best,

Bob



>From: "Waheed Al-zaydi" <aazydi@h...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Big problem with  crosstab query
>Date: Thu, 19 Dec 2002 22:40:02
>
>Hi All
>I have Application on MS-access to manage the job orders which are design
>by engineers , i have table for job orders which include the date of
>design finish by engineer , how can i create crosstab query to display
>the monthly productivity of engineers :
>Row heading of query should be engineers
>Column heading of query should be 12 month of year
>value should be the total job orders which are done by engineer in each
>month
>My problem was in column heading when i choose it as a date of design
>finish the query show all date in the table as a column but i want just
>monthly productivity
>I hope i explain my problem clearly
>please anyone can help me
>Best Regards


_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 3 months FREE*. 
http://join.msn.com/?page=features/featuredemail&xAPID=42&PS=47575&PI=7324&DI=7474&SU= 
http://www.hotmail.msn.com/cgi-bin/getmsg&HL=1216hotmailtaglines_addphotos_3mf

We do this all the time for our reports.  Instead of Date as the column
heading, use Format([DateOfDesign], "mm") as the column heading.

Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx

-----Original Message-----
From: Waheed Al-zaydi [mailto:aazydi@h...]
Sent: Thursday, December 19, 2002 16:40
To: Access
Subject: [access] Big problem with crosstab query

Hi All
I have Application on MS-access to manage the job orders which are 
design
by engineers , i have table for job orders which include the date of
design finish by engineer , how can i create crosstab query to display
the monthly productivity of engineers :
Row heading of query should be engineers
Column heading of query should be 12 month of year
value should be the total job orders which are done by engineer in each
month
My problem was in column heading when i choose it as a date of design
finish the query show all date in the table as a column but i want just
monthly productivity
I hope i explain my problem clearly
please anyone can help me
Best Regards