create and update a table from a cross tab query

[ssbbssbb]

I am fairly new to access. I have been tasked to make a financial db which will output to excel. I have raw data grouped in tables by Accounting period.
I have made a union query to select the info from all of the periods which totals the amount and groups against the spending code.
I then have a cross tab query based on the union query to layout the information in the best possible view with the spending codes as row and periods as columns.
I now want to output the results of the cross tab (refreshing everytime i run the query) into a new table which i can import into excel.
the main problem i am having is that i have to add a "Comments" column in between every period, this is to allow us to comment on any changes month to month. The problem has been when i update the table the comments are lost or (in excel) they become disjointed to the correct spend code.
I guess that if i could have the new table so that each period will update accordingly without deleting, changing or disjointing the comments columns.

sorry if i have babbled but this thing is starting to drive me mad, any help at all would be greatly appreciated.

 

[BNPMike]

What I would do is create blank columns in your cross tab. Then fill in these as comments after you have exported to Excel.

 

[dhookom]

It isn't clear whether you have comments stored in Access that you want to include in the crosstab or if you just need blank columns that you can update in Excel.
You can create a multiple value crosstabs by following the instructions in faq701-4524.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ssbbssbb]

Thanks for your help.

I want access to store the comments so that when i enter the data each month the comments will stay against the relevent row. the reasons i have been unable to just use excel is that every month the row details (which are spendcodes) will be similar but different ones will pop up each month. the crosstab view obviously suites the information as i need an up to date financial view of things. I need the blank column each time but so that it stays against the relevent figure. I have tried the northwind example but it came back not recognising a field which is there?

would adding a key to the table and or the query help? any help you can give would be brilliant.

 

[ssbbssbb]

Hi, Me again.

scratch the northwind thing, i saw the error. i have doen as you suggested and it is brilliant. the only question i have is :
Can i use two seperate sources for the Value part of the cross tab? i figured that is i perform an IF question based on the title of the column so that it will take the sum info from the £amount and if the colmn has "comments" in it it will take the info from the comments column? Here is the current SQL for the cross tab query.

you are realy saving my bacon!

RANSFORM Sum([Main Union Query].SumOfAmount) AS SumOfSumOfAmount
SELECT [Main Union Query].RAC
FROM CrossTabHeadings, [Main Union Query]
WHERE ((([Main Union Query].BLB) Is Not Null))
GROUP BY [Main Union Query].RAC, [Main Union Query].BLB
PIVOT [Fieldnames] & [AP];

 

[dhookom]

I'm not sure if this will work or not but you can try:

Transform IIf(FieldName="A",Sum(SumOfAmount), First(Comments))

Duane
MS Access MVP
Find out how to get great answers faq219-2884.