Creating Weekly report from Cumulative Totals

[Sackface]

Hi,

I have an excel report which I would like to run through Access to drive trend analysis and compare with other similar reports. The excel report has a cumulative spend figure each week and not the actual weekly spend numbers, the budget figure also can change depending on the actuals.

Excel report:

Week 1	Product ID	Customer	Yearly Budget	Spend
	1122	Sam	100	3
	1123	John	200	4
	1124	Barry	150	1
Week 2	Product ID	Customer	Yearly Budget	Spend
	1122	Sam	100	6
	1123	John	300	20
	1124	Barry	150	2
Week 3	Product ID	Customer	Yearly Budget	Spend
	1122	Sam	100	10
	1123	John	300	28
	1124	Barry	150	8

Desired Report from Access:

Product ID Customer Yearly Budget Spend Week 1 Spend Week 2 Spend Week 3 1122 Sam 100 3 3 4 1123 John 300 4 16 8 1124 Barry 150 1 1 6
Will I need to create a new table each week or can I link the file and it updates automatically? Can Access store the weekly data and just update it one week at a time? Any help on this would be greatly appreciated.

[June7]

That Excel structure doesn't look suitable for a link or import with the wizard. If you did some fixes then Access could link to the worksheet and manipulate the data just like a native table except for editing.

[Sackface]

Thanks for the response. What fixes would I need to do in Excel?

[June7]

Don't repeat the column headers.

Instead of Week1, Week2, Week3, Week4 as headers, have a header called WeekNum and then put the week number on every row. In other words, like an Access table structure.