Total based on status in crosstab

  • Thread starter Thread starter hoachen
  • Start date Start date
H

hoachen

TRANSFORM Count([Q Group By].[Qe#]) AS [CountOfQ#]
SELECT [Q Group By].[Prov#], [DProv].ProvName, Count([Q Group By].[Q#]) AS
[Total Of All Q]
FROM ([Q Group By] INNER JOIN [Date Con] ON [Q Group By].DateEntered = [Date
Con].[A Date]) INNER JOIN [DProv] ON [Q Group By].[Prov#] = [D Prov].[Prov#]
GROUP BY [Q Group By].[Prov#], [DProv].ProvName
PIVOT Format([DateEntered],"mmm 09");

Prov# ProvName Aug 09 Jul 09 Sep 09 T.close Q T.open Q T.All Q
211 Midwest CNC 7 3 2 8 10
111 Mazak Corp. 3 3 3
1023 Naab Sales 2 1 1 2
11 ed City Tool & 200 87 37 300 24 324

The above crosstab is fine but i would like to have another 2 columns that
will shows:
1. Total of Close Q
2. Total of Open Q

The name on the table that stored the status called “Qstatusâ€
Now, I can only display 1 at a time but not all 3 totals. For the Open Q, I
can take the total of All Q subtract the “close†status then I can get the
open result. And, take total of All Q subtract the “open†then I will get the
“close†result. My question is how can I do it on this crosstab query? Is it
durable?

Thanks for take your time to read my question. Your input will be very
appreciated.
 
You said the name on the table that stored the status called “Qstatus†but
not the field or how the table is related to any other table.
 
Sorry, the field name called: Qstatus which the row contains close Q and open Q

KARL DEWEY said:
You said the name on the table that stored the status called “Qstatus†but
not the field or how the table is related to any other table.

--
Build a little, test a little.


hoachen said:
TRANSFORM Count([Q Group By].[Qe#]) AS [CountOfQ#]
SELECT [Q Group By].[Prov#], [DProv].ProvName, Count([Q Group By].[Q#]) AS
[Total Of All Q]
FROM ([Q Group By] INNER JOIN [Date Con] ON [Q Group By].DateEntered = [Date
Con].[A Date]) INNER JOIN [DProv] ON [Q Group By].[Prov#] = [D Prov].[Prov#]
GROUP BY [Q Group By].[Prov#], [DProv].ProvName
PIVOT Format([DateEntered],"mmm 09");

Prov# ProvName Aug 09 Jul 09 Sep 09 T.close Q T.open Q T.All Q
211 Midwest CNC 7 3 2 8 10
111 Mazak Corp. 3 3 3
1023 Naab Sales 2 1 1 2
11 ed City Tool & 200 87 37 300 24 324

The above crosstab is fine but i would like to have another 2 columns that
will shows:
1. Total of Close Q
2. Total of Open Q

The name on the table that stored the status called “Qstatusâ€
Now, I can only display 1 at a time but not all 3 totals. For the Open Q, I
can take the total of All Q subtract the “close†status then I can get the
open result. And, take total of All Q subtract the “open†then I will get the
“close†result. My question is how can I do it on this crosstab query? Is it
durable?

Thanks for take your time to read my question. Your input will be very
appreciated.
Click to expand...
Click to expand...
 
So how is [Qstatus].[Qstatus] related to any of the other tables?
--
Build a little, test a little.


hoachen said:
Sorry, the field name called: Qstatus which the row contains close Q and open Q

KARL DEWEY said:
You said the name on the table that stored the status called “Qstatus†but
not the field or how the table is related to any other table.

--
Build a little, test a little.


hoachen said:
TRANSFORM Count([Q Group By].[Qe#]) AS [CountOfQ#]
SELECT [Q Group By].[Prov#], [DProv].ProvName, Count([Q Group By].[Q#]) AS
[Total Of All Q]
FROM ([Q Group By] INNER JOIN [Date Con] ON [Q Group By].DateEntered = [Date
Con].[A Date]) INNER JOIN [DProv] ON [Q Group By].[Prov#] = [D Prov].[Prov#]
GROUP BY [Q Group By].[Prov#], [DProv].ProvName
PIVOT Format([DateEntered],"mmm 09");

Prov# ProvName Aug 09 Jul 09 Sep 09 T.close Q T.open Q T.All Q
211 Midwest CNC 7 3 2 8 10
111 Mazak Corp. 3 3 3
1023 Naab Sales 2 1 1 2
11 ed City Tool & 200 87 37 300 24 324

The above crosstab is fine but i would like to have another 2 columns that
will shows:
1. Total of Close Q
2. Total of Open Q

The name on the table that stored the status called “Qstatusâ€
Now, I can only display 1 at a time but not all 3 totals. For the Open Q, I
can take the total of All Q subtract the “close†status then I can get the
open result. And, take total of All Q subtract the “open†then I will get the
“close†result. My question is how can I do it on this crosstab query? Is it
durable?

Thanks for take your time to read my question. Your input will be very
appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Nope.
The table contains fields (Q#, Prov#, DateEntered, QExpired, Cust# and
QStatus.)

KARL DEWEY said:
So how is [Qstatus].[Qstatus] related to any of the other tables?
--
Build a little, test a little.


hoachen said:
Sorry, the field name called: Qstatus which the row contains close Q and open Q

KARL DEWEY said:
You said the name on the table that stored the status called “Qstatus†but
not the field or how the table is related to any other table.

--
Build a little, test a little.


:

TRANSFORM Count([Q Group By].[Qe#]) AS [CountOfQ#]
SELECT [Q Group By].[Prov#], [DProv].ProvName, Count([Q Group By].[Q#]) AS
[Total Of All Q]
FROM ([Q Group By] INNER JOIN [Date Con] ON [Q Group By].DateEntered = [Date
Con].[A Date]) INNER JOIN [DProv] ON [Q Group By].[Prov#] = [D Prov].[Prov#]
GROUP BY [Q Group By].[Prov#], [DProv].ProvName
PIVOT Format([DateEntered],"mmm 09");

Prov# ProvName Aug 09 Jul 09 Sep 09 T.close Q T.open Q T.All Q
211 Midwest CNC 7 3 2 8 10
111 Mazak Corp. 3 3 3
1023 Naab Sales 2 1 1 2
11 ed City Tool & 200 87 37 300 24 324

The above crosstab is fine but i would like to have another 2 columns that
will shows:
1. Total of Close Q
2. Total of Open Q

The name on the table that stored the status called “Qstatusâ€
Now, I can only display 1 at a time but not all 3 totals. For the Open Q, I
can take the total of All Q subtract the “close†status then I can get the
open result. And, take total of All Q subtract the “open†then I will get the
“close†result. My question is how can I do it on this crosstab query? Is it
durable?

Thanks for take your time to read my question. Your input will be very
appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Try this --
TRANSFORM Count([Q Group By].[Qe#]) AS [CountOfQ#]
SELECT [Q Group By].[Prov#], [DProv].ProvName, Count([Q Group By].[Q#]) AS
[Total Of All Q], Sum(IIF([QStatus] = "Open", 1, 0)) AS [Total Q Open],
Sum(IIF([QStatus] = "Close", 1, 0)) AS [Total Q Close]
FROM ([Q Group By] INNER JOIN [Date Con] ON [Q Group By].DateEntered = [Date
Con].[A Date]) INNER JOIN [DProv] ON [Q Group By].[Prov#] = [D Prov].[Prov#]
GROUP BY [Q Group By].[Prov#], [DProv].ProvName
PIVOT Format([DateEntered],"mmm 09");

--
Build a little, test a little.


hoachen said:
Nope.
The table contains fields (Q#, Prov#, DateEntered, QExpired, Cust# and
QStatus.)

KARL DEWEY said:
So how is [Qstatus].[Qstatus] related to any of the other tables?
--
Build a little, test a little.


hoachen said:
Sorry, the field name called: Qstatus which the row contains close Q and open Q

:

You said the name on the table that stored the status called “Qstatus†but
not the field or how the table is related to any other table.

--
Build a little, test a little.


:

TRANSFORM Count([Q Group By].[Qe#]) AS [CountOfQ#]
SELECT [Q Group By].[Prov#], [DProv].ProvName, Count([Q Group By].[Q#]) AS
[Total Of All Q]
FROM ([Q Group By] INNER JOIN [Date Con] ON [Q Group By].DateEntered = [Date
Con].[A Date]) INNER JOIN [DProv] ON [Q Group By].[Prov#] = [D Prov].[Prov#]
GROUP BY [Q Group By].[Prov#], [DProv].ProvName
PIVOT Format([DateEntered],"mmm 09");

Prov# ProvName Aug 09 Jul 09 Sep 09 T.close Q T.open Q T.All Q
211 Midwest CNC 7 3 2 8 10
111 Mazak Corp. 3 3 3
1023 Naab Sales 2 1 1 2
11 ed City Tool & 200 87 37 300 24 324

The above crosstab is fine but i would like to have another 2 columns that
will shows:
1. Total of Close Q
2. Total of Open Q

The name on the table that stored the status called “Qstatusâ€
Now, I can only display 1 at a time but not all 3 totals. For the Open Q, I
can take the total of All Q subtract the “close†status then I can get the
open result. And, take total of All Q subtract the “open†then I will get the
“close†result. My question is how can I do it on this crosstab query? Is it
durable?

Thanks for take your time to read my question. Your input will be very
appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Thanks again for your big help!! It works!!! May I ask, how can I be an
expert like you?? I am so thankful your contribution to help anyone like
me--need help.


Thanks again. You are the best!
KARL DEWEY said:
So how is [Qstatus].[Qstatus] related to any of the other tables?
--
Build a little, test a little.


hoachen said:
Sorry, the field name called: Qstatus which the row contains close Q and open Q

KARL DEWEY said:
You said the name on the table that stored the status called “Qstatus†but
not the field or how the table is related to any other table.

--
Build a little, test a little.


:

TRANSFORM Count([Q Group By].[Qe#]) AS [CountOfQ#]
SELECT [Q Group By].[Prov#], [DProv].ProvName, Count([Q Group By].[Q#]) AS
[Total Of All Q]
FROM ([Q Group By] INNER JOIN [Date Con] ON [Q Group By].DateEntered = [Date
Con].[A Date]) INNER JOIN [DProv] ON [Q Group By].[Prov#] = [D Prov].[Prov#]
GROUP BY [Q Group By].[Prov#], [DProv].ProvName
PIVOT Format([DateEntered],"mmm 09");

Prov# ProvName Aug 09 Jul 09 Sep 09 T.close Q T.open Q T.All Q
211 Midwest CNC 7 3 2 8 10
111 Mazak Corp. 3 3 3
1023 Naab Sales 2 1 1 2
11 ed City Tool & 200 87 37 300 24 324

The above crosstab is fine but i would like to have another 2 columns that
will shows:
1. Total of Close Q
2. Total of Open Q

The name on the table that stored the status called “Qstatusâ€
Now, I can only display 1 at a time but not all 3 totals. For the Open Q, I
can take the total of All Q subtract the “close†status then I can get the
open result. And, take total of All Q subtract the “open†then I will get the
“close†result. My question is how can I do it on this crosstab query? Is it
durable?

Thanks for take your time to read my question. Your input will be very
appreciated.
Click to expand...
Click to expand...
Click to expand...
 
I just build something in design view then look at it in SQL to reverse
engineer for a different query.
--
Build a little, test a little.


hoachen said:
Thanks again for your big help!! It works!!! May I ask, how can I be an
expert like you?? I am so thankful your contribution to help anyone like
me--need help.


Thanks again. You are the best!
KARL DEWEY said:
So how is [Qstatus].[Qstatus] related to any of the other tables?
--
Build a little, test a little.


hoachen said:
Sorry, the field name called: Qstatus which the row contains close Q and open Q

:

You said the name on the table that stored the status called “Qstatus†but
not the field or how the table is related to any other table.

--
Build a little, test a little.


:

TRANSFORM Count([Q Group By].[Qe#]) AS [CountOfQ#]
SELECT [Q Group By].[Prov#], [DProv].ProvName, Count([Q Group By].[Q#]) AS
[Total Of All Q]
FROM ([Q Group By] INNER JOIN [Date Con] ON [Q Group By].DateEntered = [Date
Con].[A Date]) INNER JOIN [DProv] ON [Q Group By].[Prov#] = [D Prov].[Prov#]
GROUP BY [Q Group By].[Prov#], [DProv].ProvName
PIVOT Format([DateEntered],"mmm 09");

Prov# ProvName Aug 09 Jul 09 Sep 09 T.close Q T.open Q T.All Q
211 Midwest CNC 7 3 2 8 10
111 Mazak Corp. 3 3 3
1023 Naab Sales 2 1 1 2
11 ed City Tool & 200 87 37 300 24 324

The above crosstab is fine but i would like to have another 2 columns that
will shows:
1. Total of Close Q
2. Total of Open Q

The name on the table that stored the status called “Qstatusâ€
Now, I can only display 1 at a time but not all 3 totals. For the Open Q, I
can take the total of All Q subtract the “close†status then I can get the
open result. And, take total of All Q subtract the “open†then I will get the
“close†result. My question is how can I do it on this crosstab query? Is it
durable?

Thanks for take your time to read my question. Your input will be very
appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top