Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Stats Report (2003)

    I received a lot of help on a database problem a while ago (see <post#=455626>post 455626</post#> ) ) which was greatly appreciated, and has worked perfectly since. Now my company has come up with another request, which I'm not sure how to do. The original allows me to create a report to show statistics of jobs done between certain dates, with the number of jobs, number completed and the percentage. This report is run every two weeks. What they want is another report to show the difference between the current report, and the previous one. Say the report was run on the 3rd November and a user had done 26% of the jobs, and when run on the 10th of November he had done 52%, then the report would show that his number of jobs outstanding had gone down.
    Working out the differences is not the problem. The problem is how to get the data for the two dates (current and previous), as to run the report once, we have to type in a start date and end date. I don't want to confuse the issue with having to enter start dates and end dates too many times

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stats Report (2003)

    Is the report period always the same length (eg. 2 weeks long)? If so, you only really need to prompt the user for one date (say the start date of the current period). Then you can calculate the end date of the current period ([start date]+13), the end date of the previous period ([start date]-1) and the start date of the previous period ([start date]-14) from it.

    Does this help?
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Stats Report (2003)

    I've just found out that the person running the report, always runs the report from when the database was first used, so start date could be eliminated (I could put a criteria in). But thinking about it, I can't see how this can work without taking a 'snapshot' of the report. If a job is outstanding one week, but completed the next, the stats will alter anyway. I need to be able to report the difference between one week and the next (if that makes any sense)

  4. #4
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stats Report (2003)

    Looking at your database, I'm not sure that the required report is possible as it currently stands. The database shows whether a job is completed, but not when it was completed - so there's no way of knowing if a job that is complete at the moment was complete this time last month etc.

    A possible way around this is to change your "Completed" field to a date, leaving it blank until a job is completed and entering the date when it is completed. Then it would be straightforward to generate a report counting how many jobs were completed in a given time period.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Stats Report (2003)

    They've got used to putting a 'yes' if completed. Changing this to a date would only confuse matters. I'll tell them it can't be done (not with my knowledge anyway). Thanks for your advise anyway.

  6. #6
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stats Report (2003)

    There is another way - you could automatically populate a [date completed] field when the users change the value of [completed], but there are several drawbacks with this:
    <UL><LI>[date completed] would show when the job was recorded as being completed, which might be different to the actual completion date
    <LI>You wouldn't have a [date completed] for any jobs on the database that are already complete when you add the new field
    <LI>You'd have to deal with the scenario of users changing "yes" back to "no"
    <LI>It involves VBA code which is great as long as you're reasonable familiar with it[/list]If you'd like some help with writing some VBA code to do this, let us know. Otherwise I'm afraid it's a case of manually comparing this period's report with last period's, or managing without.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

Posting Permissions

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