Query Help

[IntelSldr]

The end goal to this project is a system to track employee appraisals from when they are turned in, until they are coded into our computers.
I have two tables; tbl2009 has all of the employee data; name, SNN, etc. TblStatus has the current and past actions with the date/time that it was entered as that particular status... received, sent back, ready to code, coded etc.

I have my simple qry to determines if something is coded already, not a problem there. What I need is to create a qry for my records folks to determine when something is ready for coding. The problem I am running into is that when I qry for ="Ready To Code" I get the "Coded" as well. How do I query by social (SSN) for Action to equal "Ready To Code" and not "Coded" if the structure of the table looks like this:

SSN Action Date
111-11-1111 Received Jan 01, 2010 06:00am
111-11-1111 Ready To Code Jan 01, 2010 06:30am
111-11-1111 Coded Jan 01, 2010 07:00am
111-11-1111 Received Jan 01, 2010 06:00am
111-11-1111 Sent Back Jan 01, 2010 06:30am
111-11-1111 Received Jan 01, 2010 07:00am

I know I need to to separate the socials, look for "Ready To Code" look for "Coded" and ignore anything that does not say "Ready to Code" or if it does have "Ready To Code" I need to to ensure there is no "Coded" action associated with that SSN.

I still have time to get this done so if someone wants to point me in the right direction as opposed to doing the work for me, that is ok as well.

[ajalwaysus]

You may need to post your query that you are using now, because I at least cannot determine your issue without seeing the code.

-AJ

[IntelSldr]

There is no code right now. For my other query, do determine if something has been coded already, I simply used the designer within Access to look for Social Security Numbers that have a record with a "Coded" action.

"Coded" is my final action indication everything is complete. "Ready to Code is before that" and there are several others before that.

Assuming there are multiple entries in the table for each Social security number, each entry with its own action, (I did this because it was the only way I knew how to create a historical record of when the different actions occured)I need to find only those with "Ready for Coding" and not "Coding" but only return one entry per Social. If I do the query just looking for <> "Coded" I get all entries for that social that came before coded, "Received", "Entered","Ready for Coding", etc. So now I have 5 records for each name.

I am thinking now that it would be easy to do two queries...one to find <> "Coded" and another to find ="Ready for Coding" to create that report from.

[NeoPa]

@IntelSldr
I tend to agree. We don't know why you're having an issue as we cannot see the query you say you've tested.

An easy way of posting queries is simply to post the SQL (View / SQL) of your query. If we can see this we may be able to help you and guide you to a workable way of achieving what you require.

Please remember when posting any code (including SQL) that we require you to use the CODE tags provided.

[ADezii]

@IntelSldr
Try:

SELECT *
FROM tblStatus
WHERE tblStatus.Action="Ready To Code" And DCount("*", "tblStatus", "[Action] = 'Coded' and tblStatus.[SSN]=[SSN]") =0;