Crosstab Excel Refresh

[DarrylW]

I currently have a cross tab query that I have linked to excel. Each week new data will add a new column to the cross tab, but when I refresh the query in excel these new columns do not refresh into the sheet as they were not part of the query the week before. I guess this is a common problem? Can any one help please?

[OBP]

Darryl, if the new column headings are like months of the year, you can add them ahead of time by putting them in the query's Column Headings property.

[DarrylW]

Thanks for your reply...

I did try this as it seemed the most logical thing to do. However it does put the dates in but looses data for some of the columns. They are weekending dates

[OBP]

Darryl, you should be able to put all of the dates in the column Headers, however it does depend on how you are formatting the data in the Crosstab, are you doing weekly, monthly or by actual date?

[DarrylW]

I'm doing it by actual dates. So for this week i have pulled data back for 15/05/2010.

What i'm finding is that it is not pulling data back for 01/05/2010, 08/05/2010, but will pull data back for 15/05/2010 & 22/05/2010?

It's like it doesn't like the single digit dates. I have reviewed all formats and they are the same

[OBP]

How are the dates stored in the table?
It sounds like a conflict between "01/05/2010" and "1/05/2010"
If you are using the date as is and it is the weekend date, you should be able to ask the crosstab to show it as weekly.
Have you tried a crosstab like that?

[DarrylW]

The dates are in the following format dd/mm/yyyy. I've tried putting the headings in both formats dd/mm/yyyy although i havent tried asking it to show as weekley as suggested, but i'm unsure how i'd do this any way

[OBP]

You can set them to weekly, monthly, quarterly & annual in the Wizrad as you create the Query.
You can aslo create specialised column headings using the Format() function.