Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Analysing Crosstab Data (2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Analysing Crosstab Data (2003)

    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.

  3. #3
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analysing Crosstab Data (2003)

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Analysing Crosstab Data (2003)

    > I'm using a later version

    Later than Excel 2007? Wow!

  5. #5
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analysing Crosstab Data (2003)

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

    Regards.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Analysing Crosstab Data (2003)

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

  7. #7
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analysing Crosstab Data (2003)

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Analysing Crosstab Data (2003)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •