1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access DB Question Regarding Query

Discussion in 'Software Development' started by MDDaniels, Jan 7, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. MDDaniels

    MDDaniels Thread Starter

    Joined:
    Jan 14, 2009
    Messages:
    27
    Is it possible to have a table with a column for "name" and a column for "Prior Year Eval Rate" and then in a query, everytime a particular name shows up in the table it will find the "Prior Year Eval Rate" last recorded for that name? The example below is showing a table of information going to folks and I want to pull what the prior year eval rate was from the Eval Review Date of 1/1/2012.

    Eval Packet to Manager qryDate to HRCOS ReasonEmp NameDept FromEval To ManagerEval Review DateEffective DateEval PayDateEval to Director DateCommentsRN Demographic SurveyPrior Year Eval Rate
    Annual ReviewRhoda Meza61412/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewAnita Stowers66812/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewAmanda Machen76212/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewMichelle Breaud75512/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewTerra Garrison75512/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewAllison Stephens75512/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewNancy Brock75212/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewMelynda Williams75212/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewDaniel Harbin66812/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewSherri Moore621112/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewSallie Heard61212/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewSusan Kelley61112/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewLaura Kesler61112/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewRichard Bailey61012/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewJessica Bleckley61012/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewRobin Michael61012/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewJanet Phillips61012/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewMarilyn Ashley61212/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewLaura Beauford61212/3/20121/1/20132/3/20132/21/20131/28/2012
    No

    Annual ReviewSandra Jacobs621112/3/20121/1/20132/3/20132/21/20131/28/2012
    No
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    post the database in version 2003 or lower and we can help you better. Just include sample data with only the tables relevant.
     
  3. MDDaniels

    MDDaniels Thread Starter

    Joined:
    Jan 14, 2009
    Messages:
    27
    I do not have access to 2003.
     
  4. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    if you have an older version then use that, if you have newer you can change your settings to create an older version..I have 2007 so I can work up until that version... I was just suggesting to save as a lower version so more people could look at it and offer help.
     
  5. MDDaniels

    MDDaniels Thread Starter

    Joined:
    Jan 14, 2009
    Messages:
    27
    Ziggy, I have tried to upload a very small version 2007 of my Access DB showing the table and query. Your system says it is an invalid file. I saved it as a 2007. Do you know what I need to do?

    Thanks,
    Marie
     
  6. MDDaniels

    MDDaniels Thread Starter

    Joined:
    Jan 14, 2009
    Messages:
    27
    Ziggy, I just had WinZip installed so I think the DB is attached. Please let me know. My goal is this...when I add new Annual Review employee names to the table, I would the query to check for Prior Years Evaluation Score and populate that field in the query. Example, I have listed employee 3 twice. The first time the employee's Prior Years Eval score is 0. I've listed him again without a date to HR so it will appear in the query and I want his Prior Years Eval score of 0 to appear in the query. Is this possible?

    Thanks for your help,
    Marie
     

    Attached Files:

  7. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    hi sorry, haven't been on the site for over a week. I will take a look over the weekend
     
  8. MDDaniels

    MDDaniels Thread Starter

    Joined:
    Jan 14, 2009
    Messages:
    27
    Thank you....no one else has replied.
     
  9. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    Let me know if this works for you. I first queried the table with calculation to flag Previous year and then link it back to the table in another query. You don't need a field in the table for this as it is a calculated result.

    I hope this is what you are looking for.
     

    Attached Files:

  10. MDDaniels

    MDDaniels Thread Starter

    Joined:
    Jan 14, 2009
    Messages:
    27
    Hi Ziggy,

    I think you example is going to work. I've created your example in my DB and it seems to be working, with the exception that something is making one of my entries to duplicate.

    I just need to study what you did to understand it. You were a big help...thank you:)
     
  11. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    no probs, you need to make sure that the second query for prev year score is only returning 1 record. If it is not you need to adjust the crieria. when working with Group by in the query often you will see that it will only group so far because there is a field that has different values (for a given record).

    if you can't figure it out repost with other scenarios that are affecting the results. and i will take a look.
     
  12. MDDaniels

    MDDaniels Thread Starter

    Joined:
    Jan 14, 2009
    Messages:
    27
    Ziggy, what should the criteria be adjusted to? When the query titled "qryPrevYear" is ran alone, it is pulling duplicated names.. If I put "Is Null" in the Date to HR column nothing is returned. I am a little confused.
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1084126

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice