Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004

    Question Unanswered: Creating updatable "crosstab"

    I have data listed vertically in a table that I would like to show horizontally. Access does provide functionality to create this by using Crosstabs, but this creates read-only recordsets.

    What I would like is the possibility to show data as you do with a crosstab, but be able to update these data. Do you know how to do this either with queries or in VBA?

  2. #2
    Join Date
    Feb 2004
    I'm not quite sure how you would do that. My understanding of a cross-tab query is to group like items together and perform a function on them (sum, count, avg, etc..). So if your xtab query counted the number of times a particuar record existed, when you update it, which record do you update? All of them?


    Given the following Table:

    JEFF        JERK
    JEFF        JERK
    JEFF        FOOL
    FRED      STUD
    FRED      STUD
    The query would look like:
              JERK     FOOL    STUD
    JEFF     2          1          0
    FRED   0          0          2
    So, when you change the "2" in the Cell Jeff-Jerk, how would the underlying table change? If you changed the "2" to a "3" would it create a new record in the table (Name="JEFF", Name_Called="Jerk")?

    I believe the xtab-query cannot be updateable because how would access know what do do. I suppose (just guessing here) that a crafty programmer could possibly create a form with unbound text boxes that when a cross-tab query was run, these controls are populated (I've done something similiar with a report). Then on the 'On Change' event for the text boxes, if the numbers were incremented, code would add a record (i.e. if cell Jeff-Jerk was changed to 3, code would go out and add a new record). If number was decremented, table would go out and delete 1 record (but which record??). Then if number was set to 0, delete all records. I guess it depends largely on the action you're taking..if you're averaging, then you're in trouble. It may be my lack of sleep or perhaps the beer, but it sounds doable. But not in the condition I'm in now....


  3. #3
    Join Date
    Feb 2004
    Thank you for your response. It will maybe be easier to respond if I give some more information about what my table look like and what I want to do with it.

    My table looks like
    Division AccountType Year Value
    Div1 - Income - 2004 - 1234
    Div1 - Income - 2005 - 3214
    Div1 - Income - 2006 - 4567
    Div1 - Expense - 2004 - 2345
    Div1 - Expense - 2005 - 4563
    Div1 - Expense - 2006 - 5432

    I want them to be shown like follows and be updateable
    Division - AccountType - 2004 - 2005 - 2006
    Div1 - Income - 1234 - 3214 - 4567
    Div1 - Expense - 2345 - 4563 - 5432

    As you can see there is no operation done on the numbers in the table in connection with the crosstab. I have to put in an operation (sum, max, min...) to make the crosstab work, but it does not affect the numbers. So in order to be able to update the underlying record if one of the numbers have been changed, I need to have information about the Division, Account Type and Year as they make up my primary key.

Posting Permissions

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