Analysing Crosstab Data (2003)

[InaFix]

Hi folks,

Does anyone know anything about depreciation analysis? I have a large crosstab query which shows an Asset Id as a row header and roughly 300 column heads which represent Periods (Year/Month eg 200401). The crosstab value is the amount of depreciation against an asset within that period. I'm trying to analyse out a depreciation impairment across a line - i.e. where across the line, depreciation values are steady until there is one figure out of sync followed by 0s.

I'm also trying to track changes in depreciation method which shows as one value for many periods, followed by a different value, also for many periods. What I would like to do is run a procedure which would return a value of either "Impairment" or "Method" in a new field called "Error".

I'm just beginning designing modules - does anyone have a module I can use as a basis??

Ina

[HansV]

I don't know anything about depreciation analysis, but from a quick Google search I get the impression this is mostly done using specialized software.
In Access (or in Excel versions before Excel 2007), you'll have a problem, since a query can have at most 256 columns.

[InaFix]

Hi, I'm using a later version. Thanks anyway, I'll try and figure something out.

[HansV]

> I'm using a later version

Later than Excel 2007? Wow!

[InaFix]

Erm .Not using Excel at all - using Access 2003.

Regards.

[HansV]

You wrote that you have about 300 column heads representing time periods. However, a query in Access can returm at most 255 columns.

[InaFix]

Well in view of that I'll have to do something across two crosstab queries - I have about 300 period fields going from 1980 to around 2016 - at the moment I'm looking at a section 1994 to 2006. This is obviously a site for very experienced developers so I apologise for obviously irritating you with my enquiry.

Regards.

Ina.

[HansV]

No, you don't irritate me at all, and questions of all levels are welcome. Sorry if I gave the wrong impression.

I only wanted to point out that analyzing the data will be difficult because of the 255 column limitation in Access. You can, of course create two crosstab queries, but then it becomes more difficult to compare the data between the two queries.

Does the company where you work use statistical software such as SAS, SPSS or SPlus? If so, it might be worthwhile to look into that - they can work with a much larger number of columns.
SQL Server might be another possibility - it allows up to 4096 columns in a SELECT statement.