Can Shrink

ok on that note
what do t1 and t2 mean again. I keep reading it and don't get it.
when I want to view it I get the error mesg. invalid use of , ! or () in
query exp T2 [vendor number]=T1. [Vendor Number] And T2.[ZEZA]=True.
Is this because I have not replaced T1 and T2 if so again what do I put
here. Is it the table names?
If that is true I am pulling everything out of 1 table excpet my main table
has the vendor number.
Thanks for all your help. You have been the only one that this finally
makes sense.
Chey

"Ken Sheridan" wrote:

Yep, you do enter this in SQL view. The first thing then is to confirm that
it returns the correct rows by switching to datasheet view. If you are happy
that it does then the question is what else do you need to show in the
report. Say you want to show vendors' names from a Vendors table which the
Vendor Number field in tblGrant Completion-March references, then you'd add
the vendors table to the query and join it to tblGrant Completion-March on
the Vendor Number fields. I'd normally do this by simply typing in some
extra SQL, but you can also do it by switching to design view then adding the
table and joining it to tblGrant Completion-March visually in the usual way.
You can then drag whatever other fields you need into blank columns the query
design grid. Once you are satisfied that you have all the necessary fields
for the report save the query under any suitable name.

As far as the report is concerned you just enter the query name as its
RecordSource property in the report's properties sheet. As you no longer
need a subreport you can discard that. In the report's sorting and grouping
dialogue you'd group on either the Vendor Number or a vendor Name as the
first group level, providing the names are unique, and give it a group
header. The advantage of grouping on the name is that the report will show
the vendors in alphabetical order.

Fields such as Vendor name relating to the Vendor go in the group header so
you only see them once in the report. The Months go in the detail so that
they are listed individually.

Ken Sheridan
Stafford, England

"Chey" wrote:

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?

I just realized that there was a t3
I don't have 3 tables to do this from
Thanks
I have tblGrantCompletion-This one has ZEZA and Vendor number + sum
tblChild Care Providers-This one has vendor number and all there personal info

"Chey" wrote:

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.

Chey

It looks like one reason for the error is that you've missed the first dot
(after T2) from:

T2.[vendor number]=T1.[Vendor Number]

Also there should be no spaces before or after the dots. That was my error,
though, and probably results from the fact that I draft replies in Word,
which has a nasty habit of sometimes inserting spaces after a dot without my
noticing it.

The T1, T2 and T3 are simply aliases for the table names. You assign an
alias in a query like this:

FROM [tblGrant Completion-March] AS T2

The reason for this is because the outer query and the two subqueries all
use the same table and have to compare values of the Vendor Number column
(column is really the correct name for a 'field' in tables) between different
instances of the table. To distinguish each instance of the table,
therefore, you give them separate aliases. The usual convention is to use
the first letter of the table name followed by a number. You don't have to
change them at all, though.

You say you have tblGrant Completion. So far we've been talking about
tblGrant Completion-March. Are they the same? Is tblChild Care Providers
related one to many to tblGrant Completion? And do you need data from it in
the report?

Ken Sheridan
Stafford, England

"Chey" wrote:

I just realized that there was a t3
I don't have 3 tables to do this from
Thanks
I have tblGrantCompletion-This one has ZEZA and Vendor number + sum
tblChild Care Providers-This one has vendor number and all there personal info

I got that to work. Now the layout of the report.
I like what you say on placing the vendor number and countofmonths in the
group header, however I don't know what that is. When I did what you say and
place the vendor number and countofmonth under page header it only shows 2 of
the 5 vendor numbers. When I place everything in the detail then it shows
all 5. So I am thinking I don't know what a group header is.
Thanks for helping me.

"Ken Sheridan" wrote:

Yep, you do enter this in SQL view. The first thing then is to confirm that
it returns the correct rows by switching to datasheet view. If you are happy
that it does then the question is what else do you need to show in the
report. Say you want to show vendors' names from a Vendors table which the
Vendor Number field in tblGrant Completion-March references, then you'd add
the vendors table to the query and join it to tblGrant Completion-March on
the Vendor Number fields. I'd normally do this by simply typing in some
extra SQL, but you can also do it by switching to design view then adding the
table and joining it to tblGrant Completion-March visually in the usual way.
You can then drag whatever other fields you need into blank columns the query
design grid. Once you are satisfied that you have all the necessary fields
for the report save the query under any suitable name.

As far as the report is concerned you just enter the query name as its
RecordSource property in the report's properties sheet. As you no longer
need a subreport you can discard that. In the report's sorting and grouping
dialogue you'd group on either the Vendor Number or a vendor Name as the
first group level, providing the names are unique, and give it a group
header. The advantage of grouping on the name is that the report will show
the vendors in alphabetical order.

Fields such as Vendor name relating to the Vendor go in the group header so
you only see them once in the report. The Months go in the detail so that
they are listed individually.

Ken Sheridan
Stafford, England

"Chey" wrote:

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?

Chey:

With the report in design view select Sorting and Grouping from the View
menu or click on the Sorting and Grouping toolbar item. In the left part of
the dialogue you select the fields on which to group the report. In your
case you'd select the vendor number or name (if the names are unique) in the
first row.

In the lower part of the dialogue you can set the properties of the group.
You'll see that amongst other things it enables you to give the group a group
header and/or a group footer. If you select Yes for a group header you'll
find that an extra section will appear on the report in design view. The
same is true for a group footer.

You can place controls bound to fields which you don't want to repeat in
every detailed row in the header or footer, so you might want to put the
vendor name and/or number in the header so that it acts as a heading for each
group of detailed rows. You can also put unbound controls which aggregate
data in a group, e.g. to count rows or sum values. This tends to be done
more often in a group footer rather than header, but there is no reason why
you can't do it in the group header.

You can also do the same in a report header or footer to give you aggregated
values over the whole report, i.e. a grand total which would be the sum of
all the sub-totals in the group footers or headers. One thing you can't do,
however, is aggregate values in a page footer or header by using aggregate
functions such as Count or Sum in the ControlSource of a control. To get
page totals you have to compute the values in code as the report runs.

Ken Sheridan
Stafford, England

"Chey" wrote:

I got that to work. Now the layout of the report.
I like what you say on placing the vendor number and countofmonths in the
group header, however I don't know what that is. When I did what you say and
place the vendor number and countofmonth under page header it only shows 2 of
the 5 vendor numbers. When I place everything in the detail then it shows
all 5. So I am thinking I don't know what a group header is.
Thanks for helping me.

Ok Great I got that to work
Now I have another question I didn't think about. I have the months set up
like Jan 05 all the way to Dec 07. How can I get these to read in order.
So under one vendor number I have it reading in accedning order. But that
is not what I want. Is there a way of displaying it in month order?

"Ken Sheridan" wrote:

Chey:

With the report in design view select Sorting and Grouping from the View
menu or click on the Sorting and Grouping toolbar item. In the left part of
the dialogue you select the fields on which to group the report. In your
case you'd select the vendor number or name (if the names are unique) in the
first row.

In the lower part of the dialogue you can set the properties of the group.
You'll see that amongst other things it enables you to give the group a group
header and/or a group footer. If you select Yes for a group header you'll
find that an extra section will appear on the report in design view. The
same is true for a group footer.

You can place controls bound to fields which you don't want to repeat in
every detailed row in the header or footer, so you might want to put the
vendor name and/or number in the header so that it acts as a heading for each
group of detailed rows. You can also put unbound controls which aggregate
data in a group, e.g. to count rows or sum values. This tends to be done
more often in a group footer rather than header, but there is no reason why
you can't do it in the group header.

You can also do the same in a report header or footer to give you aggregated
values over the whole report, i.e. a grand total which would be the sum of
all the sub-totals in the group footers or headers. One thing you can't do,
however, is aggregate values in a page footer or header by using aggregate
functions such as Count or Sum in the ControlSource of a control. To get
page totals you have to compute the values in code as the report runs.

Ken Sheridan
Stafford, England

"Chey" wrote:

I got that to work. Now the layout of the report.
I like what you say on placing the vendor number and countofmonths in the
group header, however I don't know what that is. When I did what you say and
place the vendor number and countofmonth under page header it only shows 2 of
the 5 vendor numbers. When I place everything in the detail then it shows
all 5. So I am thinking I don't know what a group header is.
Thanks for helping me.

Nevermind on that one, I figured it out.
Another question though. I have something that looks like this
March
April
May

July
August
September
October
November

Now June and December are missing. That is correct.
What my ultimate goal to see is if there is a break like this then make the
vendor number bold.
So what my instruction was is if any 6 month period ZEZA is checked then we
want to know. So although there are more than 6 checked in the above example
because June and December are not there then this is okay. Make vendor
number bold-Just so we know that it was a concern.
My job is to find people who do not take care of assitance kids for more
than a 6 month period. If they fall into this then they are kicked off the
program. However if they have one kid then it starts it back over again.
Can I still intermingle this into my code, or somewhere else?

"Chey" wrote:

Ok Great I got that to work
Now I have another question I didn't think about. I have the months set up
like Jan 05 all the way to Dec 07. How can I get these to read in order.
So under one vendor number I have it reading in accedning order. But that
is not what I want. Is there a way of displaying it in month order?

"Ken Sheridan" wrote:

Chey:

With the report in design view select Sorting and Grouping from the View
menu or click on the Sorting and Grouping toolbar item. In the left part of
the dialogue you select the fields on which to group the report. In your
case you'd select the vendor number or name (if the names are unique) in the
first row.

In the lower part of the dialogue you can set the properties of the group.
You'll see that amongst other things it enables you to give the group a group
header and/or a group footer. If you select Yes for a group header you'll
find that an extra section will appear on the report in design view. The
same is true for a group footer.

You can place controls bound to fields which you don't want to repeat in
every detailed row in the header or footer, so you might want to put the
vendor name and/or number in the header so that it acts as a heading for each
group of detailed rows. You can also put unbound controls which aggregate
data in a group, e.g. to count rows or sum values. This tends to be done
more often in a group footer rather than header, but there is no reason why
you can't do it in the group header.

You can also do the same in a report header or footer to give you aggregated
values over the whole report, i.e. a grand total which would be the sum of
all the sub-totals in the group footers or headers. One thing you can't do,
however, is aggregate values in a page footer or header by using aggregate
functions such as Count or Sum in the ControlSource of a control. To get
page totals you have to compute the values in code as the report runs.

Ken Sheridan
Stafford, England

"Chey" wrote:

I got that to work. Now the layout of the report.
I like what you say on placing the vendor number and countofmonths in the
group header, however I don't know what that is. When I did what you say and
place the vendor number and countofmonth under page header it only shows 2 of
the 5 vendor numbers. When I place everything in the detail then it shows
all 5. So I am thinking I don't know what a group header is.
Thanks for helping me.

Chey:

You can conditionally format a control in the detail section's Format event
procedu

Me.[Vendor Number].FontBold = IsNull(Me.theMonth)

would make the Vendor Number control bold if there was no value in theMonth.

Ken Sheridan
Stafford, England

"Chey" wrote:

Nevermind on that one, I figured it out.
Another question though. I have something that looks like this
March
April
May

July
August
September
October
November

Now June and December are missing. That is correct.
What my ultimate goal to see is if there is a break like this then make the
vendor number bold.
So what my instruction was is if any 6 month period ZEZA is checked then we
want to know. So although there are more than 6 checked in the above example
because June and December are not there then this is okay. Make vendor
number bold-Just so we know that it was a concern.
My job is to find people who do not take care of assitance kids for more
than a 6 month period. If they fall into this then they are kicked off the
program. However if they have one kid then it starts it back over again.
Can I still intermingle this into my code, or somewhere else?

Its not so much if it is blank but if there is a gap between months.
We are working with a 6 month period. If this is not possiable then I can
work with what I have. Is there a way to a gap where a month is missing?
Just so it is easy on the eyes?


"Ken Sheridan" wrote:

Chey:

You can conditionally format a control in the detail section's Format event
procedu

Me.[Vendor Number].FontBold = IsNull(Me.theMonth)

would make the Vendor Number control bold if there was no value in theMonth.

Ken Sheridan
Stafford, England

"Chey" wrote:

Nevermind on that one, I figured it out.
Another question though. I have something that looks like this
March
April
May

July
August
September
October
November

Now June and December are missing. That is correct.
What my ultimate goal to see is if there is a break like this then make the
vendor number bold.
So what my instruction was is if any 6 month period ZEZA is checked then we
want to know. So although there are more than 6 checked in the above example
because June and December are not there then this is okay. Make vendor
number bold-Just so we know that it was a concern.
My job is to find people who do not take care of assitance kids for more
than a 6 month period. If they fall into this then they are kicked off the
program. However if they have one kid then it starts it back over again.
Can I still intermingle this into my code, or somewhere else?

Chey:

One way to suppress blank months would be simply not to return rows with a
NULL theMonth column in the report's query by adding an extra criterion to
the outer query's WHERE clause:

WHERE theMonth IS NOT NULL

To do it in the report there a various ways. The Format event procedure has
a Cancel argument whose return value you can set to True conditionally. This
has the effect of preventing the section from printing for that row in the
report's underlying recordset, so if theMonth is Null in a row you can
prevent the detail section printing for that row with:

Cancel = IsNull(Me.theMonth)

in the detail section's Format event procedure. It does mean that nothing
else in the detail section prints for that row of course, so its an 'all or
nothing' solution. The other way would be to shrink the control, which is
fine if there are no other non-shrinking controls alongside it, but would not
work otherwise.

Another way is to set the MoveLayout property to False conditionally. This
causes the next detail to print in the same position as the current detail,
so if a row is empty the next row can be printed without moving the print
position down a row. Again this would be done in the detail section's Format
event procedure with:

MoveLayout = Not IsNull(Me.theMonth)

Returning to the question of making the Vendor Number bold, what I said in
my last post would not apply here of course as I think you have the vendor
number in the group header. To make the control in the group header bold
you'd have to examine all the rows for the current vendor number in the
underlying query in the

Ken Sheridan
Stafford, England

"Chey" wrote:

Its not so much if it is blank but if there is a gap between months.
We are working with a 6 month period. If this is not possiable then I can
work with what I have. Is there a way to a gap where a month is missing?
Just so it is easy on the eyes?