Total based on status in crosstab

[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.

 

[KARL DEWEY]

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.

 

[hoachen]

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.

 

[KARL DEWEY]

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

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.

 

[hoachen]

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

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

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.

 

[KARL DEWEY]

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.

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

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

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.

 

[hoachen]

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!

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

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.

 

[KARL DEWEY]

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.

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!

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

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.