Prev: Query Setup with Two Tables
Next: Report
From: dtoney on 21 May 2010 22:37
can you append the results of several crosstab queries into 1 table?

I'm calc different categories & customers and need to capture "hours" for
each area for several different categories:

Estimates
received - on time? avg days to certify
rejected
certified

Scheduling
received - on time? avg days to certify
rejected
certified

If I could append the results of these several categories into one table, I
could export into Excel, produce my graphs, and go on about my business....

any ideas?
From: KARL DEWEY on 21 May 2010 23:16
I expect it can be done in one crosstab and one append query.

Post the SQL of your crosstabs.

--
Build a little, test a little.


"dtoney" wrote:

> can you append the results of several crosstab queries into 1 table?
>
> I'm calc different categories & customers and need to capture "hours" for
> each area for several different categories:
>
> Estimates
> received - on time? avg days to certify
> rejected
> certified
>
> Scheduling
> received - on time? avg days to certify
> rejected
> certified
>
> If I could append the results of these several categories into one table, I
> could export into Excel, produce my graphs, and go on about my business....
>
> any ideas?
From: dtoney on 27 May 2010 17:05
Need to take the weekly count of Under4Days and divide by Total Count by Week
for a %toTotal.

qryEstOperStats_Timely_Est_Diff_under4days_Count

TRANSFORM
Val(Nz(Count(qryEstOperStats_Timely_Est_Diff_under4days.WORK_REQ_NO),0)) AS
CountOfWORK_REQ_NO
SELECT qryEstOperStats_Timely_Est_Diff_under4days.AREA,
Val(Nz(Count(qryEstOperStats_Timely_Est_Diff_under4days.WORK_REQ_NO),0)) AS
[Total Of WORK_REQ_NO]
FROM qryEstOperStats_Timely_Est_Diff_under4days
GROUP BY qryEstOperStats_Timely_Est_Diff_under4days.AREA
PIVOT Format([Week],"YYYY/MM/DD");

qryEstOperStats_Timely_Est_Diff_Count

TRANSFORM
Round(Val(Nz(Count(qryEstOperStats_Timely_Est_Diff.WORK_REQ_NO),0)),0) AS
CountOfWORK_REQ_NO
SELECT qryEstOperStats_Timely_Est_Diff.AREA,
Round(Val(Nz(Count(qryEstOperStats_Timely_Est_Diff.WORK_REQ_NO),0)),0) AS
[Total Of WORK_REQ_NO]
FROM qryEstOperStats_Timely_Est_Diff
GROUP BY qryEstOperStats_Timely_Est_Diff.AREA
PIVOT Format([Week],"YYYY/MM/DD");

The reason I changed the date format to show YYYY first is so the crosstab
results will be in the correct order.


"KARL DEWEY" wrote:

> I expect it can be done in one crosstab and one append query.
>
> Post the SQL of your crosstabs.
>
> --
> Build a little, test a little.
>
>
> "dtoney" wrote:
>
> > can you append the results of several crosstab queries into 1 table?
> >
> > I'm calc different categories & customers and need to capture "hours" for
> > each area for several different categories:
> >
> > Estimates
> > received - on time? avg days to certify
> > rejected
> > certified
> >
> > Scheduling
> > received - on time? avg days to certify
> > rejected
> > certified
> >
> > If I could append the results of these several categories into one table, I
> > could export into Excel, produce my graphs, and go on about my business....
> >
> > any ideas?
 | 
Pages: 1
Prev: Query Setup with Two Tables
Next: Report