Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    15

    Unanswered: dynamic field names

    Hi

    I am using Access QBE, and I would like some way of creating dynamic field aliases (names) in a query.

    for example

    lets say I have one table with account number (amongst other things), that is joined to a table that has:

    account number | month | year | Spend
    ----------------------------------------------
    12345 1 2004 100
    12345 2 2004 150

    etc etc.

    What I would like to show is

    account number | jan | feb | mar | apr .......
    but I only want the last 4 months to be shown. So for exmple for todays month (may) it would look like

    account number | Apr | mar | feb | jan
    --------------------------------------------
    12345 | 150 | 180 | 140 | 200

    my criteria looks like this:
    Format(DateAdd("m",-6,Date()),"yyyym")=[Year] & [Month]

    which works fine

    and what I want to do is put in:
    Format(DateAdd("m",-6,Date()),"mmm"): Spend
    for the column heading. but when i do this it appears exaclty as written i.e. it does not change my function into a string.

    basically i want to create a pivot table, but i cant use the function for my purposes.

    does this make sense at all? hope someone can help.

    kevin

    p.s. I know I can do it in code but i would like to avoid it if possible.

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    have you tried playing with a crosstab query?

  3. #3
    Join Date
    Apr 2004
    Posts
    15
    i have tried but it isnt really what I want. anyone have any other ideas?

Posting Permissions

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