Results 1 to 13 of 13
  1. #1
    mamaw is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2020
    Posts
    10

    YE Report_Total # claims up/down per dpt 2019 compared to 2018.

    Hello,

    I need to prepare a query/report that lists departments where the total number of claims reported for 2019 are up compared to 2018...I know how to prepare a query and report that lists all departments and total number of claims for 2019 in one column and 2018 in another...I also added two additional columns to show difference between the two years and also the percentage difference. So I can see up and down numbers, but I need to produce two separate reports: Departments with # Claims Up Compared to 2018 [show total number claims for each year and # claims up by comparison]. The other report would report # claims down...



    Please help....




  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,455
    Suggest you show us what you have done.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,553
    Your percentages are positive and negative? Then with one report, apply a filter based on that. Modify identifying information in the report accordingly if need be. Could use OpenArgs property when opening report to do that. Unless you want a totally different format, don't create 2 reports. I suppose if you're opening reports from the left nav pane then you'd need 2 reports, but you shouldn't be doing that.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  4. #4
    mamaw is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2020
    Posts
    10
    Here's what I have do so far...

    TRANSFORM Count(Accidents.ID) AS [# claims]
    SELECT Accidents.Department
    FROM Accidents
    GROUP BY Accidents.Department
    ORDER BY Accidents.Department, Format([DateOccurrence],"yyyy")
    PIVOT Format([DateOccurrence],"yyyy");
    Click image for larger version. 

Name:	RPT_Incidents Up_Down 2019 vs 2018.jpg 
Views:	17 
Size:	56.5 KB 
ID:	44175Click image for larger version. 

Name:	Incidents Up_Down Qry.jpg 
Views:	15 
Size:	39.5 KB 
ID:	44176

  5. #5
    mamaw is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2020
    Posts
    10
    typo!!! Here's what I have DONE so far

  6. #6
    mamaw is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2020
    Posts
    10
    Yes, positive and negative. Report package that I need to put together requires two separate reports...one for # claims up and another for # claims down. What would I enter as filter? I replied with a copy of what I have done so far...

  7. #7
    mamaw is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2020
    Posts
    10
    I have provided you with "what I have done so far." I hope I have provided you with sufficient information … let me know if you need additional. I know how to prepare the reports by exporting the data to Excel, but I really prefer using Access since that's where I prepare 90% of the other reports for this Year-End package.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,553
    Surely that report's recordsource isn't the query you posted?
    Just filter the report when you open it: [Incidents] > 0 or [Incidents] < 0 as already mentioned? If you need help with that, explain how you're opening the report and we can suggest a modification.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  9. #9
    mamaw is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2020
    Posts
    10
    I need help with where, what and how to enter filter so that my report will display only records where [2019] - [2018] > 0
    I have attached a screen print of the design view of my report and query.Click image for larger version. 

Name:	# Incidents Up Down Report.jpg 
Views:	12 
Size:	84.9 KB 
ID:	44192

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,553
    If you need help with that, explain how you're opening the report and we can suggest a modification.
    If you don't answer the questions I can't help. Those pictures have not helped much at all with anything. You could also copy the db, compact and zip it and upload it here.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  11. #11
    mamaw is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2020
    Posts
    10
    I answered to the best of my ability as quickly as possible....I cant send you the db because it contains confidential information. Sorry that the screen shots are not beneficial. I typically prepare these incidents up/down reports in Excel and the other YE reports in Access. My goal was to have ALL of the YE reports run from Access. For time-sake and due date closing in, I will have to produce the up/down reports in Excel and get training for Access at another time.

    Thank you for your time.

  12. #12
    mamaw is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2020
    Posts
    10
    Quote Originally Posted by mamaw View Post
    I answered to the best of my ability as quickly as possible....I cant send you the db because it contains confidential information. Sorry that the screen shots are not beneficial. I typically prepare these incidents up/down reports in Excel and the other YE reports in Access. My goal was to have ALL of the YE reports run from Access. For time-sake and due date closing in, I will have to produce the up/down reports in Excel and get training for Access at another time.

    Thank you for your time.
    FYI, not sure if I'm answering with the correct terminology, but I access the report from the list of Objects under Reports...just click on the report and print preview/print.

    Thank you for your time.

    Frustrated....even now I'm having difficulty getting this edited reply to send ... no fault of yours ... I have a lot to learn...

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,553
    You have little to no options by opening reports from the nav pane. Use a form. Want to see the 'up' values of a particular metric? Then perhaps as just one of many possibilities
    - user opens a reports form
    - chooses from a combo list of reports from tblReports (report name field shows "rptYEincrease", description field shows "Year End Metric Increase by Percent") actual name is hidden in the combo. Could use autonumber in code if table has one, or just refer to the report name
    - AfterUpdate of combo opens report and passes the filter argument (following is just an example)
    Code:
    Private Sub cmbReports_AfterUpdate
    
    Select Case Me.cmbReports
      Case This
        Do stuff
    
      Case That
        Do stuff
    
      Case "rptYEincrease"
        DoCmd.OpenReport, "rptYEincrease",acViewPreview,,"Incident>0"
    
    End Select
    
    End Sub
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

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

Similar Threads

  1. Date entry format : 08/11/2018 1350
    By WCStarks in forum Access
    Replies: 30
    Last Post: 08-24-2018, 07:59 PM
  2. New Claims Management Project
    By D.C in forum Access
    Replies: 7
    Last Post: 01-15-2018, 12:46 PM
  3. Replies: 3
    Last Post: 09-09-2015, 03:11 PM
  4. Query compared to another table
    By Petox in forum Queries
    Replies: 3
    Last Post: 02-18-2014, 04:39 PM
  5. Lookup of result based on 1 value compared to range.
    By Scorpio11 in forum Database Design
    Replies: 4
    Last Post: 04-11-2012, 02:30 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
  •  
Other Forums: Microsoft Office Forums - Senior Forums