Results 1 to 5 of 5
  1. #1
    Rangerguy2000's Avatar
    Rangerguy2000 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Hamilton, ON, Canada
    Posts
    32

    Weekly Metrics Query/Report

    Hi, I want to build a Weekly Metrics Query that summarizes a Drivers performance based in various Metrics.
    All the data is being recorded into a MetricsDetails table with the following fields;
    Record_Date, Record_ID Driver_ID, Metric_ID, Flag_ID, PointsEarned

    I have made a Query that is calculating the WeekNumber and WeekDay.



    Any suggestions which route to take?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a form for the date range:
    txtStartDate or txtEndDate

    or your week#: txtWeekNum

    make a base query to pull data from this range:
    select * from table where [dateFld] between forms!myForm!txtStartDate and
    forms!myForm!txtEndDate

    then run your Metric query off the query above.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    What metrics are you attempting to get out of your data? Depending on what you're after it's fairly possible you could do it all in one query. If you're after something complex or a series of metrics that might take a bit more doing.

  4. #4
    Rangerguy2000's Avatar
    Rangerguy2000 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Hamilton, ON, Canada
    Posts
    32
    Hi guys OK here's the structure and what I am envisioning.

    Image1 is the query I made to pull the data.
    Image2 is the results from the previous query.
    Image3 is the report from the query.

    For now this report will work as I will refine it later. But I want to change the structure.
    How can I make the days Mon, Tue, Wed, Thu, Fri etc, show horizontally and not vertically on the report.
    e.g. like in Image4.

    Click image for larger version. 

Name:	Image1.JPG 
Views:	17 
Size:	63.7 KB 
ID:	36375Click image for larger version. 

Name:	Image2.JPG 
Views:	17 
Size:	157.8 KB 
ID:	36376Click image for larger version. 

Name:	Image3.JPG 
Views:	17 
Size:	59.5 KB 
ID:	36377Click image for larger version. 

Name:	Image4.JPG 
Views:	17 
Size:	25.5 KB 
ID:	36378

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You want a crosstab query if you want your days of the week across the top of. If you are doing multiple columns a crosstab query may not work and you'll have to get a little more inventive.

    P.S. You do not need to store the day of the week, you can get it with a native function in Access based on your record date:

    DoW: weekdayname(Weekday([Record_Date]), true)

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

Similar Threads

  1. Replies: 2
    Last Post: 02-21-2018, 08:49 AM
  2. Weekly Report
    By bronson_mech in forum Reports
    Replies: 2
    Last Post: 01-20-2017, 05:21 AM
  3. Creating a weekly report
    By chr1stoper1 in forum Reports
    Replies: 2
    Last Post: 05-12-2016, 10:41 PM
  4. Replies: 1
    Last Post: 04-20-2015, 03:22 PM
  5. weekly timetable report format
    By merlin777 in forum Reports
    Replies: 14
    Last Post: 10-27-2014, 11:06 AM

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