Here is the SQL view for the entire report. You can see where it's
only
showing Qtr's 2 and 4, because the when crosstab query was run, only
those
2
quarters had data in them. Also, disregard the original post. The
problem
I'm having, is getting "$0.00" in where Jan, Feb, Mar, etc have null
values:
SELECT [FY 2006 Forecast].OQID, [FY 2006 Forecast].OppName, [FY 2006
Forecast].BusAreaID, [FY 2006 Forecast].Probability, [FY 2006
Forecast_Crosstab1].Jan, [FY 2006 Forecast_Crosstab1].Feb, [FY 2006
Forecast_Crosstab1].Mar, [FY 2006 Forecast_Crosstab1].Apr, [FY 2006
Forecast_Crosstab1].May, [FY 2006 Forecast_Crosstab1].Jun, [FY 2006
Forecast_Crosstab1].Jul, [FY 2006 Forecast_Crosstab1].Aug, [FY 2006
Forecast_Crosstab1].Sep, [FY 2006 Forecast_Crosstab1].Oct, [FY 2006
Forecast_Crosstab1].Nov, [FY 2006 Forecast_Crosstab1].Dec, [FY 2006
Forecast].[Weighted Bookings], [FY 2006 Forecast].BookAmount, [FY 2006
Forecast_Crosstab].[Qtr 2], [FY 2006 Forecast_Crosstab].[Qtr 4]
FROM ([FY 2006 Forecast] INNER JOIN [FY 2006 Forecast_Crosstab1] ON [FY
2006
Forecast].OppName = [FY 2006 Forecast_Crosstab1].OppName) INNER JOIN
[FY
2006
Forecast_Crosstab] ON [FY 2006 Forecast].OppName = [FY 2006
Forecast_Crosstab].OppName;
Thanks!!
--
Cyndi
:
I don't see the SQL view of a crosstab. You state "it's just showing
$0.00"
when displaying month columns but when displaying quarters (I don't
see
any
quarters anywhere) "it won't show the null value of $0.00". What do
you
mean
with both statements?
Also, ((DatePart("yyyy",[BookDate]))="2006") compares a numeric to a
string.
I would use:
Year([BookDate])=2006
--
Duane Hookom
MS Access MVP
--
Thanks for the answer. I got the months to go all the way across,
now
it's
just showing $0.00 if there is no $ value for that month. I also
need
to
get
the qurterly totals, which I have, but again, if there is no $ value
for
the
quarter, it won't show the null value of $0.00. Here's the SQL view
of
the
crosstab query for the report:
SELECT [FY 2006 Forecast].OQID, [FY 2006 Forecast].OppName, [FY 2006
Forecast].BusAreaID, [FY 2006 Forecast].Probability, [FY 2006
Forecast_Crosstab1].Jan, [FY 2006 Forecast_Crosstab1].Feb, [FY 2006
Forecast_Crosstab1].Mar, [FY 2006 Forecast_Crosstab1].Apr, [FY 2006
Forecast_Crosstab1].May, [FY 2006 Forecast_Crosstab1].Jun, [FY 2006
Forecast_Crosstab1].Jul, [FY 2006 Forecast_Crosstab1].Aug, [FY 2006
Forecast_Crosstab1].Sep, [FY 2006 Forecast_Crosstab1].Oct, [FY 2006
Forecast_Crosstab1].Nov, [FY 2006 Forecast_Crosstab1].Dec, [FY 2006
Forecast].[Weighted Bookings], [FY 2006 Forecast].BookAmount
FROM [FY 2006 Forecast] INNER JOIN [FY 2006 Forecast_Crosstab1] ON
[FY
2006
Forecast].OppName = [FY 2006 Forecast_Crosstab1].OppName;
Also, the original query that was used to start the report, and
still
used
in the report:
SELECT Opportunities.TaskID, Opportunities.OQID,
Opportunities.OppName,
Bookings.BusAreaID, Opportunities.Probability,
DatePart("m",[BookDate])
AS
BookMonth, DatePart("yyyy",[BookDate]) AS BookYear,
Bookings.BookAmount,
[BookAmount]*[Probability] AS [Weighted Bookings], Bookings.BookDate
FROM Opportunities INNER JOIN Bookings ON
Opportunities.OppID=Bookings.OppID
WHERE (((DatePart("yyyy",[BookDate]))="2006") AND
((Opportunities.OppStatus)
Like "New" Or (Opportunities.OppStatus)="Potential" Or
(Opportunities.OppStatus)="Developing" Or
(Opportunities.OppStatus)="Mature"
Or (Opportunities.OppStatus)="Contracting"));
Thanks in advance for your help.
--
Cyndi
:
Enter all possible month/column headings into the Column Headings
property
of the crosstab query.
From now on:
-Don't spend any more than a couple hours at the most
-Reply with the SQL view of your crosstab
--
Duane Hookom
MS Access MVP
--
I recently posted the below question (can't find it now) and
received
an
answer, but I have a further question.
The answer I received was "use a crosstab query." OK, I have
done
that,
and
it worked, THANKS!! BUT, my problem now is that it's not showing
ALL
of
the
months across the top of the page. It's only showing months that
don't
have
a null value. I've spent 5 days trying to figure all of this
out,
and
I
need
some help. THANKS!!
Cyndi
(previously submitted question)
I have a report that needs to show the MONTH and it's specific
DOLLAR
value
horizontally. I have it vertically, not a problem, but the VP
wants
it
horizontally, and wants to show every month even if there is a
null
value.
The report needs to read as :
Jan Feb Mar Apr May June etc..
Bus Area 1 $1 $3 $4 $6
Bus Area 2 $2 $6
Bus Area 3 $1 $3
The query is giving the data, but it's not distinguishing the $$
value
with
the month. I'm getting this:
Jan Jan Jan Jan Jan Jan
Bus Area 1 $1 $1
Bus Area 1 $3 $3 $3
Bus Area 1 $4 $4 $4
Bus Area 2 $2 $2
Bus Area 2 $6 $6
Bus Area 3 $1 $1
Bus Area 3 $3 $3
I can't see to get the totals to associate with the month, nor
get
the
months to be consecutive across the top. Please help.
