Crosstab Report Totals Formatting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My query looks like this:

TRANSFORM Count(dbo_EMPINV_R.EMP_NO) AS CountOfEMP_NO
SELECT dbo_EMPINV_R.EMP_NO, dbo_EMPINV_R.EMP_NO
FROM dbo_EMPINV_R
GROUP BY dbo_EMPINV_R.EMP_NO, dbo_EMPINV_R.EMP_NO
PIVOT dbo_EMPINV_R.EMP_NO;

and formats like this:
1
2
3
4

however, I want it to look like this:
1 2 3 4

any solutions?
Thanks.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your query doesn't make any sense. Why do you want to put the count of
employees in a cross-tab, without setting up a "counts on..." column?
IOW, cross-tabs usually are for something like this: "Count the number
of employees who've sold widgets Right-Handed DoDad and Left-Handed
DoDad, between Jan 1, 2004 and Feb 28, 2004." This would require a
cross-tab like this:

TRANSFORM Count(Emp_No) As daCount
SELECT SalesDate, Count(*) As Total
FROM Sales
WHERE SalesDate Between #1/1/2004# And #2/28/2004#
AND Widgets_Name In ("Right-Handed DoDad", "Left-Handed DoDad")
GROUP BY SalesDate
PIVOT Widgets_Name

Which would give a result set like this:

SalesDate Right-Handed DoDad Left-Handed DoDad
1/1/2004 2 1
2/15/2004 3 2
.... etc. ...

If you just want the count of employees you would do something like
this:

SELECT Count(EMP_NO) As EmployeeCount
FROM dbo_EMPINV_R

So, what are you really trying to do?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYHmbYechKqOuFEgEQJU3wCfQZP6PBaEykaDdchvoou9FExfsTwAnRiZ
Ph2RvPpaAKMur45K9f5w9Fk/
=D7cA
-----END PGP SIGNATURE-----
 
Hi MG,

I'm retrieving EMP_NO totals from another crosstab query that is as:

TRANSFORM Count(dbo_EMPINV_R.FILE_NUMBER) AS InvCount
SELECT dbo_EMPINV_R.FILE_TYPE, dbo_EMPINV_R.INV_DATE,
Count(dbo_EMPINV_R.FILE_NUMBER) AS InvoiceCount
FROM dbo_EMPINV_R
GROUP BY dbo_EMPINV_R.FILE_TYPE, dbo_EMPINV_R.INV_DATE
ORDER BY dbo_EMPINV_R.INV_DATE
PIVOT dbo_EMPINV_R.EMP_NO;

and I need to derive totals from the EMP_NO, so when I tried
SELECT Count(EMP_NO) As EmployeeCount
FROM dbo_EMPINV_R, I received a total of all employee data, i.e., 2,010, but I need to total each EMP_NO's totals for each File_Type.
Click to expand...

So, for example, the original crosstab query results were:

File_Type INV_Date <> 27 29 30 34 (each as emp_no,
w/o <>)
ABS 08/02/04 4 5 2 1
REO 08/02/04 1 6 4 3

And I need:

<> 27 29 30
5 11 6 4

not 2,010 or in your syntax example, a total of 26...does this make sense?

thanks, P
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Purpose of query: Count the number of File_types for each employee, by
their emp_no.

Solution:

SELECT Emp_No, FILE_TYPE, Count(*) AS FileTypeCount
FROM dbo_EMPINV_R
GROUP BY Emp_No, File_type

Resultset example:

Emp_no File_Type FileTypeCount
27 ABS 5
27 REO 6
29 ABS 2
29 REO 4
30 ABS 1
30 REO 3
.... etc. ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYLMIoechKqOuFEgEQL63wCg0S2Z7xLYYZ8mTdFo+k5Z1VzQfigAoOa5
zGa/bL7wRXCDH1OgugxYlv26
=WYtt
-----END PGP SIGNATURE-----

Hi MG,

I'm retrieving EMP_NO totals from another crosstab query that is as:

TRANSFORM Count(dbo_EMPINV_R.FILE_NUMBER) AS InvCount
SELECT dbo_EMPINV_R.FILE_TYPE, dbo_EMPINV_R.INV_DATE,
Count(dbo_EMPINV_R.FILE_NUMBER) AS InvoiceCount
FROM dbo_EMPINV_R
GROUP BY dbo_EMPINV_R.FILE_TYPE, dbo_EMPINV_R.INV_DATE
ORDER BY dbo_EMPINV_R.INV_DATE
PIVOT dbo_EMPINV_R.EMP_NO;

and I need to derive totals from the EMP_NO, so when I tried
SELECT Count(EMP_NO) As EmployeeCount



So, for example, the original crosstab query results were:

File_Type INV_Date <> 27 29 30 34 (each as emp_no,
w/o <>)
ABS 08/02/04 4 5 2 1
REO 08/02/04 1 6 4 3

And I need:

<> 27 29 30
5 11 6 4

not 2,010 or in your syntax example, a total of 26...does this make sense?
Click to expand...

< SNIP >
 
nice...thanks, I'll try that.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Purpose of query: Count the number of File_types for each employee, by
their emp_no.

Solution:

SELECT Emp_No, FILE_TYPE, Count(*) AS FileTypeCount
FROM dbo_EMPINV_R
GROUP BY Emp_No, File_type

Resultset example:

Emp_no File_Type FileTypeCount
27 ABS 5
27 REO 6
29 ABS 2
29 REO 4
30 ABS 1
30 REO 3
.... etc. ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYLMIoechKqOuFEgEQL63wCg0S2Z7xLYYZ8mTdFo+k5Z1VzQfigAoOa5
zGa/bL7wRXCDH1OgugxYlv26
=WYtt
-----END PGP SIGNATURE-----



< SNIP >
Click to expand...
 
Back
Top