Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, I have a table "Issues" which has two fields - Issue and ExpectedCompletion(Date). The Primary Key is "Issue". My database pulls in data from multiple sources to populate this table, but I now need to write a VBA procedure which will populate the attached table - In other words, it will count issues due for completion for a certain month (Jan, Feb... etc.) then provide a cumulative figure up to the final day of that month (e.g. 31/01/10) in the related month.

    I use a key per Line e.g. Line Id = 2 in this instance for my coding, but haven't worked with dates before, let alone cumulative counts, so am scratching my head a bit. A blank of the final table is attached to show you more clearly what I mean. Can you point me in the right direction remembering that I'm a coding learner not a techie?

    Much obliged if you can help!
    Attached Files Attached Files

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Have a look at attached example which creates a cross tab query with cumulative totals from tbl_Dates.
    This is just examples, but it might push you in the right direction.

    The final query is based upon a temporary table because access will not build it from the query that is used for the temp table

    From the data, a query is made that creates a list of month names against ID
    A second query is used to get the summary by id and month
    A Third query used to make the table along with a correlated sub query creates the cumulative totals
    The Fourth query makes a cross tab from the temp table

    It's a lot easier in SQL Server

    [attachment=88198:CDATES.zip]
    Attached Files Attached Files
    Andrew

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Your example spreadsheet distinguishes between Issues Planned for Closure, and Issues Actually Closed, yet your data only has 1 date: ExpectedCompletion.

    How do you know if something has actually been closed?

    I would find it helpful if you posted a small sample of data, and a manually compiled results table to show what results you are looking for from the small data sample.

    You seem to have posted the same Excel file twice.
    Regards
    John



  4. #4
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi John, yes the table will have many lines (much more than 3) - however, once I work out how to do this one line in VBA, I can basically amend the VBA variables to populate the other lines, so didn't want to push my luck asking you to show me how to do the lot (have been learning a great deal using your expertise as a springboard - repeating and working out amendments based on initial help is terrific). Sorry about posting the table twice - sometimes, to be honest I find this site and its boxes a bit confusing :-)

    Please find a spreadsheet attached with a small amount of sample data and what I'm meaning attached to line 2 of the sample table I already sent you.

    Much obliged.[attachment=88206:Sample.xls]
    Attached Files Attached Files

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Ok I am clearer now.

    One option to consider - no VBA, no crosstabs...sounds attractive.

    Have a report laid out as a crosstab, where the control source is just a Dcount

    For Jan 2010 use: =Dount("*","tblIssues", "[ExpectedCompletion] < #2/1/2010#")

    Then just copy and paste this across the row, adding a month to the date for each one.
    For other rows, you could copy this whole row, and change the field name.

    [attachment=88216:CumulDates.zip]
    Attached Files Attached Files
    Regards
    John



  6. #6
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks John and Andrew, might still have to resort to VBA for some of the lines though! Much obliged for your help.

Posting Permissions

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