Crosstab Query issue 1

[techkenny1]

Hi
I have created a crosstab query to pull debtors from a list of companies.This is then exported to Excel.
The problem i have is that it only shows 12 months. So I get Sep09 to Aug 1o. What I requires is for it to got from July 09 to Aug 10.
Here is the sql code for the query;

TRANSFORM Sum(Accounts08.InvoiceTotal) AS SumOfInvoiceTotal
SELECT Accounts08.Companyname, Sum(Accounts08.InvoiceTotal) AS Total
FROM Accounts08
WHERE (((Accounts08.ACS) In ("Invoiced")))
GROUP BY Accounts08.Companyname
PIVOT Accounts08.Month;

How can i get it to show the extra 2 months

Thanks
kp

 

[SkipVought]

Ohhhh, kp,

Are your Excel users going to have to do data analysis with your REPORT? I personally DESPISE getting REPORTS that are to be used for data analysis. But that's my extreme bias.

Your "Month" is bereft a vital piece of information: YEAR!!!

So your PIVOT clause needs FIRST year and then month. I would opt for a REAL date; the first or last or the month.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[techkenny1]

Hi SkipVought,
Many thanks for your reply. Yes the Banks will uses the data to do a data analysis.
I will take on board your last paragraph and implement the year. This data is available in the accounts table.

KP

 

[SkipVought]

If I were a bank bean counter, I'd much rather get a proper table(s) than a report, in order to perform data analysis.

Reports are for management types who need to see succinct summary information.

Usually the more granular the data, the better for data analysis. And, needless to say, normalized data.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]