Results 1 to 10 of 10
  1. #1
    Join Date
    May 2010
    Posts
    8

    Question Unanswered: HELP Aggregate Query for employee productivity

    I have made a form for my employees to use to enter there totals of the things they do at the end of their shift. each data element is worth a certain value. If I wanted there productivity totals what would be the easiest way to query this?

    I will use a small example here:

    |Action1|Action1Value|Action2|Action2Value|

    The action values are set to autofill in once they enter in there totals.
    For instance: my employee did 2 of Action1, and Action1Value was worth 0.5 points. He also did 4 of Action2, which was worth 1 point. how would I get my totals for each employee at the end of my day?
    If this qill have to be done in SQL or VBA could you be so kind as to provide an example. I want this to fill in a new field called "totals"

    thanks, and sorry if I didnt explain this as well as I probably should have.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If those are your fields, the table doesn't look normalized. Typically you would have a related table that would have a record for each action. If I did 4 actions, I'd have 4 records in that table. More info about your data might help one of us recommend a proper table structure. With a properly normalized table, your solution is probably a very simple totals query.
    Paul

  3. #3
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by bcaery View Post
    I have made a form for my employees to use to enter there totals of the things they do at the end of their shift. each data element is worth a certain value. If I wanted there productivity totals what would be the easiest way to query this?

    I will use a small example here:

    |Action1|Action1Value|Action2|Action2Value|

    The action values are set to autofill in once they enter in there totals.
    For instance: my employee did 2 of Action1, and Action1Value was worth 0.5 points. He also did 4 of Action2, which was worth 1 point. how would I get my totals for each employee at the end of my day?
    If this qill have to be done in SQL or VBA could you be so kind as to provide an example. I want this to fill in a new field called "totals"

    thanks, and sorry if I didnt explain this as well as I probably should have.
    Are you saying that the values are all on a single record (row)

    Or are you suing a query to get he data un-normalized not a single r record (row)?

    The following is not a designed using the rules of normalization since the table has repeating fields:

    |Action1|Action1Value|Action2|Action2Value|

    The about is how you would do a spreadsheet.

    If the table was designed using the rules of normalization it would look like this:

    tblShiftActions
    - ShiftActionsID - autonumber - primary key
    - ShiftID - foreign key to the Shift that identifies the shift, emplyee, etc
    - ActionID - foreign key to the Actions look up table
    - ActionValue - looked up and store to perform the calculation

    Now you can do a simple query to calculate the total points earned per action and also do a sum.

    EDIT:
    I was typing as Paul was. I agree with Paul.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  4. #4
    Join Date
    May 2010
    Posts
    8
    Quote Originally Posted by pbaldy View Post
    If those are your fields, the table doesn't look normalized. Typically you would have a related table that would have a record for each action. If I did 4 actions, I'd have 4 records in that table. More info about your data might help one of us recommend a proper table structure. With a properly normalized table, your solution is probably a very simple totals query.
    I have now created a seperate table for the values based on your suggestion (it makes sense)

    Table1: (these numbers are input by my employees in the form)
    |Action1|Action2|Action3|

    Table2: (the values table)
    |Action1Value|Action2Value|Action3Value|

    I believe this is what you were reffering to?

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No:

    Code:
    Action  ActionValue
        1           .5
        3            2
    Presumably with an additional key field that would relate back to your main data table.
    Paul

  6. #6
    Join Date
    May 2010
    Posts
    8
    Doh! ok, the values table is now like this. thank you Paul.
    Now, using that tablewith my current data entry table what would be the easiest method?
    I apologize, I am used to excel for aggregates
    Last edited by bcaery; 08-05-10 at 22:24.

  7. #7
    Join Date
    May 2010
    Posts
    8
    table: ShiftActions
    - ShiftActionsID - autonumber - primary key
    - ShiftID - foreign key to the Shift that identifies the shift, emplyee, etc
    - ActionID - foreign key to the Actions look up table
    - ActionValue - looked up and store to perform the calculation

    I am new to this aspect of access so please bear with me.

    using the table above with ActionValue being looked up from the table that paul suggested. how would i perform the calculation? or am I still getting it wrong?

    edit: do you think making a report would be easier?
    Last edited by bcaery; 08-05-10 at 22:53.

  8. #8
    Join Date
    May 2010
    Posts
    8
    Looks like if the values will remain constant I can just add them to my query.

    example:

    SELECT *
    Action1 * .5 as Action1Total, Action2 * 3 as Action3Total
    FROM Table1

    this worked on my test DB at home, I fear this will have to do untill I can get my tables done correctly. this is just a makeshift report untill our system is back online. Your ideas?

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post the db?
    Paul

  10. #10
    Join Date
    May 2010
    Posts
    8
    keep in mind this is just my test db at home. here is the link to DL the Access 2007 file

    2shared - download PRODEXAMPLE.accdb

    ughh.. sorry, free upload service. youll have to click the small text in the bottom right hand corner of the box... "Save file to your PC: click here"

    I made it into all one big aggregate

    example: Action1 * 0.5 + Action2 * 3 as ProductivityTotals

    also, didnt make the form start with new record since this is just for testing

    Just looking for an experts opinion from one of you guys.
    Last edited by bcaery; 08-06-10 at 01:17.

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
  •