Results 1 to 8 of 8
  1. #1
    Daisy509th's Avatar
    Daisy509th is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Central Texas
    Posts
    51

    How best to design this query and report

    I have a database that tracks employee actions and awards them points for each action and each employee is on a team.
    The database will report employees, teams, team members, employee points and team points. This all works.

    Here is what I cant figure out:

    We have quarterly points and of coures year ending points.
    Quarter 1 = 12/1/ of any year to 2/28/ of any year


    Quarter 2 = 3/1/ of any year to 5/31/ of any year
    Quarter 3 = 6/1/ of any year to 8/1/ of any year
    Quarter 4 = 9/1/ of any year to 11/30/ of any year

    So I want a report to show the points for each quarter when pulled, Then thourght out the year show the points for all quarters thus far.

    One problem is if I hard code the quarter dates it will only be good for that year and someone will have to modify the querries each year.
    Second problem if I dont hard code them then the user will have to enter quarter dates everytime.
    Third problem I am a novice at this


    Attachment 32998

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I have a form, pick the Quarter, or month, etc....this fills in the txtStartDate and txtEndDate
    or you can do it manually.
    Use these dates to limit the query: select * from table where date between forms!myForm!txtStartDate and forms!myForm!txtEndDate
    then do your analysis.

    Click image for larger version. 

Name:	rpts ytd.jpg 
Views:	14 
Size:	45.5 KB 
ID:	32995

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Create a table tblQtr with two fields
    Qtr number PK
    MonthNo number

    Use this table in a query where you add extra date fields - MonthNo, YearNo, YrQtr
    The YrQtr field concatenates YearNo and Qtr e.g 2018-1

    Use this for an aggregate query where you sum the points per team for each quarter
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi can't you simply use the group by quarter feature of the report interface?

    Click image for larger version. 

Name:	GroupByQuarter.JPG 
Views:	13 
Size:	44.7 KB 
ID:	33005

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Good point Noella - I tend to forget that feature exists
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Daisy509th's Avatar
    Daisy509th is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Central Texas
    Posts
    51
    The wizard didnt work for me because I couldnt figure a way to manipulate the dates. My company decide to re invent the wheel by changing the dates of our quarters. Ridders52 I am still trying to figure out your solution and get it to work.
    I will give credit soon and hopfully mark this solved.

    Thanks for the help and I take all you are willing to give

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I found the database I uploaded for you a few weeks ago and have modified that for this particular issue

    First of all, whoever decided that Dec should be in the same quarter as Jan/Feb needs a good 'thrashing'!

    Changes made in the attached database
    1. New table tblQtr - I made a mistake earlier & now MonthNo is the PK field
    2. Modified dates in my example table tblExployeeActions to cover several quarters for testing
    3. Added fields MonthNo & YearNo to qryEmpActions
    4. Created a new query qryEmpActionsYrQtr - note how I've handled Dec 2017 so its YrQtr field is listed as 2018-1
    5. Created a new aggregate query qryTotalTeamPointsYrQtr

    6. I also tried Noella's suggestion and it fails due to the decision to group quarters abnormally
    If you used Jan-Mar as quarter 1 => Oct-Dec as quarter 4 it would work perfectly
    I've included the report for your info - rptEmployeeActionsQtrDUFF

    HTH
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    The tReports table holds the report users can print.


    field: rptFile is the report name
    if the field: rptUsing, has T (for Time) then the time objects will appear.
    rptQry is used to open the query, (instead of the report)
    other fields arent really used anymore.


    Other codes: C,D,etc, would activate other combo boxes to be used in queries.


    all the Time objects are there to set the text boxes:
    txtDateStart
    txtDateEnd


    then the report would use these in the query:
    select * from table where [date] between forms!frmReports!txtDateStart and forms!frmReports!txtDateEnd


    Easter eggs:
    If you dbl-click on the title: REPORTS, it will open the report table for quick editing


    If you select a report then dbl-click on the list box header: SELECT REPORT TO PRINT, it will open the report in design mode.

    Reporting app.zip

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

Similar Threads

  1. report design
    By sunil in forum Reports
    Replies: 3
    Last Post: 01-26-2015, 11:43 AM
  2. Replies: 5
    Last Post: 09-12-2014, 06:41 AM
  3. design Report
    By Rasha in forum Access
    Replies: 1
    Last Post: 04-28-2014, 02:58 AM
  4. Report Design
    By cbrsix in forum Reports
    Replies: 2
    Last Post: 06-27-2012, 07:10 AM
  5. Replies: 2
    Last Post: 02-15-2012, 04:04 PM

Tags for this Thread

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