No so I understand this righ this code goes in SQL View under the query right?
Then how do I do the other query it askes me to save the SQL query then can
I build another query to display on my report?

"Ken Sheridan" wrote:

Try this:

SELECT T1.[Vendor Number],
(SELECT COUNT(*)
FROM [tblGrant Completion-March] AS T2
WHERE T2. [Vendor Number] = T1. [Vendor Number]
AND T2.[ZEZA] = TRUE) AS CountOfMonths,
theMonth
FROM [tblGrant Completion-March] AS T1
WHERE T1.ZEZA = TRUE
AND
(SELECT COUNT(*)
FROM [tblGrant Completion-March] AS T3
WHERE T3. [Vendor Number] = T1. [Vendor Number]
AND T3.[ZEZA] = TRUE)=6;

The way it works is this: The subquery in the outer query's SELECT clause
counts the number of rows in the table where ZEZA is TRUE and the Vendor
Number is the same as the current Vendor number in the outer query. The two
instances of the table are distinguished by giving them aliases T1 and T2.
The WHERE clause of the outer query restricts the rows returned to those
where ZEZA = TRUE. The subquery in the outer query's WHERE clause
restricts the rows returned by the outer query to those where there are at
least 6 whwre ZEZA = TRUE.

You can then base a report on this and group the report by Vendor Number.
Put Vendor Number and CountofMonths in a group header and theMonth in the
detail section. No subreport is needed.

Ken Sheridan
Stafford, England

"Chey" wrote:

If you could help that would be great.
I have asked this question numerouse times, and have had no good luck. I
take a little from everyone to try to get this to work
I have one table
tblGrant Completion-March
This has fields
ZEZA-y/n box
Vendor Number
theMonth

I have a query for the main report-this only has the vendor number
and a query for the subreport-this has vendor number, month, ZEZE
I have an unboud that has count in it on the subreport
I only want to see when ZEZA has been check for a peroid of 6 months.
Right now I can see how many times it has been checked for each provider.
I then did and if statement to make it visable. This casused just a blank
spot. Then I did the can shrink. This still left a blank spot.

What I want to see
Ex

ABC12345 (6)
January 05
Februay 05
March 05
April 05
May 05
June 05

ABC12345-Vendor Number
(6) amount of months
Jan 05-June 05 months that ZEZA were checked.

Please help me, I don't know what to do.
I need to know how to write the query properly and even if I need a subreport.
Plus where in the query to write this.