Results 1 to 2 of 2
  1. #1
    mick2000 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    9

    Team Statistics Report / Query. - removal of duplicates

    Good afternoon everyone,



    I am very much a newbie with MS Access. I have built a production tracker DB, which consolidates order Information, Product Barcode scanning & tracks the manufacture through to dispatch via courier.

    I am in the process of building statistical reports in an attempt to ensure we have accurate staff performance KPI's. The issue is the query / report, while I can get data on Barcodes scanned daily, monthly & between dates, the issue comes where for what ever reason a barcode has been scanned by the same person mulitple times. This obviously skews the results.

    Info:

    Barcode Tracking table has the following fields:

    ID BARCODE DATE TIME OPERATOR FUNCTION

    Each operator scans the barcode as they complete their work on the product. So we can report on a particular Barcode and date & time of each function.

    The individual query contains all fields above, and has criteria of "Enter Date" and "Operator Name" in their respective fields.

    The problem comes when running reports for 1 operator to see level of activity over a period. If they either accidentally or intentionally scan the same barcode twice or more, this over inflates their "count".

    I have tried a few suggestions from online, however none have been successful. Is their a reasonably straight forward way of sanitizing the report output to show totals accurately?

    Hope this ramble makes some sense, and obviously can supply any further information that may be required.

    Thanks in advance

    Mick

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try to leave the ID, Date and Time (last two are not the best field names as they are Access reserved words) out of the query output (obviously use the Date field with criteria to get your intervals) and use DISTINCT to remove the duplicates.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Rugby Team Management and Statistics
    By Ant81 in forum Access
    Replies: 1
    Last Post: 10-21-2018, 12:32 PM
  2. Replies: 4
    Last Post: 10-16-2014, 08:57 AM
  3. Statistics in report header
    By nurserich in forum Reports
    Replies: 5
    Last Post: 10-13-2014, 12:31 AM
  4. Moving Statistics below profit line on a report
    By Bill Singh in forum Access
    Replies: 1
    Last Post: 08-07-2014, 11:13 AM
  5. Replies: 1
    Last Post: 10-23-2013, 05:17 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