Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update selected rows of a table 1

Status
Not open for further replies.

ssbbssbb

Vendor
Aug 6, 2004
8
GB
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.
 
If you are new to Access, you probably want to have an experienced person review your tables and setup. There are some common mistakes people make when starting off that make working with your data much more difficult than it needs to be.

That said, you can have a comments column automatically added in your crosstab query for each period. Create a new table with a single field called "Sequence". Add two rows in the table with values 1 and 2. Add this table to your crosstab query and add a criterion:

[Sequence] Between 1 and 2 ' in case you add more rows later

Then post the SQL for your crosstab and I or someone else here can show how to change the expressions for your column heading and value fields.
 
Many thanks for helping me out JonFer,

here is the SQL of my Main union query and my cross tab query that runs off of it. i realise that i have probably set out the tables wrong as i have created one for each period and thats probably useless. many many thanks again, here we go....

MAIN UNION SELECT QUERY
SELECT [DEPT],[AP],[SPENDCODE], Sum([Amount]) AS SumOfAmount
FROM [AP-12]
GROUP BY [DEPT], [AP], [SPENDCODE], [Comments]
UNION SELECT[DEPT],[AP],[SPENDCODE], Sum([Amount]) AS SumOfAmount
FROM [AP03]
GROUP BY [DEPT], [AP], [SPENDCODE], [Comments]
UNION SELECT[DEPT],[AP],[SPENDCODE], Sum([Amount]) AS SumOfAmount
FROM [AP04]
GROUP BY [DEPT], [AP], [SPENDCODE], [Comments]
UNION SELECT[DEPT],[AP],[SPENDCODE], Sum([Amount]) AS SumOfAmount
FROM [AP05]
GROUP BY [DEPT], [AP], [SPENDCODE], [Comments]
UNION SELECT[DEPT],[AP],[SPENDCODE], Sum([Amount]) AS SumOfAmount
FROM [AP06]
GROUP BY [DEPT], [AP], [SPENDCODE], [Comments]
UNION SELECT[DEPT],[AP],[SPENDCODE], Sum([Amount]) AS SumOfAmount
FROM [AP07]
GROUP BY [DEPT], [AP], [SPENDCODE], [Comments]
UNION SELECT[DEPT],[AP],[SPENDCODE], Sum([Amount]) AS SumOfAmount
FROM [AP08]
GROUP BY [DEPT], [AP], [SPENDCODE], [Comments]
UNION SELECT[DEPT],[AP],[SPENDCODE], Sum([Amount]) AS SumOfAmount
FROM [AP09]
GROUP BY [DEPT], [AP], [SPENDCODE], [Comments]
UNION SELECT[DEPT],[AP],[SPENDCODE], Sum([Amount]) AS SumOfAmount
FROM [AP10]
GROUP BY [DEPT], [AP], [SPENDCODE], [Comments]
UNION SELECT[DEPT],[AP],[SPENDCODE], Sum([Amount]) AS SumOfAmount
FROM [AP11]
GROUP BY [DEPT], [AP], [SPENDCODE], [Comments]
UNION SELECT[DEPT],[AP],[SPENDCODE], Sum([Amount]) AS SumOfAmount
FROM [AP12]
GROUP BY [DEPT], [AP], [SPENDCODE], [Comments];

MAIN UNION SELECT QUERY CROSS TAB

TRANSFORM Sum([Main Union Query].SumOfAmount) AS SumOfSumOfAmount
SELECT [Main Union Query].DEPT, [Main Union Query].SPENDCODE
FROM [Main Union Query], Sequence
WHERE ((([Main Union Query].DEPT) Is Not Null) AND ([Sequence] Between "1" And "2"))
GROUP BY [Main Union Query].DEPT, [Main Union Query].SPENDCODE, Sequence.Sequence
PIVOT [Main Union Query].AP;
 
If [AP] is the accounting period, you can create a Make-Table query based on your Union and get a single table with all of the periods. You can then add to it with an Append query.

I forgot to say that the Sequence field should be an integer.

Here are changes to the crosstab. This assumes Sequence is now an integer. I aliased the union query to make is easier to work with.
Code:
TRANSFORM Sum(IIf([Sequence]=1,a.SumOfAmount,Null)) AS SumOfSumOfAmount
SELECT a.DEPT, a.SPENDCODE
FROM [Main Union Query] as a, Sequence
WHERE (((a.DEPT) Is Not Null) AND ([Sequence] Between 1 And 2))
GROUP BY a.DEPT, a.SPENDCODE
PIVOT IIf([Sequence]=1,a.AP,a.AP & "_Comment");
I didn't test this so post back if it doesn't work and be sure to include the updated SQL.
 
I have no idea what you have done but you are a genius!

I now have the the table exactly how i need it. I am just going to try the appending thing to see how that works but I thought i would reply while you might still be online, Thank you so much!
 
JONFER!

I have the table perfect now. i still do not understand the updating query though. the append adds records but then i get a duplicate set of records (minus the comments). how do i go about updating the information and keeping the comments with out having duplicates everytime i run the query? sorry for being so useless:(
 
I didn't mean to use the crosstab as the append. I was trying to simplify your table setup by just having one AP table instead of one for each month. That AP table would then be the source for your crosstab.

By the way, you can export the crosstab results directly to Excel instead of writing it to a table first.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top