Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2005
    Posts
    5

    Post Unanswered: Access 2000 score card...

    I'm in the need of some advice on how to setup a database. The database will be used to keep track of semi driver stats. Let me explain, each semi driver starts with 1,000 points each period (3months) and during the course of the period they can earn or lose points. They can earn points for perfect attendance, no injuries, or written compliments. The drivers lose points for absences, accidents, misdeliveries, complaints, and so on. Each item has it's own point value, for example a written compliment is worth 50 points and perfect attendance is worth 25 on the other hand unexcused absences are worth -500 points.

    The points earned or losed during the quarter are then added up and that's the total points the driver has earned duing that quarter (3months). At the end of the quarter drivers with a certain amount of points are awarded prizes. Also at the end of the year the total points from each quarter are added up and a driver of a year is awarded.

    Along with that the database will need to allow me to add and remove drivers, either for new hires or terminations.


    What I'm needing help with is what's the most effective way of setting something like this up? Something that will allow me to add and remove drivers, keep track of their points on a month to month basis and add their earned and losed points every three months while adding their 1,000 points they get each quarter. If anyone could help I would GREATLY appreciate it.


    -Michael

  2. #2
    Join Date
    Apr 2004
    Posts
    18
    At first thought. This really is quite simple.

    You'll need a main table which holds all the points transactions by date and driver. The sum of any records for a particular driver and date range will give you his total points for that period.

    Each record will need a reason code i.e. P for intial points (+1000) on the first date of the month. And then others like U for unathorised absence points (-500) and so forth. The coding will allow you to analyse not only drivers and dates but what they are gaining losing points for etc. I would recommend a descriptive field to explain what the entry was made for i.e. "didn't ring in to report absence" etc

    You will obviously then need to build some forms to help you enter all of these point transactions and to manage drivers etc. Then you can write some reports to look at the main table.

    Keep it simple , but effective.
    DAO/ADO/VB.net/VBA/Access2K/Excel2K/SQL2000/Essbase/OFA/Oracle OPM, IM, EAM/Discoverer/ SQLPlus

  3. #3
    Join Date
    Oct 2005
    Posts
    5
    sqlappboy - So you're recommending to create one big table with all the drivers and each point both plus and minus? How would I break it up into seperate months?

    I was thinking of creating one table that had all the positive reasons and it's point value, the same for negative and one for drivers. However I'm still wondering the samething - how do I make it so I can keep track by month by month? The 1000 points are for the total of 3 months or 1 quarter, so I'd need to add 1000 to the sum of 3months +/- totals too.



    -Michael

  4. #4
    Join Date
    Apr 2004
    Posts
    18
    If the data is in one table you need only to then use date ranged queries which can then be summarised, by driver id and points etc Splitting the data up will just over complicate the process.

    I obviously would suggest that you keep driver details, point code details etc in separate lookup tables.
    DAO/ADO/VB.net/VBA/Access2K/Excel2K/SQL2000/Essbase/OFA/Oracle OPM, IM, EAM/Discoverer/ SQLPlus

  5. #5
    Join Date
    Oct 2005
    Posts
    5
    sqlappboy,

    If in a single table wouldn't I have to manually enter the point value for each positive or negative item?

    For example if I have driver John Smith with a perfect attendence I'd have to enter 500 for that entry? Along with that the following month I'd have to enter John Smith's name again and enter in his point value for each section, correct?

    I'm sorry for my newbie questions, I just don't have any real experience with Access and I'm having a hard time picturing this. You wouldn't happen to know of a template that follows this similar setup that I'm looking for?


    -Michael

  6. #6
    Join Date
    Oct 2005
    Posts
    5
    BTW - Currently we are doing this in Excel. I've got a spreadsheet with 4 worksheets. One for quarter total that shows the totals for all drivers, the other three worksheets are the months within that quarter. We have a formula that tallies each worksheet then displays the total on worksheet one (total).

    I could post that if that would be any help.


    -Michael

  7. #7
    Join Date
    Apr 2004
    Posts
    18
    Surely you could automate the adding of the point scoring records for say the inital 1000 points and a default of 500 for attendance in a beginning of the month run. These could then be updated / removed when absence is entered etc.

    At the end of the day, most of the points transactions will need to be keyed at some point. The rest can be built as a default for each month.
    DAO/ADO/VB.net/VBA/Access2K/Excel2K/SQL2000/Essbase/OFA/Oracle OPM, IM, EAM/Discoverer/ SQLPlus

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by WoodiE55
    BTW - Currently we are doing this in Excel. I've got a spreadsheet with 4 worksheets. One for quarter total that shows the totals for all drivers, the other three worksheets are the months within that quarter. We have a formula that tallies each worksheet then displays the total on worksheet one (total).

    I could post that if that would be any help.


    -Michael
    It shows. Sounds like you're trying to use access just like it was excel.

    You can break out individual months by using grouping levels when you write the reports.

    You only need a minimum of three tables to do this. Just remember, similar data almost always should be in the same table...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Michael,

    I recommend several tables, as follows:
    tblDrivers - this holds basic info about each driver (name, hire date, etc.)
    tblPoints - this holds the "scoring rules" (item, point value, etc.)
    tblScoreCard - this holds the list of points gained, lost, etc.

    tblDrivers will have a key field (make it AutoNumber), lets call it DriverID. Use a basic Form to maintain this table.

    tblPoints will have a key field (make it AutoNumber), let's call it PointID. Use a basic Form to maintain this table.

    For table score card, you store the DriverID, the PointID, the date, and a text field to hold the reason. Use a form linked to tblScoreCard, with a Combo Box for the driver and the points. The combo boxes will work with the ID fields, but display the Text, making it easier for you to do the data entry. The Form and the Combo Boxes can be created using Wizards.

    To make it a bit nicer, make the score card form a sub form of the drivers form, using the DriverID as the link field. This way, one form comes up, you edit the driver info and the sub form displays all the points gained and lost by that driver.

    You can assemble the 3 tables in a query. Use a totals query, grouping by date ranges, to get the points. You can also export queries and reports to Excel, if that is for convienent for you.

    There are a number of other features you could add later. One thing to be warry of: if you change the point value of an item, it changes that point value globally (past and future) for that item. A small improvement would be to add a Points field to tblScoreCard. A small snippet of code would copy the point value of the item to the score card table. That way, future adjustments to the point values do not alter past points earned or lost.

    Have fun!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  10. #10
    Join Date
    Apr 2004
    Posts
    18
    Quote Originally Posted by sqlappboy
    If the data is in one table you need only to then use date ranged queries which can then be summarised, by driver id and points etc Splitting the data up will just over complicate the process.

    I obviously would suggest that you keep driver details, point code details etc in separate lookup tables.
    Tcace - Thanks for the long winded version of what I already told him, eh

    Michael - why don't you attach your spreadsheet and I'll build you the shell of the db - is Access2k ok ? You can put a cheque in the post.....
    Last edited by sqlappboy; 10-13-05 at 11:21.
    DAO/ADO/VB.net/VBA/Access2K/Excel2K/SQL2000/Essbase/OFA/Oracle OPM, IM, EAM/Discoverer/ SQLPlus

  11. #11
    Join Date
    Oct 2005
    Posts
    5
    Quote Originally Posted by Teddy
    It shows. Sounds like you're trying to use access just like it was excel...
    I know and I'm sorry. I know very very little about Access and trying to go from one to the other is really not as easy as I thought it would be.


    tcace - Thanks so much for your "long winded version" Being the newbie I am posts like that really help. I was about to give up on this project and just stick to Excel but I MIGHT have gotten an idea or two from this post. I'll have to give your suggestion a try if I can figure out how to get it all done.

    Attached is the Excel spreadsheet I've been working with. At this time only months April - August has data scores in it as well as the total worksheet. Jan - March we wasn't using this and Oct on obviously isn't over or here yet. Maybe if you see what I'm doing that might make it more clear on what I'm trying to get done.


    Thanks,

    -Michael
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •