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

    Report based calculations

    Attachment 36451

    Hi,
    I have the above report using a Crosstab Query that puls data from multiple tables.
    My question is, can I do a calcualtion based in the report based on the data?
    What you under Mon, Tue etc. is the Flag_ID's for a driver performance. e.g.

    Flag_ID


    1 = Y/Y
    2 = N/Y
    3 = N/N

    and All the Metric_IDS's (1-11) have different weightages. e.g.
    Metric_ID's
    1 = 5
    2 = 10
    3 = 15

    And all these (1-11) weights dd up to a total of 100.

    I want to do a Sum IIF with weights under each day based on the Metric_ID's and the Flag_IDs'.

    Any help is appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Get 'Invalid Attachment' on your link.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

    Report Based Calculations

    Hi,
    attaching the DB.
    Please look at Query qry1Sub_DriversWeeklyReport_Crosstab .. This query pulls the data from the Query qry2Sub_DriversWeeklyReport ..

    Report rpt1Sub_DriversWeeklyReport_Crosstab is the report I want to manipulate.

    Essentially, I want the report to calculate the Drivers Daily Performance.
    e.g.
    Metric_ID 1 has a wight of 5,
    Metric_ID 2 has a wight of 10.
    Metric_ID 3 has a wight of 15.
    So Metric_ID's 1-11 add up to a total of 100.

    And
    Flag_ID 1 is Y/- (Yes OnTime, -)
    Flag_ID 2 is N/N, (No OnTime, Not Controllable)
    Flag_ID 3 is N/Y, (No OnTime, Controllable)

    So, I want to calculate as per below for Monday;
    When Metric_ID=1, and Flag_ID=1, Driver gets 5 points;
    When Metric_ID=1, and Flag_ID=2, Driver gets 2.5 points;
    When Metric_ID=1, and Flag_ID=3, Driver gets 0 points;

    And I want to do this for all Metric_ID's and then Sum them for every day of the week.

    But if you can help me do this for Mon, I can replicate for other days.

    Thanks!
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    So the Flag_ID controls the point value?

    Choose([Flag_ID], 5, 2.5, 0)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Rangerguy2000's Avatar
    Rangerguy2000 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Hamilton, ON, Canada
    Posts
    32
    where did your earlier solution go? The one where you suggested to rename a table and make a new table...

    I noticed you replied about an hour or so ago .. I tried that and it worked but had a few more questions on it.

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

    Report Based Calculations

    Yes and No.
    A combination of Metric_ID and Flag_ID controls the Points a Driver earns.

    e.g.
    Metric_ID 1 - Shift OnTime - Max Points 5.
    Metric_ID 2 - Scanned Stops - Max Points 10.
    Metric_ID 5 - Missed Deliveries - Max Points 15.


    And there are 3 types of Flags that a Driver can perform:
    e.g.
    Flag_ID - Performance - Controllable
    Flag_ID #1 - Y/- Driver earns Full Points (Driver Performed the function successfully so no need to report to on Controllable)
    Flag_ID #2 - N/N Driver earns 1/2 points (Driver didn't Perform the function successfully and it was Non-Controllable due to weather etc.)
    Flag_ID #3 - N/Y Driver earns 0 points (Driver didn't Perform the function successfully and it Controllable due to driver fault)

    So say for example we are reporting on Metric_ID #1 & #5 for Driver ..


    Metric_ID #1: Max Points Available = 5
    Scenario 1 Scenario 2 Scenario 3
    Metric_ID = 1 Metric_ID = 1 Metric_ID = 1
    Flag_ID = 1 Flag_ID = 2 Flag_ID = 3
    Driver earns 5 points Driver earns 2.5 points Driver earns 0 points


    Metric_ID #5: Max Points Available = 15

    Scenario 1 Scenario 2 Scenario 3
    Metric_ID = 5 Metric_ID = 5 Metric_ID = 5
    Flag_ID = 1 Flag_ID = 2 Flag_ID = 3
    Driver earns 15 points Driver earns 7.5 points Driver earns 0 points


    So after inputting all the Metric_ID's and all the relevant Flag_ID's daily, a driver earns total score out of 100, and then a grade as well.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You read my post before I edited it. I re-read the info and felt I did not have full understanding so removed.

    You have 11 Metric_ID's. What max point value for each? Create a field called Weight in tbl4_MetricIDs for this. And another field in tbl5_FlagIDs called Factor with values of 1, 0.5, 0.

    Now pull both tables into query and multiply Weight * Factor.

    SELECT tbl3_MetricDetails.*, [Weight]*[Factor] AS Points
    FROM tbl5_MetricFlags RIGHT JOIN (tbl4_MetricIDs RIGHT JOIN tbl3_MetricDetails ON tbl4_MetricIDs.Metric_ID = tbl3_MetricDetails.Metric_ID) ON tbl5_MetricFlags.Flag_ID = tbl3_MetricDetails.Flag_ID;

    New table not required after all.

    The CROSSTAB:

    TRANSFORM Sum([Weight]*[Factor]) AS PointsEarned
    SELECT tbl3_MetricDetails.Metric_ID, [tbl3_metricDetails.Driver_ID] & " " & [First_Name] & " " & [Last_Name] AS Driver, tbl4_MetricIDs.Metric_Name
    FROM tbl5_MetricFlags RIGHT JOIN (tbl4_MetricIDs RIGHT JOIN (tbl2_Drivers RIGHT JOIN tbl3_MetricDetails ON tbl2_Drivers.Driver_ID = tbl3_MetricDetails.Driver_ID) ON tbl4_MetricIDs.Metric_ID = tbl3_MetricDetails.Metric_ID) ON tbl5_MetricFlags.Flag_ID = tbl3_MetricDetails.Flag_ID
    GROUP BY tbl3_MetricDetails.Metric_ID, [tbl3_metricDetails.Driver_ID] & " " & [First_Name] & " " & [Last_Name], tbl4_MetricIDs.Metric_Name
    ORDER BY tbl3_MetricDetails.Metric_ID, [tbl3_metricDetails.Driver_ID] & " " & [First_Name] & " " & [Last_Name]
    PIVOT WeekdayName(Weekday([Record_Date],2),True,2) In ("Mon","Tue","Wed","Thu","Fri");
    Last edited by June7; 12-07-2018 at 08:37 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

    Report Based Calculations

    Hi I am having issues updating the tbl3_MetricsDetails table using the qry1Sub_DriversMetricDetails ..

    The query is updating the Record_Date, Driver_ID, Metric_ID, Flag_ID but it is not updating the PointsEarned.
    It should be PointsEarned = [tbl4_MetricsIDs.Metric_Weight] * [tbl5_FlagIDs.Flag_Factor]

    Attaching the DB.
    TY!
    Attached Files Attached Files

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    No need for that field. Calculate this value when needed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Rangerguy2000's Avatar
    Rangerguy2000 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Hamilton, ON, Canada
    Posts
    32
    The reason I need the "Points" in the tbl3_MetricDetails is when I call the ..
    frm1Main_DriversDetails which has the frmSub1_DriverMetricsDetails ...

    I need to show the Points a driver has for any given date/metric and also to calculate it in real-time when inputting new data using the above forms.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Can use the first query I showed as form RecordSource.

    If you include other fields from tbl4 and tbl5, do not allow edits to those fields - set textboxes as Locked Yes and TabStop No.

    Saving calculated value would require code (macro or VBA). This code would have to execute anytime either of the two field values is changed. Possibly in the form BeforeUpdate event.


    Data will not recalculate until record is committed to table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Rangerguy2000's Avatar
    Rangerguy2000 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Hamilton, ON, Canada
    Posts
    32
    ..................................................
    Private Sub Record_Date_AfterUpdate()

    CurrentDb.Execute "INSERT INTO tbl3_MetricDetails(Record_Date, Driver_ID, Metric_ID) SELECT '" & Me.Record_Date & "' AS RD, '" & Me.cboDriver_ID & "' AS DID, Metric_ID FROM tbl4_MetricIDs"

    Me.frmSub_MetricDetails.Requery

    End Sub
    .................................................. ..............................

    I am using the above VBA code to update the fields in tbl3_MetricDetails using the frm1Sub_DriversMetricsDetails which is driven by the qry1Sub_DriversMetricsDetails.

    I am not sure how can I use [Metric_Weight]*[Flag_Factor] to update the Points field in tbl3_MetricDetails.

    Any help is appreciated.

  13. #13
    Rangerguy2000's Avatar
    Rangerguy2000 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Hamilton, ON, Canada
    Posts
    32
    OK , I solved this dilemma .. I used a nested IIF statement with OR and AND clauses to calculate the Point in the form.
    Although it is not a Multiplication and is fooling the system, it works though.
    Below is the IIF statemennt I used.
    ..........................
    IIf(([Metric_ID]=1 Or [Metric_ID]=4 Or [Metric_ID]=6 Or [Metric_ID]=11) And ([Flag_ID]=1),5,IIf(([Metric_ID]=1 Or [Metric_ID]=4 Or [Metric_ID]=6 Or [Metric_ID]=11) And ([Flag_ID]=2),2.5,IIf(([Metric_ID]=2 Or [Metric_ID]=3 Or [Metric_ID]=8 Or [Metric_ID]=9 Or [Metric_ID]=10) And ([Flag_ID]=1),10,IIf(([Metric_ID]=2 Or [Metric_ID]=3 Or [Metric_ID]=8 Or [Metric_ID]=9 Or [Metric_ID]=10) And ([Flag_ID]=2),5,IIf(([Metric_ID]=5 Or [Metric_ID]=7) And ([Flag_ID]=1),15,IIf(([Metric_ID]=5 Or [Metric_ID]=7) And ([Flag_ID]=2),7.5,0))))))
    ..........................

  14. #14
    Rangerguy2000's Avatar
    Rangerguy2000 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Hamilton, ON, Canada
    Posts
    32
    OK, one thing I am not able to solve is ..

    I want to pre-load all the MetricIDs from tbl_MetricIDs when I open the frm1MainDriversMetricDetails which then calls frm1Sub_DriversMetricDetails ..

    Any suggestions?

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Don't know what you mean by 'pre-load', load where?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 07-07-2016, 02:34 AM
  2. Replies: 9
    Last Post: 12-17-2014, 06:13 PM
  3. Calculations based columns in a entry form
    By Jamescdawson in forum Access
    Replies: 8
    Last Post: 06-11-2012, 07:09 AM
  4. Incorrect Calculations in Report
    By Bridiewms in forum Reports
    Replies: 6
    Last Post: 01-30-2012, 10:28 AM
  5. Replies: 22
    Last Post: 03-15-2011, 07:17 AM

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