Count in a query

[joebb3]

First, I appologise if this has been asked before. I have spent the last few days looking, but I haven't found my answer.

I have to find a way to pull table data for ONLY people that meet a specific criteria a set number of times. Here is an example:

Data:

Name YearOfTest Result
Joe 2003 Fail
Joe 2004 Pass
Joe 2005 Fail
John 2003 Fail
John 2004 Fail
John 2005 Pass
Mary 2003 Fail
Mary 2004 Pass
Mary 2005 Fail
Jane 2003 Fail
Jane 2004 Pass
Jane 2005 Pass

I need to be able to have the report return the members (and all of their data) that have failed at least twice and also failed in 2005. With the data above, the report should look like:

Report:
Name YearOfTest Result
Joe 2003 Fail
Joe 2004 Pass
Joe 2005 Fail

What I HAVE is a report that will show Joe, John and Mary and HIDE Jane (because her COUNT is less than 2). It doesn't take date into account at all.

I am completely stumped.

Thank you!
Joe

 

[Remou]

From your data I get Mary, too, she has the same results as Joe. This is what I tried:

[tt]SELECT DISTINCTROW tblTable.FName, tblTable.YearOfTest, tblTable.Result
FROM tblTable
WHERE (((tblTable.FName) In (SELECT A.FName
FROM tblTable AS A
WHERE (((A.Result)="Fail"))
GROUP BY A.FName
HAVING (((Count(A.Result))>1));
) And (tblTable.FName) In (Select FName From tblTable B Where B.YearOfTest=2005 And Result="Fail")));[/tt]

 

[joebb3]

Looks like it will work, but I have 3 joined tables that I pull my REAL data from. Members, Cycles and BCA.

Here is what I have for the query:

SELECT DISTINCTROW Members.ID, Members.uic, Members.ssn, Members.name, Members.rank, Cycles.cycle, Cycles.cyclesort, Cycles.overall, BCA.[hgt/wgtok], BCA.BFOK, Cycles.status, BCA.BCstatus, BCA.[bf%], UICandNames.ShortTitle
FROM (Members INNER JOIN UICandNames ON Members.uic = UICandNames.uic) INNER JOIN (Cycles INNER JOIN BCA ON Cycles.CID = BCA.CID) ON Members.ID = Cycles.ID
WHERE (((Members.name) In (SELECT A.name 
FROM Members AS A 
WHERE (((A.overall) = "FAIL  "))
GROUP BY A.name
HAVING (((Count(A.overall))>1));
) And (Members.name) In (Select name From Cycles B Where B.cyclesort >= 2006 and overall = "FAIL  ")));

What I'm getting is a box asking for "A.overall"

Also, I need to order this by UIC,NAME,Cyclesort in that order.

Thanks so much!
Joe

 

[Remou]

Overall does not appear to be a field in Members.

 

[joebb3]

It isn't... Its in Cycles. But the syntax eludes me.