Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Specific Months in a Report (Access 97/SR2)

    I thought I was being so smart. I have a database that keeps track of the amount of downtime in our factory. There are 12 different reasons for the downtime. I want a report that gives me a recap of data by month and reason. For example, a report might show:
    January 2002

    Item Total Hours
    Mechanical 10
    Shipping 12
    Human Error 6

    Each record contains the date for each incident. To get the above report, I created a Totals query and used the following to get a total for each month - Month: Format([IncidentDate],"mmmm"", ""yyyy") and then GroupBy for the total and Sum for the DownTime column.

    This works great, except, now that I have 15 months of data entered, the report is very long. Since I changed the format of the IncidentDate, (or, rather represent it another way) I can't do a "Between" search, such as Between January, 2001 and July, 2001. I'm sure I just need to add a way to show the month and year, in another column, in date format but I can't figure out how I would do that. Anybody have any ideas? Thanks for the help.

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specific Months in a Report (Access 97/SR2)

    Try including a WHERE clause (specify "Where" in the totals row of the design grid) in your totals query that excludes older dates (e.g., WHERE [IncidentDate] >= #1/1/02#). Make sure this is based on your IncidentDate, not your reformatted date ("Month").

    Hope this helps.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Specific Months in a Report (Access 97/SR2)

    Try a crosstab query. Pivot on the month and year and use the item for your row heading and the sum of the hours for your value.
    Charlotte

Posting Permissions

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