to display specific group by result in Crosstab query

H

hoachen

I am wondering, is there a way to display the result specifically on the Pub?
.. For example, i group all the same name catagory and then only those i want
to display after run.

Pub: Group by -> row heading (such as Pearson, Kaplan, ect)
Sub: Group by -> column heading
item#: count -> value
Total pub: count -> row heading
sub: where
 
K

KARL DEWEY

Need a little more information. Don't know what Pub, Sub, Item#, and Total
pub are.

Post your query SQL by opening in design view, Click on VIEW - SQL View,
highlight all. copy, and paste in a post.
 
H

hoachen

Below is the SQL view:
Right now, it shows all the Pub Name but I would like to specific which pub
name i want to show and then those i did not choose to show will be group
into "Other". I hope this help explain

TRANSFORM Count([INFORMATION].[ITEM#]) AS [CountOfITEM#]
SELECT [INFORMATION].Pub, Count([INFORMATION].Pub) AS Totals
FROM [INFORMATION]
WHERE ((([INFORMATION].Sub) Like "Vital") AND (([INFORMATION].Author) Not
Like "*[*]*")) OR ((([INFORMATION].Sub) Like "Courses")) OR
((([INFORMATION].Sub) Like "light"))
GROUP BY [INFORMATION].Pub
PIVOT [INFORMATION].Sub;

Let say, I want these shows:
Pub COURSES LIGHT VITAL Totals
ABC CLIO C 11 11
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
OTHER 1936 3 155 2094
TOTAL 3975 3 236 4214

Currently show like this:
Pub COURSES LIGHT VITALS Totals
ABC CLIO C 11 11
ACSI COURS 3 3
AMSCO S 92 92
BAKER+TAY 1 1
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
GUILFORD C 20 20
CENGAGE CO 1893 26 1919
HUMAN K CO 1 1
JONES+B CO 43 12 55
LIGHTNIN S 3 12902
 
K

KARL DEWEY

Your post had Vital & Vitals. Your SQL had Count and needed Sum.

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub;

It will take a little more work to get the total row at the bottom.
 
K

KARL DEWEY

I omitted to creat table named Info_Pub with list of Pub not deemed Other.
Create these three queries to have Totals row at the bottom --
hoachen_1
SELECT "Total" AS Pub_, INFORMATION.Sub, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY "Total", INFORMATION.Sub;

hoachen_2
SELECT Information.Pub, Information.Sub, Information.[ITEM#]
FROM Information
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
UNION ALL SELECT hoachen_1.Pub_, hoachen_1.Sub, hoachen_1.Totals
FROM hoachen_1;

TRANSFORM Sum([hoachen_2].[ITEM#]) AS [SumOfITEM#]
SELECT IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other") AS
Pub_, Sum([hoachen_2].[ITEM#]) AS Totals
FROM [hoachen_2] LEFT JOIN Info_Pub ON [hoachen_2].Pub=Info_Pub.Pub
GROUP BY IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other")
PIVOT [hoachen_2].Sub;

--
Build a little, test a little.


KARL DEWEY said:
Your post had Vital & Vitals. Your SQL had Count and needed Sum.

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub;

It will take a little more work to get the total row at the bottom.

--
Build a little, test a little.


hoachen said:
Below is the SQL view:
Right now, it shows all the Pub Name but I would like to specific which pub
name i want to show and then those i did not choose to show will be group
into "Other". I hope this help explain

TRANSFORM Count([INFORMATION].[ITEM#]) AS [CountOfITEM#]
SELECT [INFORMATION].Pub, Count([INFORMATION].Pub) AS Totals
FROM [INFORMATION]
WHERE ((([INFORMATION].Sub) Like "Vital") AND (([INFORMATION].Author) Not
Like "*[*]*")) OR ((([INFORMATION].Sub) Like "Courses")) OR
((([INFORMATION].Sub) Like "light"))
GROUP BY [INFORMATION].Pub
PIVOT [INFORMATION].Sub;

Let say, I want these shows:
Pub COURSES LIGHT VITAL Totals
ABC CLIO C 11 11
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
OTHER 1936 3 155 2094
TOTAL 3975 3 236 4214

Currently show like this:
Pub COURSES LIGHT VITALS Totals
ABC CLIO C 11 11
ACSI COURS 3 3
AMSCO S 92 92
BAKER+TAY 1 1
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
GUILFORD C 20 20
CENGAGE CO 1893 26 1919
HUMAN K CO 1 1
JONES+B CO 43 12 55
LIGHTNIN S 3 12902
Click to expand...
Click to expand...
 
H

hoachen

Thank you for your help! Now, i am confuse, why need to create a table named
"Info_pub"? All the data were in one table, i can't change that :(

So, the below queries will show the total in row? What about the statement
you wrote?

KARL DEWEY said:
I omitted to creat table named Info_Pub with list of Pub not deemed Other.
Create these three queries to have Totals row at the bottom --
hoachen_1
SELECT "Total" AS Pub_, INFORMATION.Sub, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY "Total", INFORMATION.Sub;

hoachen_2
SELECT Information.Pub, Information.Sub, Information.[ITEM#]
FROM Information
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
UNION ALL SELECT hoachen_1.Pub_, hoachen_1.Sub, hoachen_1.Totals
FROM hoachen_1;

TRANSFORM Sum([hoachen_2].[ITEM#]) AS [SumOfITEM#]
SELECT IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other") AS
Pub_, Sum([hoachen_2].[ITEM#]) AS Totals
FROM [hoachen_2] LEFT JOIN Info_Pub ON [hoachen_2].Pub=Info_Pub.Pub
GROUP BY IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other")
PIVOT [hoachen_2].Sub;

--
Build a little, test a little.


KARL DEWEY said:
Your post had Vital & Vitals. Your SQL had Count and needed Sum.

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub;

It will take a little more work to get the total row at the bottom.

--
Build a little, test a little.


hoachen said:
Below is the SQL view:
Right now, it shows all the Pub Name but I would like to specific which pub
name i want to show and then those i did not choose to show will be group
into "Other". I hope this help explain

TRANSFORM Count([INFORMATION].[ITEM#]) AS [CountOfITEM#]
SELECT [INFORMATION].Pub, Count([INFORMATION].Pub) AS Totals
FROM [INFORMATION]
WHERE ((([INFORMATION].Sub) Like "Vital") AND (([INFORMATION].Author) Not
Like "*[*]*")) OR ((([INFORMATION].Sub) Like "Courses")) OR
((([INFORMATION].Sub) Like "light"))
GROUP BY [INFORMATION].Pub
PIVOT [INFORMATION].Sub;

Let say, I want these shows:
Pub COURSES LIGHT VITAL Totals
ABC CLIO C 11 11
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
OTHER 1936 3 155 2094
TOTAL 3975 3 236 4214

Currently show like this:
Pub COURSES LIGHT VITALS Totals
ABC CLIO C 11 11
ACSI COURS 3 3
AMSCO S 92 92
BAKER+TAY 1 1
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
GUILFORD C 20 20
CENGAGE CO 1893 26 1919
HUMAN K CO 1 1
JONES+B CO 43 12 55
LIGHTNIN S 3 12902
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

The need for Info_pub is to use in the
IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
so that any Pub not in list is displayed as 'Other'. By th way you need
'Total' in it also.

One query totals and then is put in a union with query selecting orignal
data and finally a crosstab query.

--
Build a little, test a little.


hoachen said:
Thank you for your help! Now, i am confuse, why need to create a table named
"Info_pub"? All the data were in one table, i can't change that :(

So, the below queries will show the total in row? What about the statement
you wrote?

KARL DEWEY said:
I omitted to creat table named Info_Pub with list of Pub not deemed Other.
Create these three queries to have Totals row at the bottom --
hoachen_1
SELECT "Total" AS Pub_, INFORMATION.Sub, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY "Total", INFORMATION.Sub;

hoachen_2
SELECT Information.Pub, Information.Sub, Information.[ITEM#]
FROM Information
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
UNION ALL SELECT hoachen_1.Pub_, hoachen_1.Sub, hoachen_1.Totals
FROM hoachen_1;

TRANSFORM Sum([hoachen_2].[ITEM#]) AS [SumOfITEM#]
SELECT IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other") AS
Pub_, Sum([hoachen_2].[ITEM#]) AS Totals
FROM [hoachen_2] LEFT JOIN Info_Pub ON [hoachen_2].Pub=Info_Pub.Pub
GROUP BY IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other")
PIVOT [hoachen_2].Sub;

--
Build a little, test a little.


KARL DEWEY said:
Your post had Vital & Vitals. Your SQL had Count and needed Sum.

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub;

It will take a little more work to get the total row at the bottom.

--
Build a little, test a little.


:

Below is the SQL view:
Right now, it shows all the Pub Name but I would like to specific which pub
name i want to show and then those i did not choose to show will be group
into "Other". I hope this help explain

TRANSFORM Count([INFORMATION].[ITEM#]) AS [CountOfITEM#]
SELECT [INFORMATION].Pub, Count([INFORMATION].Pub) AS Totals
FROM [INFORMATION]
WHERE ((([INFORMATION].Sub) Like "Vital") AND (([INFORMATION].Author) Not
Like "*[*]*")) OR ((([INFORMATION].Sub) Like "Courses")) OR
((([INFORMATION].Sub) Like "light"))
GROUP BY [INFORMATION].Pub
PIVOT [INFORMATION].Sub;

Let say, I want these shows:
Pub COURSES LIGHT VITAL Totals
ABC CLIO C 11 11
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
OTHER 1936 3 155 2094
TOTAL 3975 3 236 4214

Currently show like this:
Pub COURSES LIGHT VITALS Totals
ABC CLIO C 11 11
ACSI COURS 3 3
AMSCO S 92 92
BAKER+TAY 1 1
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
GUILFORD C 20 20
CENGAGE CO 1893 26 1919
HUMAN K CO 1 1
JONES+B CO 43 12 55
LIGHTNIN S 3 12902
Click to expand...
Click to expand...
Click to expand...
 
H

hoachen

The...i thinking so slow!!!! new table for the pub that i want to match.
Genius...well, since you are so good with this, I have one more request to
this. Now, the query only match up the Pub(new table: “Info_pubâ€) with the
Pub at table “informationâ€. I also need the Pub from “Info_pub†table that
match the Sub at the “information†table and display it just like Pub.

For example:
The Pub (MCG also can be Sub)
The Sub (Pearson also can be Pub)

Information Table data
Item# Author Pub Sub
6633814 JOHNSTON OOSTING+A Vital
6633810 NORTHSTAR FRISTSTAR Vital
6633801 *NORTHSTAR FRISTSTAR Vital
6633670 SULLIVAN PEARSON Vital
66336666 SULLIVAN LIGHT PEARSON
6666630 Smith S+S Vital
6633650 *SPARKCHART SPARKNOTES Vital
66336466 *SPARKCHART SPARKNOTES Vital
6621640 BISTNER ELSEV Vital
66216366 AEHLERT ELSEV Vital
6671762 ABEL PEARSON Courses
6671760 HUSTED PEARSON Courses
6665577 HUSTED PEARSON Courses
6672248 PANDE Light MCG
66686631 DALRYMPLE Light AEGYPAN PR
6668666 CRAM 101 TXBK MCG Courses
66638066 STERN Light PENG
66528166 WILSON Light JOSSEY
6647830 HAYES Light PENG
66478266 TAPSCOTT Light PENG


KARL DEWEY said:
The need for Info_pub is to use in the
IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
so that any Pub not in list is displayed as 'Other'. By th way you need
'Total' in it also.

One query totals and then is put in a union with query selecting orignal
data and finally a crosstab query.

--
Build a little, test a little.


hoachen said:
Thank you for your help! Now, i am confuse, why need to create a table named
"Info_pub"? All the data were in one table, i can't change that :(

So, the below queries will show the total in row? What about the statement
you wrote?

KARL DEWEY said:
I omitted to creat table named Info_Pub with list of Pub not deemed Other.
Create these three queries to have Totals row at the bottom --
hoachen_1
SELECT "Total" AS Pub_, INFORMATION.Sub, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY "Total", INFORMATION.Sub;

hoachen_2
SELECT Information.Pub, Information.Sub, Information.[ITEM#]
FROM Information
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
UNION ALL SELECT hoachen_1.Pub_, hoachen_1.Sub, hoachen_1.Totals
FROM hoachen_1;

TRANSFORM Sum([hoachen_2].[ITEM#]) AS [SumOfITEM#]
SELECT IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other") AS
Pub_, Sum([hoachen_2].[ITEM#]) AS Totals
FROM [hoachen_2] LEFT JOIN Info_Pub ON [hoachen_2].Pub=Info_Pub.Pub
GROUP BY IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other")
PIVOT [hoachen_2].Sub;

--
Build a little, test a little.


:

Your post had Vital & Vitals. Your SQL had Count and needed Sum.

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub;

It will take a little more work to get the total row at the bottom.

--
Build a little, test a little.


:

Below is the SQL view:
Right now, it shows all the Pub Name but I would like to specific which pub
name i want to show and then those i did not choose to show will be group
into "Other". I hope this help explain

TRANSFORM Count([INFORMATION].[ITEM#]) AS [CountOfITEM#]
SELECT [INFORMATION].Pub, Count([INFORMATION].Pub) AS Totals
FROM [INFORMATION]
WHERE ((([INFORMATION].Sub) Like "Vital") AND (([INFORMATION].Author) Not
Like "*[*]*")) OR ((([INFORMATION].Sub) Like "Courses")) OR
((([INFORMATION].Sub) Like "light"))
GROUP BY [INFORMATION].Pub
PIVOT [INFORMATION].Sub;

Let say, I want these shows:
Pub COURSES LIGHT VITAL Totals
ABC CLIO C 11 11
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
OTHER 1936 3 155 2094
TOTAL 3975 3 236 4214

Currently show like this:
Pub COURSES LIGHT VITALS Totals
ABC CLIO C 11 11
ACSI COURS 3 3
AMSCO S 92 92
BAKER+TAY 1 1
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
GUILFORD C 20 20
CENGAGE CO 1893 26 1919
HUMAN K CO 1 1
JONES+B CO 43 12 55
LIGHTNIN S 3 12902
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I do not follow what you are asking.

After seeing your raw data I know Ierred in using Sum instead of Count in
two of the queries. Here are the corrected queries ---
hoachen_1
SELECT "Total" AS Pub_, INFORMATION.Sub, Count(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY "Total", INFORMATION.Sub;

hoachen_2
SELECT Information.Pub, Information.Sub, Count(Information.[ITEM#]) AS
ItemCount
FROM Information
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY Information.Pub, Information.Sub
UNION ALL SELECT hoachen_1.Pub_, hoachen_1.Sub, hoachen_1.Totals
FROM hoachen_1;

TRANSFORM Sum([hoachen_2].[ItemCount]) AS [SumOfItemCount]
SELECT IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other") AS
Pub_, Sum([hoachen_2].[ItemCount]) AS Totals
FROM hoachen_2 LEFT JOIN Info_Pub ON [hoachen_2].Pub=Info_Pub.Pub
GROUP BY IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other")
PIVOT [hoachen_2].Sub;

--
Build a little, test a little.


hoachen said:
The...i thinking so slow!!!! new table for the pub that i want to match.
Genius...well, since you are so good with this, I have one more request to
this. Now, the query only match up the Pub(new table: “Info_pubâ€) with the
Pub at table “informationâ€. I also need the Pub from “Info_pub†table that
match the Sub at the “information†table and display it just like Pub.

For example:
The Pub (MCG also can be Sub)
The Sub (Pearson also can be Pub)

Information Table data
Item# Author Pub Sub
6633814 JOHNSTON OOSTING+A Vital
6633810 NORTHSTAR FRISTSTAR Vital
6633801 *NORTHSTAR FRISTSTAR Vital
6633670 SULLIVAN PEARSON Vital
66336666 SULLIVAN LIGHT PEARSON
6666630 Smith S+S Vital
6633650 *SPARKCHART SPARKNOTES Vital
66336466 *SPARKCHART SPARKNOTES Vital
6621640 BISTNER ELSEV Vital
66216366 AEHLERT ELSEV Vital
6671762 ABEL PEARSON Courses
6671760 HUSTED PEARSON Courses
6665577 HUSTED PEARSON Courses
6672248 PANDE Light MCG
66686631 DALRYMPLE Light AEGYPAN PR
6668666 CRAM 101 TXBK MCG Courses
66638066 STERN Light PENG
66528166 WILSON Light JOSSEY
6647830 HAYES Light PENG
66478266 TAPSCOTT Light PENG


KARL DEWEY said:
The need for Info_pub is to use in the
IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
so that any Pub not in list is displayed as 'Other'. By th way you need
'Total' in it also.

One query totals and then is put in a union with query selecting orignal
data and finally a crosstab query.

--
Build a little, test a little.


hoachen said:
Thank you for your help! Now, i am confuse, why need to create a table named
"Info_pub"? All the data were in one table, i can't change that :(

So, the below queries will show the total in row? What about the statement
you wrote?

:

I omitted to creat table named Info_Pub with list of Pub not deemed Other.
Create these three queries to have Totals row at the bottom --
hoachen_1
SELECT "Total" AS Pub_, INFORMATION.Sub, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY "Total", INFORMATION.Sub;

hoachen_2
SELECT Information.Pub, Information.Sub, Information.[ITEM#]
FROM Information
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
UNION ALL SELECT hoachen_1.Pub_, hoachen_1.Sub, hoachen_1.Totals
FROM hoachen_1;

TRANSFORM Sum([hoachen_2].[ITEM#]) AS [SumOfITEM#]
SELECT IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other") AS
Pub_, Sum([hoachen_2].[ITEM#]) AS Totals
FROM [hoachen_2] LEFT JOIN Info_Pub ON [hoachen_2].Pub=Info_Pub.Pub
GROUP BY IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other")
PIVOT [hoachen_2].Sub;

--
Build a little, test a little.


:

Your post had Vital & Vitals. Your SQL had Count and needed Sum.

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub;

It will take a little more work to get the total row at the bottom.

--
Build a little, test a little.


:

Below is the SQL view:
Right now, it shows all the Pub Name but I would like to specific which pub
name i want to show and then those i did not choose to show will be group
into "Other". I hope this help explain

TRANSFORM Count([INFORMATION].[ITEM#]) AS [CountOfITEM#]
SELECT [INFORMATION].Pub, Count([INFORMATION].Pub) AS Totals
FROM [INFORMATION]
WHERE ((([INFORMATION].Sub) Like "Vital") AND (([INFORMATION].Author) Not
Like "*[*]*")) OR ((([INFORMATION].Sub) Like "Courses")) OR
((([INFORMATION].Sub) Like "light"))
GROUP BY [INFORMATION].Pub
PIVOT [INFORMATION].Sub;

Let say, I want these shows:
Pub COURSES LIGHT VITAL Totals
ABC CLIO C 11 11
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
OTHER 1936 3 155 2094
TOTAL 3975 3 236 4214

Currently show like this:
Pub COURSES LIGHT VITALS Totals
ABC CLIO C 11 11
ACSI COURS 3 3
AMSCO S 92 92
BAKER+TAY 1 1
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
GUILFORD C 20 20
CENGAGE CO 1893 26 1919
HUMAN K CO 1 1
JONES+B CO 43 12 55
LIGHTNIN S 3 12902
Click to expand...
Click to expand...
Click to expand...
 
H

hoachen

I am saying this query below, only pull the pub column and it does not pull
the sub. I want the pub and sub to pull if there is a match from Info_pub
table.

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub;





KARL DEWEY said:
I do not follow what you are asking.

After seeing your raw data I know Ierred in using Sum instead of Count in
two of the queries. Here are the corrected queries ---
hoachen_1
SELECT "Total" AS Pub_, INFORMATION.Sub, Count(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY "Total", INFORMATION.Sub;

hoachen_2
SELECT Information.Pub, Information.Sub, Count(Information.[ITEM#]) AS
ItemCount
FROM Information
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY Information.Pub, Information.Sub
UNION ALL SELECT hoachen_1.Pub_, hoachen_1.Sub, hoachen_1.Totals
FROM hoachen_1;

TRANSFORM Sum([hoachen_2].[ItemCount]) AS [SumOfItemCount]
SELECT IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other") AS
Pub_, Sum([hoachen_2].[ItemCount]) AS Totals
FROM hoachen_2 LEFT JOIN Info_Pub ON [hoachen_2].Pub=Info_Pub.Pub
GROUP BY IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other")
PIVOT [hoachen_2].Sub;

--
Build a little, test a little.


hoachen said:
The...i thinking so slow!!!! new table for the pub that i want to match.
Genius...well, since you are so good with this, I have one more request to
this. Now, the query only match up the Pub(new table: “Info_pubâ€) with the
Pub at table “informationâ€. I also need the Pub from “Info_pub†table that
match the Sub at the “information†table and display it just like Pub.

For example:
The Pub (MCG also can be Sub)
The Sub (Pearson also can be Pub)

Information Table data
Item# Author Pub Sub
6633814 JOHNSTON OOSTING+A Vital
6633810 NORTHSTAR FRISTSTAR Vital
6633801 *NORTHSTAR FRISTSTAR Vital
6633670 SULLIVAN PEARSON Vital
66336666 SULLIVAN LIGHT PEARSON
6666630 Smith S+S Vital
6633650 *SPARKCHART SPARKNOTES Vital
66336466 *SPARKCHART SPARKNOTES Vital
6621640 BISTNER ELSEV Vital
66216366 AEHLERT ELSEV Vital
6671762 ABEL PEARSON Courses
6671760 HUSTED PEARSON Courses
6665577 HUSTED PEARSON Courses
6672248 PANDE Light MCG
66686631 DALRYMPLE Light AEGYPAN PR
6668666 CRAM 101 TXBK MCG Courses
66638066 STERN Light PENG
66528166 WILSON Light JOSSEY
6647830 HAYES Light PENG
66478266 TAPSCOTT Light PENG


KARL DEWEY said:
The need for Info_pub is to use in the
IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
so that any Pub not in list is displayed as 'Other'. By th way you need
'Total' in it also.

One query totals and then is put in a union with query selecting orignal
data and finally a crosstab query.

--
Build a little, test a little.


:

Thank you for your help! Now, i am confuse, why need to create a table named
"Info_pub"? All the data were in one table, i can't change that :(

So, the below queries will show the total in row? What about the statement
you wrote?

:

I omitted to creat table named Info_Pub with list of Pub not deemed Other.
Create these three queries to have Totals row at the bottom --
hoachen_1
SELECT "Total" AS Pub_, INFORMATION.Sub, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY "Total", INFORMATION.Sub;

hoachen_2
SELECT Information.Pub, Information.Sub, Information.[ITEM#]
FROM Information
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
UNION ALL SELECT hoachen_1.Pub_, hoachen_1.Sub, hoachen_1.Totals
FROM hoachen_1;

TRANSFORM Sum([hoachen_2].[ITEM#]) AS [SumOfITEM#]
SELECT IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other") AS
Pub_, Sum([hoachen_2].[ITEM#]) AS Totals
FROM [hoachen_2] LEFT JOIN Info_Pub ON [hoachen_2].Pub=Info_Pub.Pub
GROUP BY IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other")
PIVOT [hoachen_2].Sub;

--
Build a little, test a little.


:

Your post had Vital & Vitals. Your SQL had Count and needed Sum.

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub;

It will take a little more work to get the total row at the bottom.

--
Build a little, test a little.


:

Below is the SQL view:
Right now, it shows all the Pub Name but I would like to specific which pub
name i want to show and then those i did not choose to show will be group
into "Other". I hope this help explain

TRANSFORM Count([INFORMATION].[ITEM#]) AS [CountOfITEM#]
SELECT [INFORMATION].Pub, Count([INFORMATION].Pub) AS Totals
FROM [INFORMATION]
WHERE ((([INFORMATION].Sub) Like "Vital") AND (([INFORMATION].Author) Not
Like "*[*]*")) OR ((([INFORMATION].Sub) Like "Courses")) OR
((([INFORMATION].Sub) Like "light"))
GROUP BY [INFORMATION].Pub
PIVOT [INFORMATION].Sub;

Let say, I want these shows:
Pub COURSES LIGHT VITAL Totals
ABC CLIO C 11 11
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
OTHER 1936 3 155 2094
TOTAL 3975 3 236 4214

Currently show like this:
Pub COURSES LIGHT VITALS Totals
ABC CLIO C 11 11
ACSI COURS 3 3
AMSCO S 92 92
BAKER+TAY 1 1
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
GUILFORD C 20 20
CENGAGE CO 1893 26 1919
HUMAN K CO 1 1
JONES+B CO 43 12 55
LIGHTNIN S 3 12902
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Add to the union query something like this ( not tested) --
UNION ALL SELECT INFORMATION.Sub AS Pub, INFORMATION.Sub,
Count(INFORMATION.[ITEM#]) AS Total
FROM INFORMATION INNER JOIN Info_Pub ON INFORMATION.Sub = Info_Pub.Pub
GROUP BY INFORMATION.Sub, INFORMATION.Sub;

--
Build a little, test a little.


hoachen said:
I am saying this query below, only pull the pub column and it does not pull
the sub. I want the pub and sub to pull if there is a match from Info_pub
table.

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub;





KARL DEWEY said:
I do not follow what you are asking.

After seeing your raw data I know Ierred in using Sum instead of Count in
two of the queries. Here are the corrected queries ---
hoachen_1
SELECT "Total" AS Pub_, INFORMATION.Sub, Count(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY "Total", INFORMATION.Sub;

hoachen_2
SELECT Information.Pub, Information.Sub, Count(Information.[ITEM#]) AS
ItemCount
FROM Information
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY Information.Pub, Information.Sub
UNION ALL SELECT hoachen_1.Pub_, hoachen_1.Sub, hoachen_1.Totals
FROM hoachen_1;

TRANSFORM Sum([hoachen_2].[ItemCount]) AS [SumOfItemCount]
SELECT IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other") AS
Pub_, Sum([hoachen_2].[ItemCount]) AS Totals
FROM hoachen_2 LEFT JOIN Info_Pub ON [hoachen_2].Pub=Info_Pub.Pub
GROUP BY IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other")
PIVOT [hoachen_2].Sub;

--
Build a little, test a little.


hoachen said:
The...i thinking so slow!!!! new table for the pub that i want to match.
Genius...well, since you are so good with this, I have one more request to
this. Now, the query only match up the Pub(new table: “Info_pubâ€) with the
Pub at table “informationâ€. I also need the Pub from “Info_pub†table that
match the Sub at the “information†table and display it just like Pub.

For example:
The Pub (MCG also can be Sub)
The Sub (Pearson also can be Pub)

Information Table data
Item# Author Pub Sub
6633814 JOHNSTON OOSTING+A Vital
6633810 NORTHSTAR FRISTSTAR Vital
6633801 *NORTHSTAR FRISTSTAR Vital
6633670 SULLIVAN PEARSON Vital
66336666 SULLIVAN LIGHT PEARSON
6666630 Smith S+S Vital
6633650 *SPARKCHART SPARKNOTES Vital
66336466 *SPARKCHART SPARKNOTES Vital
6621640 BISTNER ELSEV Vital
66216366 AEHLERT ELSEV Vital
6671762 ABEL PEARSON Courses
6671760 HUSTED PEARSON Courses
6665577 HUSTED PEARSON Courses
6672248 PANDE Light MCG
66686631 DALRYMPLE Light AEGYPAN PR
6668666 CRAM 101 TXBK MCG Courses
66638066 STERN Light PENG
66528166 WILSON Light JOSSEY
6647830 HAYES Light PENG
66478266 TAPSCOTT Light PENG


:

The need for Info_pub is to use in the
IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
so that any Pub not in list is displayed as 'Other'. By th way you need
'Total' in it also.

One query totals and then is put in a union with query selecting orignal
data and finally a crosstab query.

--
Build a little, test a little.


:

Thank you for your help! Now, i am confuse, why need to create a table named
"Info_pub"? All the data were in one table, i can't change that :(

So, the below queries will show the total in row? What about the statement
you wrote?

:

I omitted to creat table named Info_Pub with list of Pub not deemed Other.
Create these three queries to have Totals row at the bottom --
hoachen_1
SELECT "Total" AS Pub_, INFORMATION.Sub, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY "Total", INFORMATION.Sub;

hoachen_2
SELECT Information.Pub, Information.Sub, Information.[ITEM#]
FROM Information
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
UNION ALL SELECT hoachen_1.Pub_, hoachen_1.Sub, hoachen_1.Totals
FROM hoachen_1;

TRANSFORM Sum([hoachen_2].[ITEM#]) AS [SumOfITEM#]
SELECT IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other") AS
Pub_, Sum([hoachen_2].[ITEM#]) AS Totals
FROM [hoachen_2] LEFT JOIN Info_Pub ON [hoachen_2].Pub=Info_Pub.Pub
GROUP BY IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other")
PIVOT [hoachen_2].Sub;

--
Build a little, test a little.


:

Your post had Vital & Vitals. Your SQL had Count and needed Sum.

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub;

It will take a little more work to get the total row at the bottom.

--
Build a little, test a little.


:

Below is the SQL view:
Right now, it shows all the Pub Name but I would like to specific which pub
name i want to show and then those i did not choose to show will be group
into "Other". I hope this help explain

TRANSFORM Count([INFORMATION].[ITEM#]) AS [CountOfITEM#]
SELECT [INFORMATION].Pub, Count([INFORMATION].Pub) AS Totals
FROM [INFORMATION]
WHERE ((([INFORMATION].Sub) Like "Vital") AND (([INFORMATION].Author) Not
Like "*[*]*")) OR ((([INFORMATION].Sub) Like "Courses")) OR
((([INFORMATION].Sub) Like "light"))
GROUP BY [INFORMATION].Pub
PIVOT [INFORMATION].Sub;

Let say, I want these shows:
Pub COURSES LIGHT VITAL Totals
ABC CLIO C 11 11
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
OTHER 1936 3 155 2094
TOTAL 3975 3 236 4214

Currently show like this:
Pub COURSES LIGHT VITALS Totals
ABC CLIO C 11 11
ACSI COURS 3 3
AMSCO S 92 92
BAKER+TAY 1 1
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
GUILFORD C 20 20
CENGAGE CO 1893 26 1919
HUMAN K CO 1 1
JONES+B CO 43 12 55
LIGHTNIN S 3 12902
Click to expand...
Click to expand...
Click to expand...
 
H

hoachen

Do you mean after crosstab query and have the union? I tried it and it
"syntax error (missiong operator)

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub

UNION ALL SELECT INFORMATION.Sub AS Pub, INFORMATION.Sub,
Count(INFORMATION.[ITEM#]) AS Total
FROM INFORMATION INNER JOIN Info_Pub ON INFORMATION.Sub = Info_Pub.Pub
GROUP BY INFORMATION.Sub;

hoachen said:
I am saying this query below, only pull the pub column and it does not pull
the sub. I want the pub and sub to pull if there is a match from Info_pub
table.

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub;





KARL DEWEY said:
I do not follow what you are asking.

After seeing your raw data I know Ierred in using Sum instead of Count in
two of the queries. Here are the corrected queries ---
hoachen_1
SELECT "Total" AS Pub_, INFORMATION.Sub, Count(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY "Total", INFORMATION.Sub;

hoachen_2
SELECT Information.Pub, Information.Sub, Count(Information.[ITEM#]) AS
ItemCount
FROM Information
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY Information.Pub, Information.Sub
UNION ALL SELECT hoachen_1.Pub_, hoachen_1.Sub, hoachen_1.Totals
FROM hoachen_1;

TRANSFORM Sum([hoachen_2].[ItemCount]) AS [SumOfItemCount]
SELECT IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other") AS
Pub_, Sum([hoachen_2].[ItemCount]) AS Totals
FROM hoachen_2 LEFT JOIN Info_Pub ON [hoachen_2].Pub=Info_Pub.Pub
GROUP BY IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other")
PIVOT [hoachen_2].Sub;

--
Build a little, test a little.


:

The...i thinking so slow!!!! new table for the pub that i want to match.
Genius...well, since you are so good with this, I have one more request to
this. Now, the query only match up the Pub(new table: “Info_pubâ€) with the
Pub at table “informationâ€. I also need the Pub from “Info_pub†table that
match the Sub at the “information†table and display it just like Pub.

For example:
The Pub (MCG also can be Sub)
The Sub (Pearson also can be Pub)

Information Table data
Item# Author Pub Sub
6633814 JOHNSTON OOSTING+A Vital
6633810 NORTHSTAR FRISTSTAR Vital
6633801 *NORTHSTAR FRISTSTAR Vital
6633670 SULLIVAN PEARSON Vital
66336666 SULLIVAN LIGHT PEARSON
6666630 Smith S+S Vital
6633650 *SPARKCHART SPARKNOTES Vital
66336466 *SPARKCHART SPARKNOTES Vital
6621640 BISTNER ELSEV Vital
66216366 AEHLERT ELSEV Vital
6671762 ABEL PEARSON Courses
6671760 HUSTED PEARSON Courses
6665577 HUSTED PEARSON Courses
6672248 PANDE Light MCG
66686631 DALRYMPLE Light AEGYPAN PR
6668666 CRAM 101 TXBK MCG Courses
66638066 STERN Light PENG
66528166 WILSON Light JOSSEY
6647830 HAYES Light PENG
66478266 TAPSCOTT Light PENG


:

The need for Info_pub is to use in the
IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
so that any Pub not in list is displayed as 'Other'. By th way you need
'Total' in it also.

One query totals and then is put in a union with query selecting orignal
data and finally a crosstab query.

--
Build a little, test a little.


:

Thank you for your help! Now, i am confuse, why need to create a table named
"Info_pub"? All the data were in one table, i can't change that :(

So, the below queries will show the total in row? What about the statement
you wrote?

:

I omitted to creat table named Info_Pub with list of Pub not deemed Other.
Create these three queries to have Totals row at the bottom --
hoachen_1
SELECT "Total" AS Pub_, INFORMATION.Sub, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY "Total", INFORMATION.Sub;

hoachen_2
SELECT Information.Pub, Information.Sub, Information.[ITEM#]
FROM Information
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
UNION ALL SELECT hoachen_1.Pub_, hoachen_1.Sub, hoachen_1.Totals
FROM hoachen_1;

TRANSFORM Sum([hoachen_2].[ITEM#]) AS [SumOfITEM#]
SELECT IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other") AS
Pub_, Sum([hoachen_2].[ITEM#]) AS Totals
FROM [hoachen_2] LEFT JOIN Info_Pub ON [hoachen_2].Pub=Info_Pub.Pub
GROUP BY IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other")
PIVOT [hoachen_2].Sub;

--
Build a little, test a little.


:

Your post had Vital & Vitals. Your SQL had Count and needed Sum.

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub;

It will take a little more work to get the total row at the bottom.

--
Build a little, test a little.


:

Below is the SQL view:
Right now, it shows all the Pub Name but I would like to specific which pub
name i want to show and then those i did not choose to show will be group
into "Other". I hope this help explain

TRANSFORM Count([INFORMATION].[ITEM#]) AS [CountOfITEM#]
SELECT [INFORMATION].Pub, Count([INFORMATION].Pub) AS Totals
FROM [INFORMATION]
WHERE ((([INFORMATION].Sub) Like "Vital") AND (([INFORMATION].Author) Not
Like "*[*]*")) OR ((([INFORMATION].Sub) Like "Courses")) OR
((([INFORMATION].Sub) Like "light"))
GROUP BY [INFORMATION].Pub
PIVOT [INFORMATION].Sub;

Let say, I want these shows:
Pub COURSES LIGHT VITAL Totals
ABC CLIO C 11 11
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
OTHER 1936 3 155 2094
TOTAL 3975 3 236 4214

Currently show like this:
Pub COURSES LIGHT VITALS Totals
ABC CLIO C 11 11
ACSI COURS 3 3
AMSCO S 92 92
BAKER+TAY 1 1
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
GUILFORD C 20 20
CENGAGE CO 1893 26 1919
HUMAN K CO 1 1
JONES+B CO 43 12 55
LIGHTNIN S 3 12902
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Since the two queries must return the same number of fields and matching
types, it is difficult to write a union query using a crosstab.

You have to know How many columns will be generated by the PIVOT clause.

Also, your second query ay be incorrect as it stands since you probably
need to group on both instances of INFORMATION.Sub.

If you know how many columns are generated by the pivot you could change
the select to match that. For instance if the pivot generated three
columns, you could try this as the select clause of the second query.

SELECT INFORMATION.Sub AS Pub, INFORMATION.Sub,
Count(INFORMATION.[ITEM#]) AS Total
,First(0), First(0), First(0)
FROM ...

You also might want to include a column to allow you to determine which
rows have the regular totals versus which row is the grand total.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Do you mean after crosstab query and have the union? I tried it and it
"syntax error (missiong operator)

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub

UNION ALL SELECT INFORMATION.Sub AS Pub, INFORMATION.Sub,
Count(INFORMATION.[ITEM#]) AS Total
FROM INFORMATION INNER JOIN Info_Pub ON INFORMATION.Sub = Info_Pub.Pub
GROUP BY INFORMATION.Sub;

hoachen said:
I am saying this query below, only pull the pub column and it does not pull
the sub. I want the pub and sub to pull if there is a match from Info_pub
table.

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub;





:

I do not follow what you are asking.

After seeing your raw data I know Ierred in using Sum instead of Count in
two of the queries. Here are the corrected queries ---
hoachen_1
SELECT "Total" AS Pub_, INFORMATION.Sub, Count(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY "Total", INFORMATION.Sub;

hoachen_2
SELECT Information.Pub, Information.Sub, Count(Information.[ITEM#]) AS
ItemCount
FROM Information
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY Information.Pub, Information.Sub
UNION ALL SELECT hoachen_1.Pub_, hoachen_1.Sub, hoachen_1.Totals
FROM hoachen_1;

TRANSFORM Sum([hoachen_2].[ItemCount]) AS [SumOfItemCount]
SELECT IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other") AS
Pub_, Sum([hoachen_2].[ItemCount]) AS Totals
FROM hoachen_2 LEFT JOIN Info_Pub ON [hoachen_2].Pub=Info_Pub.Pub
GROUP BY IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other")
PIVOT [hoachen_2].Sub;

--
Build a little, test a little.


:

The...i thinking so slow!!!! new table for the pub that i want to match.
Genius...well, since you are so good with this, I have one more request to
this. Now, the query only match up the Pub(new table: “Info_pubâ€) with the
Pub at table “informationâ€. I also need the Pub from “Info_pub†table that
match the Sub at the “information†table and display it just like Pub.

For example:
The Pub (MCG also can be Sub)
The Sub (Pearson also can be Pub)

Information Table data
Item# Author Pub Sub
6633814 JOHNSTON OOSTING+A Vital
6633810 NORTHSTAR FRISTSTAR Vital
6633801 *NORTHSTAR FRISTSTAR Vital
6633670 SULLIVAN PEARSON Vital
66336666 SULLIVAN LIGHT PEARSON
6666630 Smith S+S Vital
6633650 *SPARKCHART SPARKNOTES Vital
66336466 *SPARKCHART SPARKNOTES Vital
6621640 BISTNER ELSEV Vital
66216366 AEHLERT ELSEV Vital
6671762 ABEL PEARSON Courses
6671760 HUSTED PEARSON Courses
6665577 HUSTED PEARSON Courses
6672248 PANDE Light MCG
66686631 DALRYMPLE Light AEGYPAN PR
6668666 CRAM 101 TXBK MCG Courses
66638066 STERN Light PENG
66528166 WILSON Light JOSSEY
6647830 HAYES Light PENG
66478266 TAPSCOTT Light PENG


:

The need for Info_pub is to use in the
IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
so that any Pub not in list is displayed as 'Other'. By th way you need
'Total' in it also.

One query totals and then is put in a union with query selecting orignal
data and finally a crosstab query.

--
Build a little, test a little.


:

Thank you for your help! Now, i am confuse, why need to create a table named
"Info_pub"? All the data were in one table, i can't change that :(

So, the below queries will show the total in row? What about the statement
you wrote?

:

I omitted to creat table named Info_Pub with list of Pub not deemed Other.
Create these three queries to have Totals row at the bottom --
hoachen_1
SELECT "Total" AS Pub_, INFORMATION.Sub, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY "Total", INFORMATION.Sub;

hoachen_2
SELECT Information.Pub, Information.Sub, Information.[ITEM#]
FROM Information
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
UNION ALL SELECT hoachen_1.Pub_, hoachen_1.Sub, hoachen_1.Totals
FROM hoachen_1;

TRANSFORM Sum([hoachen_2].[ITEM#]) AS [SumOfITEM#]
SELECT IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other") AS
Pub_, Sum([hoachen_2].[ITEM#]) AS Totals
FROM [hoachen_2] LEFT JOIN Info_Pub ON [hoachen_2].Pub=Info_Pub.Pub
GROUP BY IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other")
PIVOT [hoachen_2].Sub;

--
Build a little, test a little.


:

Your post had Vital & Vitals. Your SQL had Count and needed Sum.

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub;

It will take a little more work to get the total row at the bottom.

--
Build a little, test a little.


:

Below is the SQL view:
Right now, it shows all the Pub Name but I would like to specific which pub
name i want to show and then those i did not choose to show will be group
into "Other". I hope this help explain

TRANSFORM Count([INFORMATION].[ITEM#]) AS [CountOfITEM#]
SELECT [INFORMATION].Pub, Count([INFORMATION].Pub) AS Totals
FROM [INFORMATION]
WHERE ((([INFORMATION].Sub) Like "Vital") AND (([INFORMATION].Author) Not
Like "*[*]*")) OR ((([INFORMATION].Sub) Like "Courses")) OR
((([INFORMATION].Sub) Like "light"))
GROUP BY [INFORMATION].Pub
PIVOT [INFORMATION].Sub;

Let say, I want these shows:
Pub COURSES LIGHT VITAL Totals
ABC CLIO C 11 11
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
OTHER 1936 3 155 2094
TOTAL 3975 3 236 4214

Currently show like this:
Pub COURSES LIGHT VITALS Totals
ABC CLIO C 11 11
ACSI COURS 3 3
AMSCO S 92 92
BAKER+TAY 1 1
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
GUILFORD C 20 20
CENGAGE CO 1893 26 1919
HUMAN K CO 1 1
JONES+B CO 43 12 55
LIGHTNIN S 3 12902
Click to expand...
Click to expand...
Click to expand...
 
H

hoachen

Thank you for helping me about this. The query below is very close to what i
want, it just "display" and "count" the pub where the data like "vital,
course, and light" in Sub column BUT NOT "display" and "count" on the sub
where the data like "light" in Pub column. I hope this explain. Any idea how
i include the pub column which have "light" and pull the sub and display and
count them.

TRANSFORM Count(INFORMATION).[ITEM#]) AS [CountOfITEM#]
SELECT IIf(INFORMATION.Pub=Info_Pub.Pubs,INFORMATION.Pub,"OTHER") AS
PubName, Count(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pubs
WHERE (((INFORMATION.Sub)="Vital") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Course") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not Like
"*[*]*"))
GROUP BY IIf(INFORMATION.Pub=Info_Pub.Pubs,INFORMATION.Pub,"OTHER")
PIVOT INFORMATION.Sub;


John Spencer said:
Since the two queries must return the same number of fields and matching
types, it is difficult to write a union query using a crosstab.

You have to know How many columns will be generated by the PIVOT clause.

Also, your second query ay be incorrect as it stands since you probably
need to group on both instances of INFORMATION.Sub.

If you know how many columns are generated by the pivot you could change
the select to match that. For instance if the pivot generated three
columns, you could try this as the select clause of the second query.

SELECT INFORMATION.Sub AS Pub, INFORMATION.Sub,
Count(INFORMATION.[ITEM#]) AS Total
,First(0), First(0), First(0)
FROM ...

You also might want to include a column to allow you to determine which
rows have the regular totals versus which row is the grand total.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Do you mean after crosstab query and have the union? I tried it and it
"syntax error (missiong operator)

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub

UNION ALL SELECT INFORMATION.Sub AS Pub, INFORMATION.Sub,
Count(INFORMATION.[ITEM#]) AS Total
FROM INFORMATION INNER JOIN Info_Pub ON INFORMATION.Sub = Info_Pub.Pub
GROUP BY INFORMATION.Sub;

:

I am saying this query below, only pull the pub column and it does not pull
the sub. I want the pub and sub to pull if there is a match from Info_pub
table.

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub;





:

I do not follow what you are asking.

After seeing your raw data I know Ierred in using Sum instead of Count in
two of the queries. Here are the corrected queries ---
hoachen_1
SELECT "Total" AS Pub_, INFORMATION.Sub, Count(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY "Total", INFORMATION.Sub;

hoachen_2
SELECT Information.Pub, Information.Sub, Count(Information.[ITEM#]) AS
ItemCount
FROM Information
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY Information.Pub, Information.Sub
UNION ALL SELECT hoachen_1.Pub_, hoachen_1.Sub, hoachen_1.Totals
FROM hoachen_1;

TRANSFORM Sum([hoachen_2].[ItemCount]) AS [SumOfItemCount]
SELECT IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other") AS
Pub_, Sum([hoachen_2].[ItemCount]) AS Totals
FROM hoachen_2 LEFT JOIN Info_Pub ON [hoachen_2].Pub=Info_Pub.Pub
GROUP BY IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other")
PIVOT [hoachen_2].Sub;

--
Build a little, test a little.


:

The...i thinking so slow!!!! new table for the pub that i want to match.
Genius...well, since you are so good with this, I have one more request to
this. Now, the query only match up the Pub(new table: “Info_pubâ€) with the
Pub at table “informationâ€. I also need the Pub from “Info_pub†table that
match the Sub at the “information†table and display it just like Pub.

For example:
The Pub (MCG also can be Sub)
The Sub (Pearson also can be Pub)

Information Table data
Item# Author Pub Sub
6633814 JOHNSTON OOSTING+A Vital
6633810 NORTHSTAR FRISTSTAR Vital
6633801 *NORTHSTAR FRISTSTAR Vital
6633670 SULLIVAN PEARSON Vital
66336666 SULLIVAN LIGHT PEARSON
6666630 Smith S+S Vital
6633650 *SPARKCHART SPARKNOTES Vital
66336466 *SPARKCHART SPARKNOTES Vital
6621640 BISTNER ELSEV Vital
66216366 AEHLERT ELSEV Vital
6671762 ABEL PEARSON Courses
6671760 HUSTED PEARSON Courses
6665577 HUSTED PEARSON Courses
6672248 PANDE Light MCG
66686631 DALRYMPLE Light AEGYPAN PR
6668666 CRAM 101 TXBK MCG Courses
66638066 STERN Light PENG
66528166 WILSON Light JOSSEY
6647830 HAYES Light PENG
66478266 TAPSCOTT Light PENG


:

The need for Info_pub is to use in the
IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
so that any Pub not in list is displayed as 'Other'. By th way you need
'Total' in it also.

One query totals and then is put in a union with query selecting orignal
data and finally a crosstab query.

--
Build a little, test a little.


:

Thank you for your help! Now, i am confuse, why need to create a table named
"Info_pub"? All the data were in one table, i can't change that :(

So, the below queries will show the total in row? What about the statement
you wrote?

:

I omitted to creat table named Info_Pub with list of Pub not deemed Other.
Create these three queries to have Totals row at the bottom --
hoachen_1
SELECT "Total" AS Pub_, INFORMATION.Sub, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY "Total", INFORMATION.Sub;

hoachen_2
SELECT Information.Pub, Information.Sub, Information.[ITEM#]
FROM Information
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
UNION ALL SELECT hoachen_1.Pub_, hoachen_1.Sub, hoachen_1.Totals
FROM hoachen_1;

TRANSFORM Sum([hoachen_2].[ITEM#]) AS [SumOfITEM#]
SELECT IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other") AS
Pub_, Sum([hoachen_2].[ITEM#]) AS Totals
FROM [hoachen_2] LEFT JOIN Info_Pub ON [hoachen_2].Pub=Info_Pub.Pub
GROUP BY IIf([hoachen_2].[Pub]=[Info_Pub].[Pub],[hoachen_2].[Pub],"Other")
PIVOT [hoachen_2].Sub;

--
Build a little, test a little.


:

Your post had Vital & Vitals. Your SQL had Count and needed Sum.

TRANSFORM Sum(INFORMATION.[ITEM#]) AS [SumOfITEM#]
SELECT IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
AS Pub_, Sum(INFORMATION.[ITEM#]) AS Totals
FROM INFORMATION LEFT JOIN Info_Pub ON INFORMATION.Pub = Info_Pub.Pub
WHERE (((INFORMATION.Sub)="Vitals") AND ((INFORMATION.Author) Not Like
"*[*]*")) OR (((INFORMATION.Sub)="Courses") AND ((INFORMATION.Author) Not
Like "*[*]*")) OR (((INFORMATION.Sub)="light") AND ((INFORMATION.Author) Not
Like "*[*]*"))
GROUP BY IIf([INFORMATION].[Pub]=[Info_Pub].[Pub],[INFORMATION].[Pub],"Other")
PIVOT INFORMATION.Sub;

It will take a little more work to get the total row at the bottom.

--
Build a little, test a little.


:

Below is the SQL view:
Right now, it shows all the Pub Name but I would like to specific which pub
name i want to show and then those i did not choose to show will be group
into "Other". I hope this help explain

TRANSFORM Count([INFORMATION].[ITEM#]) AS [CountOfITEM#]
SELECT [INFORMATION].Pub, Count([INFORMATION].Pub) AS Totals
FROM [INFORMATION]
WHERE ((([INFORMATION].Sub) Like "Vital") AND (([INFORMATION].Author) Not
Like "*[*]*")) OR ((([INFORMATION].Sub) Like "Courses")) OR
((([INFORMATION].Sub) Like "light"))
GROUP BY [INFORMATION].Pub
PIVOT [INFORMATION].Sub;

Let say, I want these shows:
Pub COURSES LIGHT VITAL Totals
ABC CLIO C 11 11
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
OTHER 1936 3 155 2094
TOTAL 3975 3 236 4214

Currently show like this:
Pub COURSES LIGHT VITALS Totals
ABC CLIO C 11 11
ACSI COURS 3 3
AMSCO S 92 92
BAKER+TAY 1 1
BEDFORD F 146 6 152
CENGAGE CO 1893 26 1919
GRAY PUB 38 38
GUILFORD C 20 20
CENGAGE CO 1893 26 1919
HUMAN K CO 1 1
JONES+B CO 43 12 55
LIGHTNIN S 3 12902
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combining Crosstab Queries 1
Need Help with Crosstab Query 4
Total SUM in crosstab query 2
Select Query & Crosstab Query 1
Help with crosstab query 10
Crosstab Query Question 5
Crosstab Query Column headings 4
report recordsource from crosstab which columns varies 1

Top