Re: Month's in column report

Tech-Archive recommends: Fix windows errors by optimizing your registry

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


"Duane Hookom" wrote:

> 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
> --
>
> "Cyndi" <Cyndi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:EA837C8F-1B2C-451F-8EB1-57A64F3A3BC1@xxxxxxxxxxxxxxxx
> >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.
> >
> > --
> > Cyndi
>
>
>
.

Relevant Pages

  • Months in column report
    ... The answer I received was "use a crosstab query." ... my problem now is that it's not showing ALL of the ... Cyndi ... The report needs to read as: ...
    (microsoft.public.access.reports)
  • Re: Dynamic Reporting based on Parameterized Crosstab Query
    ... The tek-tips solution does display the actual date on the report. ... charge date charged hours are associated with. ... on the first 3 rowwise fields of the crosstab query. ...
    (microsoft.public.access.reports)
  • Re: Crosstab Report
    ... Specify the column names in the query's Column Headings property. ... Save the report with unbound controls, ... Then in the Open event of the report, generate the crosstab query statement ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.reports)
  • Re: pigeon hole report
    ... When trying to create a report ... I downloaded your Concatenate function and can get that part ... > You should be able to create a crosstab query that makes a grid with all the ... > plan information for a specific pigeon concatenated together. ...
    (microsoft.public.access.reports)
  • Re: Openrecordset Query with Parameter
    ... > I have report that summaizes rolling periods. ... > fields change as the column headings potentially change each time the ... > retrieve the field names from the crosstab query and assign them to ... Dim qdf As DAO.QueryDef ...
    (microsoft.public.access.modulesdaovba)