Results 1 to 8 of 8

Team Points tracking database

  1. #1
    Daisy509th is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    3

    Team Points tracking database

    So I am working on a points tracking database. I have four table i use.
    tblEmployee
    EmployeeName
    TeamName
    NID
    PID
    DateofIncident

    tblScoringPositive
    PID
    Pdescription
    Ppoints

    tblScoringNegative
    NID
    Ndescription
    Npoints

    The fourth table is a make table query which is made every time points are add for a real time static record of where the teams are point wise.

    I have several queries and forms. I can track points at an employee level with description the problem is when i try to track them at a team level.
    I have written several queries base off of the employee level data but cant seem to get all the points combined from all the different rows in the table and add up properly.

    So a record looks like this


    Employee ID EmployeeName TeamName NID PID DateofIncident
    1 Doe,John Misfits 8 1 12/1/2017
    2 smith,randy Dozens 1 7 12/2/2017
    3 Doe,John Misfits 1 4 12/10/2017

    So the ID's link back to a description and point value. One is suppose to equal zero but the zero doesn't seem to be getting enter into table.

    So the problem is I cant figure out how together all these values and add them up when refer back to teams. Any help or changes will be meet with great appreciation.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    11,933
    You have told us HOW you have done something. Readers need to know more about that something in order to offer focused advice.
    What exactly is a Team?
    What determines when Points are awarded/assigned?
    What are Positive and Negative Points?
    What is an Incident?

    Readers know nothing of you, your environment or the activities involved.
    Please describe to us in simple, plain terms (not database jargon) what your data base is about.
    Good luck.

  3. #3
    Daisy509th is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    3
    Ok, sorry about my confusion.

    The database is to track 12 teams through the year and by quarters for safery issues.

    Each team consist of 12 employees.
    Positive actions are things that are good for safety. There are 8 postive actions. The points award are 25, 15,10,5 depending on the actions.
    Negative action are things that are bad for safety. There are 8 negative actions too. The points award are -25,-15,-10,-5,-3,-2, and -1 depending on the actions.

    The safety commitee reviews the postive and negative action reports and scores them accordingly. Those reports are than turn in to me where i do the data entry.

    The database needs to:
    Track all positive and negative actions by employee
    Track all points earned and losted by employee
    Track all points earned by a team
    Need to be able to print reports that:
    a report that show all positive and negative action commited by an employee and the points gain or lost because of it. Also show the date of the action.
    a report that show the points earned or lost by an employee
    a report that show the points a team has earned.

    Also all dates need to be able to be print by:
    Day
    Week
    Month
    Quarter (Dec Jan Feb) quarter 1 (Mar Apr May) Quarter 2 ( June July Aug) Quarter 3 ( Sep Oct Nov) Quarter 4
    Year

    I hope this is closer to what you guys need to help me. I am brand new to group and this is my first post.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,926
    Don't use MAKE TABLE. A process that routinely modifies db structure is poor design.

    Ideally, save transaction records of points earned/lost and calculate balance from the raw data when needed. Properly designed, db should be able to report balance status for any designated period.

    This is just another version of inventory control - your inventory being points.

    Review http://allenbrowne.com/AppInventory.html
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    ridders52's Avatar
    ridders52 is online now Long time standing
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    306
    I think you can and should combine the two scoring tables

    TblScoring
    SID as PK
    Description
    Points (could be + or -)

    Or if you prefer keep Ppoints and Npoints

    Having just 2 tables will simplify data entry and also make creating your required queries straightforward.
    The reports will each need one of the queries created as their record source
    Colin (Mendip Data Systems)
    Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10, Website, email

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    11,933
    It seems that Points (positive/negative) are related to Incidents/Actions.
    I agree with Ridders that 1 table for Points, actually for Actions that has associated Points.
    An Employee commits/performs 1 or many Actions (on specific Dates or within certain quarters)
    An Employee is part of a Team

    General table set up (not cast in concrete)
    Team-->Employee-->EmployeePointsInTimePeriod<-----Actions

    Knowing PointsByEmployee in a time period, you can determine/report Team scores by Time period.

  7. #7
    Daisy509th is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    3
    Ok, so I going to create two tables for my database
    one being
    tblscoring
    fields being:
    Sid
    team
    employee
    actions
    action date
    Aid
    this will be my main table

    tblaction
    Aid
    action description
    action points +/-

    So, am I missing something or is this the right build for the tables?

  8. #8
    ridders52's Avatar
    ridders52 is online now Long time standing
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    306
    It's a bad idea to use spaces in field names and even worse to use special characters like +/-

    Personally, I prefer

    tblEmployee
    EmpID as PK field (or EID)
    FirstName
    LastName
    Team
    ActionDate
    ActionID (FK field)

    TblActions
    ActionID as PK (or AID)
    Action
    Points
    Colin (Mendip Data Systems)
    Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10, Website, email

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

Similar Threads

  1. Team Points System for Students
    By sphirst in forum Database Design
    Replies: 4
    Last Post: 01-08-2018, 06:49 AM
  2. team building database
    By ldypurple in forum Access
    Replies: 6
    Last Post: 03-06-2013, 03:34 PM
  3. Help with hockey team history database
    By Hockeyguy1924 in forum Database Design
    Replies: 20
    Last Post: 06-15-2012, 02:05 PM
  4. Sports Team database
    By Patriot7470 in forum Database Design
    Replies: 2
    Last Post: 03-17-2012, 12:50 PM
  5. help with tracking within a database
    By martyn in forum Queries
    Replies: 0
    Last Post: 10-07-2009, 01:15 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
  •  
Tech Forums: Microsoft Office Forums