Results 1 to 9 of 9

Pulling Specific Information in Access Reports

  1. #1
    Herman Dickey is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2016
    Location
    Memphis, TN
    Posts
    29

    Pulling Specific Information in Access Reports

    I have a report of employee training that is kept on an ongoing basis. The goal is to produce a report that ONLY shows the training they have received in 2016. The query did exactly what I needed, but the report still shows everything.



    How can I fix this?

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    What is the record source for the report, and how are you opening it?

    If you are opening the report with a DoCmd.OpenReport.... on a form, you can include a date filter in the DoCmd statement, for example:

    DoCmd.Openreport ReportName, , , "Year(Trainingdate) = 2016"

    You could also hard-code the date criteria into the query, and base the report on the query.

  3. #3
    Herman Dickey is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2016
    Location
    Memphis, TN
    Posts
    29
    Thanks John.

    It worked. The query now shows only training dates that occurred in 2016. Now, the total on the report won't add only those numbers to show the amount of training hours an employee has had for the year. Any ideas?

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    So, the report shows only 2016 training, as you want?

    What is the control source for the total training hours box on the report? Usually it will be =sum(some field in the detail)

    Does the report have a detail section listing all the 2016 training ?

  5. #5
    Herman Dickey is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2016
    Location
    Memphis, TN
    Posts
    29
    The control source is the query, which has a "sum of training_hours" column. In the query, each employee's training hours are totaled. On the printed report, the "total training hours YTD" field shows 1.0 on the first, 2.0 on the next, and so forth.

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    If the query has a column for sum of training hours, then all you need is a report control that has that query totals column as its control source. The report doesn't need to sum anything for each employee, because the query has already done it.

  7. #7
    Herman Dickey is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2016
    Location
    Memphis, TN
    Posts
    29
    The field that I used in the report was from the "Sum of training_hours" in the query, thinking it would show exactly as it does there.

  8. #8
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    Strange, it should work. The control source for that field on the report is set to the Totl field in the query, and running sum is set to "No"?

    What section of the report is that data in? It's a bit counter - intuitive, but it should be in the Detail section, because the report is not summing anything - it's already summed in the query.

  9. #9
    Herman Dickey is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2016
    Location
    Memphis, TN
    Posts
    29
    You're right. It's in the detail section.

    So I should set the running sum to NO? Let me try that. Thanks!

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

Similar Threads

  1. Replies: 1
    Last Post: 10-08-2015, 04:27 AM
  2. Replies: 5
    Last Post: 06-11-2015, 06:45 PM
  3. Pulling up information
    By Accu-Grind in forum Forms
    Replies: 6
    Last Post: 04-27-2015, 12:08 PM
  4. Replies: 2
    Last Post: 05-26-2012, 10:39 AM
  5. Replies: 1
    Last Post: 04-06-2009, 07:25 PM

Posting Permissions

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