Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2010
    Posts
    12

    Unanswered: Crosstab Queries: Dynamic reporting months

    Hi everybody,

    Dynamic Monthly Crosstab Report - Microsoft: Access Reports FAQ - Tek-Tips

    I've been trying to recreate this report but have gotten stuck with a number of a things.

    My forecast table has records of hours worked for each resource on a number of projects. In my table, fields are basically

    [Project],[Name],[Location],[Jan 10], [Feb 10], .... [Dec 11] < where Jan 10 contains the total amount of hours worked for that resource.

    Currently I have the form set-up to show the rest of 2010 - [July 10] - [Dec 10]. What I am trying to show is a rolling 12 month outlook into the current month + the 11 following months, so for insteance - in November, my users will open the form/report and see allocations from [Nov 10] - [Oct 2011].

    Using the "dateadd" function, I was thinking that I could put a text box in my form that =Date() to show the current date. I also know how to get the current month and the month after

    =DateAdd("m",0,me.txtCurrentDate)
    =DateAdd("m",1,me.txtCurrentDate)
    ... and so on

    This would work if my column headers were dates instead of text - 3/1/2010 instead of Mar 10 or 4/1/2010 instead of Apr 10.

    So I thought that to get around this I should first make a reference table { where [Month] = Mar 10, Apr 10 and [Date] = 3/1/2010 , 4/1/2010 } and then build a query that brings [Project], [Name], [Location] from my forecast table, but then only brings in the respective Mar 10, Apr 10 column fields if they are within the range (current month - current month + 11).

    However... I cannot figure out how to write the sql that brings in the next 12 months. I get stuck because Mar 10, Apr 10 are columns in my forecast table, but rows in my reference table.


    Do you have any ideas? My sincere appologies if this is a stupid question.

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Change your table design to [Project],[Name],[Location],[Forecast date]. Forecast date is the start of the month of the forecast. Then use your crosstab to produce the rolling twelve months

  3. #3
    Join Date
    Jun 2010
    Posts
    12
    Thanks Poppa Smurf for the response. You and another contributor have recommended this solution. Apparently the structure of my db - having columns for each month's total hours for each resource - is not "normalized" and therefore doesn't lend itself to a crosstab query.

    However, I like the structure of the table for my purposes - currently I have set-up a useful search form where users can simply enter in resource attributes (project, location, rate) that filter the records. Then they are able to enter in the number of hours each month for each resource, and these changes are documented in a report that compares the original table to the one that they make the edits from. Once the changes have been approved (by me), the original table is loaded with the new hourly totals.

    While it may "commit spreadsheet", I'd prefer to keep it structured this way. My primary issue has to do with the form's display of months. Do you know of any way that I can set-it up so that as each month progresses, the 12 month range will progress one month as well?

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    So each month you have to add a new field to the table and redesign your form?

    Using unbound text boxes for your column headers do the following to get a rolling 12 months of headers e.g. Jul 10, Aug 10,Sep 10. Note this will not give you a rolling 12 months of data.

    In the control source of each unbound text box
    =Format(DateAdd("m",0,Date()),"mmm yy")
    =Format(DateAdd("m",1,Date()),"mmm yy")
    ... etc

    Would you please upload a copy of your database with some sample data and I will have a look at your rolling 12 months of data problem?

  5. #5
    Join Date
    Jun 2010
    Posts
    12

    Attachment

    Here's my database. The year is off - Mar 09 really means Mar 10, and Mar 10 really means Mar 11. I didn't have enough time to adjust these.


    Let me know what you think I should do...

    thanks!
    Last edited by valverde311; 06-29-10 at 17:31.

  6. #6
    Join Date
    Jun 2010
    Posts
    12
    Thanks for the control source expression to return Jun 10, Jul 10 and so on.

    Is there any way of referencing the value of the textbox so that it will pull columnar data from a column if the column name = the value of the textbox?

    right now it pulls data with the control source being "Jun 10"... I was hoping that it would work if i set the source to be =[tblForecast].[Text104] as the value of the Text104 = Jun 10. Unfortunately, I only get a "?Name" error.

  7. #7
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    You forgot to upload your database as an attachment. You could send the database to my email address as a zipped file.

  8. #8
    Join Date
    Jun 2010
    Posts
    12

    My DB

    Here's my database.

    If it is not possible to use the expression =Format(DateAdd("m",0,Date()),"mmm yy") to reference a column name (that is identical to the expressions result) in a query or a form, what do you think would be the easiest way to get the months in my form to shift out every month?
    Attached Files Attached Files

  9. #9
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Please upload your zipped database again as I am getting error messages that the file is corrupted or there are no file(s) to extract as size of 65KB indicates no file to extract.

  10. #10
    Join Date
    Jun 2010
    Posts
    12
    Hmm... not sure why you're having that issue. I have tried the uploaded zip file from a few different computers and it has worked each time. It's a 956kb database compressed at 93% so the 67kb size of the zip file seems normal to me... I've sent the uncompressed file to your email.

  11. #11
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Joel

    For some reason using Windows 7 I can not extract your zip files and some of the other zip files posted to this forum.

    Anyway attached is a solution to your problem. Below is the changes made to your database. Please read the comments in the OpenForm event of your searchform and let me know if you need more information.

    You will have to make changes to your form to show the 12 months period.

    Changes to table vend_forecast
    Added fields for the next 12 months until [Jun 11]

    Changes to Form Searchform
    The following changes will be explained in detail when explaining the Coding Change later in this document.
    1. Removed the record source as the record source will be created using code see the Coding below.
    2. Changed the control source of the months from [Jul 09], [Aug 09] etc. to m0, m1 etc. See OpenForm event code in the form
    3. Renamed the name of the field from Jul 09, Aug 09 etc. to m0, m1 etc. This makes it easier to change the caption of the label to the respective month
    4. Renamed the label that show the months above the fields.
    5. Changed the control source of the month totals in the footer to = Sum([m0] etc.
    6. On the form created the Open form event and added coding for this event. See the comments throughout the code.

    NOTE: These changes were made for testing purposes. Resizing of the fields on the form is required.To show the full twelve months, additional fields, labels and coding changes are required.
    Attached Files Attached Files

  12. #12
    Join Date
    Apr 2009
    Posts
    4

    attachment is not opening

    Hi,

    I have also a similar situation. But the attachment is not recognised in mdb or accdb format. pls attach a fresh one which can be opened in access (mdb or accdb )

    thanks in advance

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The problem does not come from the attached file. You need to use WinRAR or an ancient version of WinZip (7.x and 80 work OK) to open the file.
    Have a nice day!

  14. #14
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    I managed to open you zip file
    I suggest you look seriously at normalizing your table e.g. for each month of the years have one record and NOT one record that covers all the months and years. Having a record for each month of the years will make the dynamic crosstab very easy,plus you could look at pasty months using the crosstab.

    Using a series of append queries or code you could easily create the data into a normalized table.

  15. #15
    Join Date
    Apr 2009
    Posts
    4

    attachment is not opening

    hi,

    i extracted the file by use winrar. ertacted file "joel" is not opening in access
    pls help. screen shot is attached
    Attached Files Attached Files

Posting Permissions

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