Results 1 to 10 of 10
  1. #1
    eripsni is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    6

    Any ideas?

    I need to create a database to track various performance metrics of different people. My issues are these:
    1) Each person has different metrics (some overlap, but there can be as many as 12 different metrics, with each person having 2-3 metrics being tracked)
    2) Each person has a different acceptable level for each metric that they are being tracked on.


    3) Each person is tracked at different frequencies (some quarterly, others semi-annually - and they don't all have it on the same months i.e. not everyone is reviewed Dec 31, Mar 30, Jun 30, Sep 30)

    Any ideas?

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    All of this can be done with Access. Is there a specific question? Are you asking about data structure or, building a form or something else?

  3. #3
    eripsni is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    6
    I created the following tables:
    1) a table listing the metric types
    2) a table listing the id of each person and which metrics they are to have tracked
    3) a table with the following columns: id, metric type (dropdown from other table), metric threshold for that person
    4) a table with the following columns: id, metric type, reporting date, reported value

    The problem I'm having is writing a query that would show which didn't meet their expected values. Can it know that the value for each metric will be different based on the person? How would I go about doing this?

    The other problem I can see having is what to do with the data as the next reporting period comes up. I'd like to store the previous info as well, rather than just having the metric threshold and the most recent value.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Can you post a sample database, one that contains garbage information but you know what you want your expected values to be in a query. I would rather not have to build an entire example.

  5. #5
    eripsni is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    6

    Re:

    EDIT - I was going to attach a DB, but it's 1.98MB. How on earth does one get it down to 500kb to be under the limit?

    -------------------------------------

    Attached you'll find a sample of what I'm trying to do. I'd like to be able to see when any of the associates actual values do not meet the threshold. In this case, associates 1234 and 9714 passed the thresholds, while associate 6429 had a bad quarter and didn't meet goal on any of his three metrics.

    In addition, it'd be great to be able to search by date as well. Can you point me in the right direction? Would this be best served through a report rather than a query? How would one go about doing this?

    Thanks!

  6. #6
    eripsni is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    6
    I'll try zipping it once I get home for the day.

    Thanks.

  7. #7
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Quote Originally Posted by eripsni View Post
    EDIT - I was going to attach a DB, but it's 1.98MB. How on earth does one get it down to 500kb to be under the limit?
    Have you run COMPACT AND REPAIR on it first and then ZIPPED it (right-click on the file and select SEND TO > COMPRESSED FOLDER) and see where that gets you.

  8. #8
    eripsni is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    6
    Let's try this again - thanks!

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    There are a number of problems with this database as far as the links go. You should never store anything but the unique value of each table in any child table (you were storing names) I've changed the relationships and some of the ID fields (you named each autonumber field ID I gave them more unique names so it's easy to tell which ID is on which table) and I created a query that should be doing what you want, I included every training item but you can filter it with the same mechanic.

    Here's a copy of your database back.

  10. #10
    eripsni is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    6
    Thanks for your help - I'm new to Access and appreciate your assistance!

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

Similar Threads

  1. Ideas on shorter code for cascading updates
    By usmcgrunt in forum Programming
    Replies: 3
    Last Post: 12-03-2010, 12:17 PM
  2. Slow Runtimes any ideas to speed up process?
    By salisbut in forum Access
    Replies: 9
    Last Post: 09-16-2010, 12:14 PM
  3. help please. i need basic access privacy/protection ideas
    By helpporfavorplz in forum Security
    Replies: 1
    Last Post: 02-20-2010, 05:32 PM
  4. Replies: 0
    Last Post: 09-07-2009, 04:27 PM
  5. Need some ideas
    By amauricio2 in forum Database Design
    Replies: 0
    Last Post: 03-02-2009, 11:03 AM

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