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

    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 online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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's Avatar
    Daisy509th is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Central Texas
    Posts
    51
    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
    52,820
    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
    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
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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, 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
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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's Avatar
    Daisy509th is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Central Texas
    Posts
    51
    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
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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, 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

  9. #9
    Daisy509th's Avatar
    Daisy509th is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Central Texas
    Posts
    51
    I really appreciate the help to this point. I am still in need of some assists please.

    I have create two tables:
    tblActions(ActionID,Action, and Points)
    tblEmployee(FirstName,LastName,Team,ActionDate, and ActionID)

    I have also created two queries:
    One is used for data entry and the other returns an employee record.

    So here are my questions:

    1) on the employee qurrey how do I add together points for specific employees. I can make seprate querries for one employee and get it to add his points but dont think it efficient to write a querry for each individual employee or team for that matter.
    Need is to be able to report points for employee or team at any given point in time or for a specific time period. Plus, at times, I will need to report all employee or team points in a report instead of just a specific team or employee.

    2) On my data entry form can I use list or combo boxes for names, teams, and action slection and if so should I make seprate tables for these values and then use lookup fields? Or should I add the values directly to the boxes but this will make it hard to edit?

    Thanks again for the help and is there away on this site to make an answer as the solution?

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a link to info on database planning and design.
    Work through 1 or 2 of the tutorials from RogersAccessLibrary; you'll learn a procedure that can be used with any database. Probably the best 45 minutes you'll spend to understand database.

    Also, tables are where data is stored.
    Forms should be the means by which users interact with the database. Forms are really a mechanism (like a window) showing data from the underlying tables.

    It is critical in database to get your tables designed, and tested, to meet your business requirements. Build a model, create some test data, and test/exercise your model to ensure you can access or derive the information you need. Do this on paper(pencil/paper) before jumping in to physical database. The tutorials will guide you through the process.

    Good luck.

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Daisy

    First of all many thanks for the RP comment.

    Orange has given you some important advice. Please read it and the links carefully

    In answer to your last post, it was quicker to do this by creating an example database (attached)
    I've further modified the structure
    There are now 4 tables: tblEmployees, tblTeams, tblActions, tblEmpActions

    There are 4 queries: qryEmpActions, qryTotalEmpPoints, qryTotalTeamPoints, qryTotalTeamPointsDates

    I've created 2 forms based on underlying queries:
    a) frmEmploeeActions - used for data entry
    Note how the combo boxes cause the fields shaded in grey to be automatically populated
    b) frmTeamSummaryDates & subform
    Used to show the total points by team between 2 dates of your choice

    You should be able to do a similar thing for total points by employee between set dates

    Hopefully if you study the example db, you will be able to do the next steps yourself including creating reports based on suitable queries

    Also, to mark your thread solved, see the info on the green "How to Mark A Thread Solved" button at the top of the page

    Good luck
    Attached Files Attached Files
    Last edited by isladogs; 01-19-2018 at 10:38 AM.
    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

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