Results 1 to 6 of 6
  1. #1
    air3jxt is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    May 2013
    Location
    New Albany, IN
    Posts
    4

    Dynamic Dates in Crosstab Column Headings

    Hey guys!



    I have a crosstab query that displays a totals for each month of the year. Currently my Column Headings is as follows:

    "Jan 2014","Feb 2014","Mar 2014", etc.

    Is there a way to use a variable for the year based on a field in my table? The data is only from a calendar year and it would be really nice to not have to manually change this part each year and this would translate other places, as well. I tried entering SELECT DISTINCT MONTH FROM TABLE, but Access didn't like it there.

    Ideas?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    Should be!
    Paste the query here in sql format, also here's a sample of a cross tab I made years ago,

    test2.zip

    The report will fail due to no data but it show whats going on.

  4. #4
    air3jxt is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    May 2013
    Location
    New Albany, IN
    Posts
    4
    Trevor - thanks! I got this to work, but I'd like to include the year if possible. Any ideas?

  5. #5
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    I have only used this for expences per month for the period selected - I have only ever used it for 1 year period, it will group all records from all years if it's not limited to 1 year, perhaps sombody else can assist with this. please post your working sample to see what you are doing.

  6. #6
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    P.S.
    Is there a way to use a variable for the year based on a field in my table? The data is only from a calendar year and it would be really nice to not have to manually change this part each year and this would translate other places, as well. I tried entering SELECT DISTINCT MONTH FROM TABLE, but Access didn't like it there.

    You can use qurey1 as the input for your dates, I manualy change this once a year, but you could change it to use two text fields on a form for the start and end dates

    your could use date functions to set the date fields in your form based on the current date/year or on another input. ie select year for report then set dates based on that

    then use this in query1
    Between forms.yourform.firstdate and forms.yourform.seconddate

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dynamic crosstab report sorting headings & dlookup
    By chrisangk in forum Programming
    Replies: 9
    Last Post: 01-14-2013, 02:37 PM
  2. Dynamic Headings on Multi Column Report
    By EddieN1 in forum Reports
    Replies: 7
    Last Post: 08-19-2012, 02:39 PM
  3. Replies: 1
    Last Post: 03-14-2011, 11:11 AM
  4. Replies: 1
    Last Post: 07-30-2010, 10:28 AM
  5. Dynamic Column Headers but NOT CROSSTAB
    By jtkjames in forum Queries
    Replies: 1
    Last Post: 07-26-2010, 05:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums