Help! Reports and Crosstab Queries

[Guest]

Hello,

I have created a crosstab query and I would like to use the information in a
report. I can't figure out how to get the total number of Lost for Jan in
my report. I need the same thing for Won. Below is an example of my
crosstab query:

Won/Lost SortDate QuoteID TotalValue Jan Feb

012006 4 $67,000.00 4
022006 1 $10,252.00 1
Lost 012006 2 $25,000.00 2
Lost 022006 1 $8500.00 1
Won 012006 7 $150,000.00 7
Won 022006 3 $12,000.00 3

If you look at the third row under Won/Lost is shows Lost,
follow that across to the right and under the SortDate is the month of
012006 (Jan), under Jan it shows "2" which is the total number of Losses for
Jan. Same thing for "Won" it shows "7" Wins for Jan. I want to take the
total number of Losses for Jan and insert it in my Lost colunm for Jan in
my report and I would like to do the same for the Wins. I have the crosstab
query as my record source for the report. I don't know what to put in the
"control source" in the text box for Jan that will give me the total of
Losses.

Any help is appreciated.

Mark

 

[Guest]

You were asked earlier today to post the SQL for your crosstab query.

 

[Guest]

Karl,

I believe you have me confused with another post. This is the first time I
have post this questions. However, here is the SQL data you asked for.

TRANSFORM Count(Quotes.SolicitationID) AS CountOfSolicitationID
SELECT Quotes.[Won/Lost], Format([SubmitDate],"mmyyyy") AS SortDate,
Count(Quotes.SolicitationID) AS [Total Of SolicitationID],
Sum(([Quantity]*[QuotedPrice])) AS TotalValue
FROM Quotes
GROUP BY Quotes.[Won/Lost], Format([SubmitDate],"mmyyyy")
PIVOT Format([SubmitDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thanks for you time.

Mark

 

[Duane Hookom]

I believe you can total won for Jan with an expression like:

=Sum(Abs([Won/Lost] ="won") * [Jan])
losses for Jan would be
=Sum(Abs([Won/Lost] ="lost") * [Jan])

--
Duane Hookom
MS Access MVP

Karl,

I believe you have me confused with another post. This is the first time
I
have post this questions. However, here is the SQL data you asked for.

TRANSFORM Count(Quotes.SolicitationID) AS CountOfSolicitationID
SELECT Quotes.[Won/Lost], Format([SubmitDate],"mmyyyy") AS SortDate,
Count(Quotes.SolicitationID) AS [Total Of SolicitationID],
Sum(([Quantity]*[QuotedPrice])) AS TotalValue
FROM Quotes
GROUP BY Quotes.[Won/Lost], Format([SubmitDate],"mmyyyy")
PIVOT Format([SubmitDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thanks for you time.

Mark

You were asked earlier today to post the SQL for your crosstab query.

 

[Guest]

Here is a series of queries that I think does what you want.

MarkJackson_1 ---
SELECT Format([SubmitDate],"mmyyyy") AS SortDate, IIf([Won/Lost]="Won",1,0)
AS Won, IIf([Won/Lost]="Lost",1,0) AS Lost, (([Quantity]*[QuotedPrice])) AS
[Value]
FROM Quotes;

MarkJackson_2 ---
SELECT MarkJackson_1.SortDate, Sum(MarkJackson_1.Won) AS SumOfWon,
Sum(MarkJackson_1.Lost) AS SumOfLost, Sum(MarkJackson_1.Value) AS SumOfValue
FROM MarkJackson_1
GROUP BY MarkJackson_1.SortDate;

MarkJackson_3 ---
SELECT MarkJackson_2.SortDate, "Won " & [SumOfWon] & " " & "Lost " &
[SumOfLost] AS ABC, MarkJackson_2.SumOfValue
FROM MarkJackson_2;

TRANSFORM First(MarkJackson_3.ABC) AS FirstOfABC
SELECT MarkJackson_3.SortDate, MarkJackson_3.SumOfValue AS [Total Of Value]
FROM MarkJackson_3
GROUP BY MarkJackson_3.SortDate, MarkJackson_3.SumOfValue
PIVOT Format(DateSerial(Right([SortDate],4),Left([SortDate],2),1),"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Karl,

I believe you have me confused with another post. This is the first time I
have post this questions. However, here is the SQL data you asked for.

TRANSFORM Count(Quotes.SolicitationID) AS CountOfSolicitationID
SELECT Quotes.[Won/Lost], Format([SubmitDate],"mmyyyy") AS SortDate,
Count(Quotes.SolicitationID) AS [Total Of SolicitationID],
Sum(([Quantity]*[QuotedPrice])) AS TotalValue
FROM Quotes
GROUP BY Quotes.[Won/Lost], Format([SubmitDate],"mmyyyy")
PIVOT Format([SubmitDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thanks for you time.

Mark

You were asked earlier today to post the SQL for your crosstab query.

 

[Guest]

I thank both of you. It works perfectly!

Karl,

I believe you have me confused with another post. This is the first time I
have post this questions. However, here is the SQL data you asked for.

TRANSFORM Count(Quotes.SolicitationID) AS CountOfSolicitationID
SELECT Quotes.[Won/Lost], Format([SubmitDate],"mmyyyy") AS SortDate,
Count(Quotes.SolicitationID) AS [Total Of SolicitationID],
Sum(([Quantity]*[QuotedPrice])) AS TotalValue
FROM Quotes
GROUP BY Quotes.[Won/Lost], Format([SubmitDate],"mmyyyy")
PIVOT Format([SubmitDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thanks for you time.

Mark

You were asked earlier today to post the SQL for your crosstab query.