Combining 2 crosstab queries into 1 query: Help

[tekxzen]

Hello all,

I have a couple of crosstab queries (pend & process), and I want to
combine them into one query. The problem is, I haven't had any luck,
so I am in need of assistance.

Breakdown:
pend crosstab query has 14 records.
process crosstab query has 15 records.

The reason why pend query have 14 records is because one of the user
did not pend any; however, the user did process something and that's
why the process has more records.

** Also note that I have 6 tables, all of which are connected to each
other.
Employee Category
Worktypes
Usage Rates
JV Sample
IMG_Users
Touch Times

** Also note that there are 5 queries
Attendance
Error Total
pended
process
Worktype Productivity (This is the query that I am trying to put the
pended and process together)

Question:
I like to have it set up so that even the user did not pend anything,
it will still show up, but as "0" pend instead of not showing up at
all. How do I go about doing this type of scenario? Is it even
possible? The below SQL is what my Worktype Productivity.

TRANSFORM Count([JV sample].TransactionTMID) AS CountOfTransactionTMID
SELECT IMG_Users.Site, [Vacation and Absences].Team, [Vacation and
Absences].Position, IMG_Users.UserID, IMG_Users.[Last Name], IMG_Users.
[First Name], [JV sample].WRKTYPE, Count([JV sample].TransactionTMID)
AS [Total Process]

FROM ([Usage Rates] INNER JOIN ((Attendance INNER JOIN [Vacation and
Absences] ON Attendance.EmployeeID = [Vacation and
Absences].EmployeeID) INNER JOIN ([JV sample] INNER JOIN IMG_Users ON
[JV sample].USERID = IMG_Users.UserID) ON [Vacation and
Absences].EmployeeID = IMG_Users.UserID) ON [Usage Rates].UserID =
[Vacation and Absences].EmployeeID) INNER JOIN [Touch Times] ON [JV
sample].WRKTYPE = [Touch Times].Worktype

WHERE ((([JV sample].STATCD)="Totradauth" Or ([JV sample].STATCD)
="Totradaut1" Or ([JV sample].STATCD)="Totradproc" Or ([JV
sample].STATCD)="Toexceptn" Or ([JV sample].STATCD)="Tofttop2") AND
(([JV sample].[Pend(Y/N)])="no"))

GROUP BY IMG_Users.Site, [Vacation and Absences].Team, [Vacation and
Absences].Position, IMG_Users.UserID, IMG_Users.[Last Name], IMG_Users.
[First Name], [JV sample].WRKTYPE, [JV sample].WRKTYPE, [Usage Rates].
[IMG Usage %]

ORDER BY IMG_Users.Site, [Vacation and Absences].Position DESC ,
IMG_Users.[Last Name]
PIVOT [JV sample].STATCD;

Any suggestions is greatly appreciated.

Thanks,

 

[Duane Hookom]

I would rather not attempt to understand all your data and tables. However,
there is a solution for creating crosstabs with multiple values at
http://www.tek-tips.com/faqs.cfm?fid=4524.