Cross Tab Help

[James]

Ok, I have a query that requests a user enter in a date such as 2/20/2007.
It then looks up some data, I then use this data in the following crosstab:

TRANSFORM Sum([qry_between].[TotalCharges]) AS [Total For Period]
SELECT [qry_between].[Sub Level A Name] AS Branch,
Count([qry_between].[TotalCharges]) AS [Number Of Phones],
Avg([qry_between].[TotalCharges]) AS [Average Per Phone]
FROM qry_between
GROUP BY [qry_between].[Sub Level A Name]
PIVOT Format([Invoice Date],"mmm") IN ("HELP HERE");

Right now everything works great, expect I get the heading HELP HERE, I want
this to Display the name of the month that the user imputted to begin with
(ie Febuary in this example)
How would I go about doing this?

Thanks

 

[James]

To add to this, Right now in the last line I have
PIVOT Format([Invoice Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Now this works great, but it shows all months, is there any way I can have
it hide the months that don't have any data in them? So when I put in my
febuary date, it only shows feb?

 

[Van T. Dinh]

....
PIVOT Format([Invoice Date],"mmm") IN
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[James]

Is there anyway I can get it to hide the months that dont have any data in
them?
Its kind of a pain having to scroll over if you only have data in dec or
something...

...
PIVOT Format([Invoice Date],"mmm") IN
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


--
HTH
Van T. Dinh
MVP (Access)

Ok, I have a query that requests a user enter in a date such as
2/20/2007. It then looks up some data, I then use this data in the
following crosstab:

TRANSFORM Sum([qry_between].[TotalCharges]) AS [Total For Period]
SELECT [qry_between].[Sub Level A Name] AS Branch,
Count([qry_between].[TotalCharges]) AS [Number Of Phones],
Avg([qry_between].[TotalCharges]) AS [Average Per Phone]
FROM qry_between
GROUP BY [qry_between].[Sub Level A Name]
PIVOT Format([Invoice Date],"mmm") IN ("HELP HERE");

Right now everything works great, expect I get the heading HELP HERE, I
want this to Display the name of the month that the user imputted to
begin with (ie Febuary in this example)
How would I go about doing this?

Thanks

 

[Van T. Dinh]

That is actually considered as a "feature" per Access Help: if you have data
for 11 months, it looks nicer if you still have 12 Columns for 12 months and
the Column without data is empty.

Could I ask why you use CrossTab but with only 1 result Column for the given
month? Usually, people use CrossTab because they wany to compare data for
multiple months / period.