Solved: Access DB Question Regarding Query

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 
Joined
Jun 17, 2002
Messages
2,556
post the database in version 2003 or lower and we can help you better. Just include sample data with only the tables relevant.
 
Joined
Jun 17, 2002
Messages
2,556
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.
 

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
 

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
 

Attachments

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

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:)
 
Joined
Jun 17, 2002
Messages
2,556
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.
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top