Performing a subquery in a textbox control on a report

G

G Bear

I have a sales report based on a query. It is grouped by sales manager at
the top level. I am running a bunch of calculations on the data for each
salesperson.

Two of the items I am trying to calculate over the recordset are:
1) Count of cancelled projects by salesperson
2) Count of unique clients

I have used the std aggregate functions for a lot of the report so far, and
can't get DCOUNT to work, since it requires the main Query as a parameter -
so same result for each grouping.

have tried to write SQL subquery statements. I built my statements in the
query builder and they return the expected results. However when I put them
into the control source of the text box on the report they return #NAME?.

Will putting subqueries into a text control even work, or am I barking up
the wrong tree? Will it maintain the grouping integrity?

My subqueries look like this in the control source:
=(SELECT COUNT(*) as 'Cancels' FROM qryPSM_YTD WHERE (Cycle = 'Cancelled'))

=(SELECT DiSTINCT COUNT(ClientID) as 'uniqueclients' FROM qryPSM_YTD)
~~~from memory, not 100% this one is right....

Getting this to work. opens up a world of other options for my report - so
I'd like to figure it out using subqueries on the main recordset.

Thanks
-Greg
 
D

Duane Hookom

You can't use SQL statements as control sources. You might be able to use
standard Sum() or Count() or domain aggregate functions or code or
subreports but not SQL in control sources.
 
G

G Bear

Thanks - I got the impression that it could because when I put the query into
the control source, I got an error message saying I needed to include
parenthesis around the subquery. And the full report doesn't error out when
I run it.

The database is in SQL, using Access as the report writer / inpot forms if
that makes any difference.

Thanks
-Greg
 
D

Duane Hookom

I'm sticking by my first reply.
You can use subqueries in queries but not as control sources.
 
G

G Bear

Thanks for the response :) - lets say I take the code route, since it would
be potentially the most flexible. Will the steps be:
1) I set a textcontrol named 'txtCancelled' in my report.
2) I need a code snippet to now write to that variable that:
a) maintains the group integrity in the report
b) runs the subquery against the main report query
c) needs some sort of trigger event like report print

Could you point me to any samples of something like this?

-----------------
 
D

Duane Hookom

That's not the method that I would use, however, you would use the On Format
event of the report section containing the text box.
Dim db as DAO.Database
Dim rs As DAO.Recordset
SET db =CurrentDb
Set rs = db.OpenRecordSet("SELECT COUNT(*) as Cancels " & _
"FROM qryPSM_YTD WHERE Cycle = 'Cancelled'")
If not rs.EOF Then
Me.txtCancelled = rs!Cancels
End If
rs.Close
Set rs nothing
Set db = Nothing

The entire above code could be replaced with a control source in the text
box of:
=DCount("*","qryPSM_YTD","Cycle='Cancelled'")

If your qryPSM_YTD is the same as the Record Source of your report, then you
might get away with the much more efficient solution of setting the Control
Source to:
=Sum(Abs(Cycle="Cancelled"))

I expect there is some grouping value that needs to included but I don't see
anything in your replies that provides any information about this.
 
G

G Bear

Thanks a lot, Duane. Great Reply. Yes, getting aggregates that maintain the
report grouping integrity in this was the ultimate goal/obstacle.

I started out trying dcount, but since it wasn't maintaining the group the
way other aggregate functions do (because of the full table/qry as parameter)
it just shows the same value in each grouping. In my case, setting the
control of a text box in my report to
=DCount("*","qryPSM_YTD","Cycle='Cancelled'") just returns 97 in each
grouping of Sales Managers. (Of which, I am one, so forgive the lack of
experience - just bucking for a raise here). Dcount definitely works if
there is another way to crack the grouping issue.

The underlying query for the report is the same, so I tried what you
suggested setting the control source to =Sum(Abs(Cycle="Cancelled")) --
this just returned an error for me.

So went back to the code route -- I assume this will have the same grouping
issue as dcount, but was hoping I could suck in a group parameter from the
report, and add it to the where clause. (I think I need to do the same thing
to maintain a graph in this same grouping - seperate question perhaps)

The snippet below didn't work for me as DAO - so spent some time digging and
rewrote this in ADO. Its not working for me currently at runtime, tossing an
error at or near the "Select" statement. But seems close:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

Dim rs As ADODB.Recordset
Dim strSQL As String

Set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection

strSQL = "SELECT COUNT(*) AS Cancels FROM qryPSM_YTD WHERE (Cycle =
'Cancelled')"

rs.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable

'If Not rs.EOF Then
Me.Text83 = rs!Cancels
'End If

rs.Close

End Sub
--------------------------------------------------------------------
 
D

Duane Hookom

I am not even going to help send you down the code path when it is not
necessary.
You should be able to use either of the other two methods. If you want to
limit your DCount() you might be able to use something like:
=DCount("*","qryPSM_YTD","Cycle='Cancelled' And [SalesManager]=""" &
[SalesManager] & """")
You haven't provided enough information that I am totally confident with the
above expression.

Also the
=Sum(Abs(Cycle="Cancelled"))
should work as long as:
- you don't use it in a Page header or footer
- your text box name is not the name of a field
- you have a text field in your report record source named Cycle
 
G

G Bear

Oh yeah! that will work, thanks a lot Duane.

OK... last question then, how do I get at the value that is in GroupHeader0
and pass it in to DCount?

I tried the Sum(abs()) again, but get this error:
"Expressiong is invalid. Aggregate functions are only allowed on output
fields of the Record Source."
It is in the Group header/footer, and other parameters below are met.

Thanks.
_Greg

Duane Hookom said:
I am not even going to help send you down the code path when it is not
necessary.
You should be able to use either of the other two methods. If you want to
limit your DCount() you might be able to use something like:
=DCount("*","qryPSM_YTD","Cycle='Cancelled' And [SalesManager]=""" &
[SalesManager] & """")
You haven't provided enough information that I am totally confident with the
above expression.

Also the
=Sum(Abs(Cycle="Cancelled"))
should work as long as:
- you don't use it in a Page header or footer
- your text box name is not the name of a field
- you have a text field in your report record source named Cycle

--
Duane Hookom
MS Access MVP
--

G Bear said:
Thanks a lot, Duane. Great Reply. Yes, getting aggregates that maintain
the
report grouping integrity in this was the ultimate goal/obstacle.

I started out trying dcount, but since it wasn't maintaining the group the
way other aggregate functions do (because of the full table/qry as
parameter)
it just shows the same value in each grouping. In my case, setting the
control of a text box in my report to
=DCount("*","qryPSM_YTD","Cycle='Cancelled'") just returns 97 in each
grouping of Sales Managers. (Of which, I am one, so forgive the lack of
experience - just bucking for a raise here). Dcount definitely works if
there is another way to crack the grouping issue.

The underlying query for the report is the same, so I tried what you
suggested setting the control source to =Sum(Abs(Cycle="Cancelled")) --
this just returned an error for me.

So went back to the code route -- I assume this will have the same
grouping
issue as dcount, but was hoping I could suck in a group parameter from the
report, and add it to the where clause. (I think I need to do the same
thing
to maintain a graph in this same grouping - seperate question perhaps)

The snippet below didn't work for me as DAO - so spent some time digging
and
rewrote this in ADO. Its not working for me currently at runtime, tossing
an
error at or near the "Select" statement. But seems close:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

Dim rs As ADODB.Recordset
Dim strSQL As String

Set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection

strSQL = "SELECT COUNT(*) AS Cancels FROM qryPSM_YTD WHERE (Cycle =
'Cancelled')"

rs.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable

'If Not rs.EOF Then
Me.Text83 = rs!Cancels
'End If

rs.Close

End Sub
 
D

Duane Hookom

So, your report's record source has a field list that includes the field
[Cycle]?

A GroupHeader0 has no value. Values come from fields in the table or
controls on the report. What exactly do you want to pass to DCount()?

--
Duane Hookom
MS Access MVP
--

G Bear said:
Oh yeah! that will work, thanks a lot Duane.

OK... last question then, how do I get at the value that is in
GroupHeader0
and pass it in to DCount?

I tried the Sum(abs()) again, but get this error:
"Expressiong is invalid. Aggregate functions are only allowed on output
fields of the Record Source."
It is in the Group header/footer, and other parameters below are met.

Thanks.
_Greg

Duane Hookom said:
I am not even going to help send you down the code path when it is not
necessary.
You should be able to use either of the other two methods. If you want to
limit your DCount() you might be able to use something like:
=DCount("*","qryPSM_YTD","Cycle='Cancelled' And [SalesManager]=""" &
[SalesManager] & """")
You haven't provided enough information that I am totally confident with
the
above expression.

Also the
=Sum(Abs(Cycle="Cancelled"))
should work as long as:
- you don't use it in a Page header or footer
- your text box name is not the name of a field
- you have a text field in your report record source named Cycle

--
Duane Hookom
MS Access MVP
--

G Bear said:
Thanks a lot, Duane. Great Reply. Yes, getting aggregates that
maintain
the
report grouping integrity in this was the ultimate goal/obstacle.

I started out trying dcount, but since it wasn't maintaining the group
the
way other aggregate functions do (because of the full table/qry as
parameter)
it just shows the same value in each grouping. In my case, setting the
control of a text box in my report to
=DCount("*","qryPSM_YTD","Cycle='Cancelled'") just returns 97 in each
grouping of Sales Managers. (Of which, I am one, so forgive the lack
of
experience - just bucking for a raise here). Dcount definitely works
if
there is another way to crack the grouping issue.

The underlying query for the report is the same, so I tried what you
suggested setting the control source to
Sum(Abs(Cycle="Cancelled")) --
this just returned an error for me.

So went back to the code route -- I assume this will have the same
grouping
issue as dcount, but was hoping I could suck in a group parameter from
the
report, and add it to the where clause. (I think I need to do the same
thing
to maintain a graph in this same grouping - seperate question perhaps)

The snippet below didn't work for me as DAO - so spent some time
digging
and
rewrote this in ADO. Its not working for me currently at runtime,
tossing
an
error at or near the "Select" statement. But seems close:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As
Integer)

Dim rs As ADODB.Recordset
Dim strSQL As String

Set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection

strSQL = "SELECT COUNT(*) AS Cancels FROM qryPSM_YTD WHERE (Cycle =
'Cancelled')"

rs.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable

'If Not rs.EOF Then
Me.Text83 = rs!Cancels
'End If

rs.Close

End Sub
--------------------------------------------------------------------
:

That's not the method that I would use, however, you would use the On
Format
event of the report section containing the text box.
Dim db as DAO.Database
Dim rs As DAO.Recordset
SET db =CurrentDb
Set rs = db.OpenRecordSet("SELECT COUNT(*) as Cancels " & _
"FROM qryPSM_YTD WHERE Cycle = 'Cancelled'")
If not rs.EOF Then
Me.txtCancelled = rs!Cancels
End If
rs.Close
Set rs nothing
Set db = Nothing

The entire above code could be replaced with a control source in the
text
box of:
=DCount("*","qryPSM_YTD","Cycle='Cancelled'")

If your qryPSM_YTD is the same as the Record Source of your report,
then
you
might get away with the much more efficient solution of setting the
Control
Source to:
=Sum(Abs(Cycle="Cancelled"))

I expect there is some grouping value that needs to included but I
don't
see
anything in your replies that provides any information about this.

--
Duane Hookom
MS Access MVP


Thanks for the response :) - lets say I take the code route, since
it
would
be potentially the most flexible. Will the steps be:
1) I set a textcontrol named 'txtCancelled' in my report.
2) I need a code snippet to now write to that variable that:
a) maintains the group integrity in the report
b) runs the subquery against the main report query
c) needs some sort of trigger event like report print

Could you point me to any samples of something like this?

-----------------
:

I'm sticking by my first reply.
You can use subqueries in queries but not as control sources.

--
Duane Hookom
MS Access MVP
--

Thanks - I got the impression that it could because when I put
the
query
into
the control source, I got an error message saying I needed to
include
parenthesis around the subquery. And the full report doesn't
error
out
when
I run it.

The database is in SQL, using Access as the report writer / inpot
forms
if
that makes any difference.

Thanks
-Greg

:

You can't use SQL statements as control sources. You might be
able
to
use
standard Sum() or Count() or domain aggregate functions or code
or
subreports but not SQL in control sources.

--
Duane Hookom
MS Access MVP


I have a sales report based on a query. It is grouped by sales
manager
at
the top level. I am running a bunch of calculations on the
data
for
each
salesperson.

Two of the items I am trying to calculate over the recordset
are:
1) Count of cancelled projects by salesperson
2) Count of unique clients

I have used the std aggregate functions for a lot of the
report
so
far,
and
can't get DCOUNT to work, since it requires the main Query as
a
parameter -
so same result for each grouping.

have tried to write SQL subquery statements. I built my
statements
in
the
query builder and they return the expected results. However
when
I
put
them
into the control source of the text box on the report they
return
#NAME?.

Will putting subqueries into a text control even work, or am I
barking
up
the wrong tree? Will it maintain the grouping integrity?

My subqueries look like this in the control source:
=(SELECT COUNT(*) as 'Cancels' FROM qryPSM_YTD WHERE (Cycle =
'Cancelled'))

=(SELECT DiSTINCT COUNT(ClientID) as 'uniqueclients' FROM
qryPSM_YTD)
~~~from memory, not 100% this one is right....

Getting this to work. opens up a world of other options for my
report -
so
I'd like to figure it out using subqueries on the main
recordset.

Thanks
-Greg
 
G

G Bear

Thanks, good to know I can't get avalue from there. There is a Cycle field
in the record source. Also a field called Name in the record source that is
used in the report group.

I have a text control in the report called PSMname that is grouped. Would I
reference that in dcount as "Cycle = 'Cancelled' AND Name = Me!PSMname.value"

Thanks,
-Greg


Duane Hookom said:
So, your report's record source has a field list that includes the field
[Cycle]?

A GroupHeader0 has no value. Values come from fields in the table or
controls on the report. What exactly do you want to pass to DCount()?

--
Duane Hookom
MS Access MVP
--

G Bear said:
Oh yeah! that will work, thanks a lot Duane.

OK... last question then, how do I get at the value that is in
GroupHeader0
and pass it in to DCount?

I tried the Sum(abs()) again, but get this error:
"Expressiong is invalid. Aggregate functions are only allowed on output
fields of the Record Source."
It is in the Group header/footer, and other parameters below are met.

Thanks.
_Greg

Duane Hookom said:
I am not even going to help send you down the code path when it is not
necessary.
You should be able to use either of the other two methods. If you want to
limit your DCount() you might be able to use something like:
=DCount("*","qryPSM_YTD","Cycle='Cancelled' And [SalesManager]=""" &
[SalesManager] & """")
You haven't provided enough information that I am totally confident with
the
above expression.

Also the
=Sum(Abs(Cycle="Cancelled"))
should work as long as:
- you don't use it in a Page header or footer
- your text box name is not the name of a field
- you have a text field in your report record source named Cycle

--
Duane Hookom
MS Access MVP
--

Thanks a lot, Duane. Great Reply. Yes, getting aggregates that
maintain
the
report grouping integrity in this was the ultimate goal/obstacle.

I started out trying dcount, but since it wasn't maintaining the group
the
way other aggregate functions do (because of the full table/qry as
parameter)
it just shows the same value in each grouping. In my case, setting the
control of a text box in my report to
=DCount("*","qryPSM_YTD","Cycle='Cancelled'") just returns 97 in each
grouping of Sales Managers. (Of which, I am one, so forgive the lack
of
experience - just bucking for a raise here). Dcount definitely works
if
there is another way to crack the grouping issue.

The underlying query for the report is the same, so I tried what you
suggested setting the control source to
Sum(Abs(Cycle="Cancelled")) --
this just returned an error for me.

So went back to the code route -- I assume this will have the same
grouping
issue as dcount, but was hoping I could suck in a group parameter from
the
report, and add it to the where clause. (I think I need to do the same
thing
to maintain a graph in this same grouping - seperate question perhaps)

The snippet below didn't work for me as DAO - so spent some time
digging
and
rewrote this in ADO. Its not working for me currently at runtime,
tossing
an
error at or near the "Select" statement. But seems close:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As
Integer)

Dim rs As ADODB.Recordset
Dim strSQL As String

Set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection

strSQL = "SELECT COUNT(*) AS Cancels FROM qryPSM_YTD WHERE (Cycle =
'Cancelled')"

rs.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable

'If Not rs.EOF Then
Me.Text83 = rs!Cancels
'End If

rs.Close

End Sub
--------------------------------------------------------------------
:

That's not the method that I would use, however, you would use the On
Format
event of the report section containing the text box.
Dim db as DAO.Database
Dim rs As DAO.Recordset
SET db =CurrentDb
Set rs = db.OpenRecordSet("SELECT COUNT(*) as Cancels " & _
"FROM qryPSM_YTD WHERE Cycle = 'Cancelled'")
If not rs.EOF Then
Me.txtCancelled = rs!Cancels
End If
rs.Close
Set rs nothing
Set db = Nothing

The entire above code could be replaced with a control source in the
text
box of:
=DCount("*","qryPSM_YTD","Cycle='Cancelled'")

If your qryPSM_YTD is the same as the Record Source of your report,
then
you
might get away with the much more efficient solution of setting the
Control
Source to:
=Sum(Abs(Cycle="Cancelled"))

I expect there is some grouping value that needs to included but I
don't
see
anything in your replies that provides any information about this.

--
Duane Hookom
MS Access MVP


Thanks for the response :) - lets say I take the code route, since
it
would
be potentially the most flexible. Will the steps be:
1) I set a textcontrol named 'txtCancelled' in my report.
2) I need a code snippet to now write to that variable that:
a) maintains the group integrity in the report
b) runs the subquery against the main report query
c) needs some sort of trigger event like report print

Could you point me to any samples of something like this?

-----------------
:

I'm sticking by my first reply.
You can use subqueries in queries but not as control sources.

--
Duane Hookom
MS Access MVP
--

Thanks - I got the impression that it could because when I put
the
query
into
the control source, I got an error message saying I needed to
include
parenthesis around the subquery. And the full report doesn't
error
out
when
I run it.

The database is in SQL, using Access as the report writer / inpot
forms
if
that makes any difference.

Thanks
-Greg

:

You can't use SQL statements as control sources. You might be
able
to
use
standard Sum() or Count() or domain aggregate functions or code
or
subreports but not SQL in control sources.

--
Duane Hookom
MS Access MVP


I have a sales report based on a query. It is grouped by sales
manager
at
the top level. I am running a bunch of calculations on the
data
for
each
salesperson.

Two of the items I am trying to calculate over the recordset
are:
1) Count of cancelled projects by salesperson
2) Count of unique clients

I have used the std aggregate functions for a lot of the
report
so
far,
and
can't get DCOUNT to work, since it requires the main Query as
a
parameter -
so same result for each grouping.

have tried to write SQL subquery statements. I built my
statements
in
the
query builder and they return the expected results. However
when
I
put
them
into the control source of the text box on the report they
return
#NAME?.

Will putting subqueries into a text control even work, or am I
barking
up
the wrong tree? Will it maintain the grouping integrity?

My subqueries look like this in the control source:
=(SELECT COUNT(*) as 'Cancels' FROM qryPSM_YTD WHERE (Cycle =
'Cancelled'))

=(SELECT DiSTINCT COUNT(ClientID) as 'uniqueclients' FROM
qryPSM_YTD)
~~~from memory, not 100% this one is right....

Getting this to work. opens up a world of other options for my
report -
so
I'd like to figure it out using subqueries on the main
recordset.

Thanks
-Greg
 
D

Duane Hookom

Having a field named name might be part or all of your issue. Every object
has a name property so entering a name of name to save an object may cause
significant issues.

--
Duane Hookom
MS Access MVP


G Bear said:
Thanks, good to know I can't get avalue from there. There is a Cycle
field
in the record source. Also a field called Name in the record source that
is
used in the report group.

I have a text control in the report called PSMname that is grouped. Would
I
reference that in dcount as "Cycle = 'Cancelled' AND Name =
Me!PSMname.value"

Thanks,
-Greg


Duane Hookom said:
So, your report's record source has a field list that includes the field
[Cycle]?

A GroupHeader0 has no value. Values come from fields in the table or
controls on the report. What exactly do you want to pass to DCount()?

--
Duane Hookom
MS Access MVP
--

G Bear said:
Oh yeah! that will work, thanks a lot Duane.

OK... last question then, how do I get at the value that is in
GroupHeader0
and pass it in to DCount?

I tried the Sum(abs()) again, but get this error:
"Expressiong is invalid. Aggregate functions are only allowed on
output
fields of the Record Source."
It is in the Group header/footer, and other parameters below are met.

Thanks.
_Greg

:

I am not even going to help send you down the code path when it is not
necessary.
You should be able to use either of the other two methods. If you want
to
limit your DCount() you might be able to use something like:
=DCount("*","qryPSM_YTD","Cycle='Cancelled' And [SalesManager]=""" &
[SalesManager] & """")
You haven't provided enough information that I am totally confident
with
the
above expression.

Also the
=Sum(Abs(Cycle="Cancelled"))
should work as long as:
- you don't use it in a Page header or footer
- your text box name is not the name of a field
- you have a text field in your report record source named Cycle

--
Duane Hookom
MS Access MVP
--

Thanks a lot, Duane. Great Reply. Yes, getting aggregates that
maintain
the
report grouping integrity in this was the ultimate goal/obstacle.

I started out trying dcount, but since it wasn't maintaining the
group
the
way other aggregate functions do (because of the full table/qry as
parameter)
it just shows the same value in each grouping. In my case, setting
the
control of a text box in my report to
=DCount("*","qryPSM_YTD","Cycle='Cancelled'") just returns 97 in
each
grouping of Sales Managers. (Of which, I am one, so forgive the
lack
of
experience - just bucking for a raise here). Dcount definitely
works
if
there is another way to crack the grouping issue.

The underlying query for the report is the same, so I tried what you
suggested setting the control source to
Sum(Abs(Cycle="Cancelled")) --
this just returned an error for me.

So went back to the code route -- I assume this will have the same
grouping
issue as dcount, but was hoping I could suck in a group parameter
from
the
report, and add it to the where clause. (I think I need to do the
same
thing
to maintain a graph in this same grouping - seperate question
perhaps)

The snippet below didn't work for me as DAO - so spent some time
digging
and
rewrote this in ADO. Its not working for me currently at runtime,
tossing
an
error at or near the "Select" statement. But seems close:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As
Integer)

Dim rs As ADODB.Recordset
Dim strSQL As String

Set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection

strSQL = "SELECT COUNT(*) AS Cancels FROM qryPSM_YTD WHERE (Cycle
=
'Cancelled')"

rs.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable

'If Not rs.EOF Then
Me.Text83 = rs!Cancels
'End If

rs.Close

End Sub
--------------------------------------------------------------------
:

That's not the method that I would use, however, you would use the
On
Format
event of the report section containing the text box.
Dim db as DAO.Database
Dim rs As DAO.Recordset
SET db =CurrentDb
Set rs = db.OpenRecordSet("SELECT COUNT(*) as Cancels " & _
"FROM qryPSM_YTD WHERE Cycle = 'Cancelled'")
If not rs.EOF Then
Me.txtCancelled = rs!Cancels
End If
rs.Close
Set rs nothing
Set db = Nothing

The entire above code could be replaced with a control source in
the
text
box of:
=DCount("*","qryPSM_YTD","Cycle='Cancelled'")

If your qryPSM_YTD is the same as the Record Source of your report,
then
you
might get away with the much more efficient solution of setting the
Control
Source to:
=Sum(Abs(Cycle="Cancelled"))

I expect there is some grouping value that needs to included but I
don't
see
anything in your replies that provides any information about this.

--
Duane Hookom
MS Access MVP


Thanks for the response :) - lets say I take the code route,
since
it
would
be potentially the most flexible. Will the steps be:
1) I set a textcontrol named 'txtCancelled' in my report.
2) I need a code snippet to now write to that variable that:
a) maintains the group integrity in the report
b) runs the subquery against the main report query
c) needs some sort of trigger event like report print

Could you point me to any samples of something like this?

-----------------
:

I'm sticking by my first reply.
You can use subqueries in queries but not as control sources.

--
Duane Hookom
MS Access MVP
--

Thanks - I got the impression that it could because when I put
the
query
into
the control source, I got an error message saying I needed to
include
parenthesis around the subquery. And the full report doesn't
error
out
when
I run it.

The database is in SQL, using Access as the report writer /
inpot
forms
if
that makes any difference.

Thanks
-Greg

:

You can't use SQL statements as control sources. You might be
able
to
use
standard Sum() or Count() or domain aggregate functions or
code
or
subreports but not SQL in control sources.

--
Duane Hookom
MS Access MVP


I have a sales report based on a query. It is grouped by
sales
manager
at
the top level. I am running a bunch of calculations on the
data
for
each
salesperson.

Two of the items I am trying to calculate over the
recordset
are:
1) Count of cancelled projects by salesperson
2) Count of unique clients

I have used the std aggregate functions for a lot of the
report
so
far,
and
can't get DCOUNT to work, since it requires the main Query
as
a
parameter -
so same result for each grouping.

have tried to write SQL subquery statements. I built my
statements
in
the
query builder and they return the expected results.
However
when
I
put
them
into the control source of the text box on the report they
return
#NAME?.

Will putting subqueries into a text control even work, or
am I
barking
up
the wrong tree? Will it maintain the grouping integrity?

My subqueries look like this in the control source:
=(SELECT COUNT(*) as 'Cancels' FROM qryPSM_YTD WHERE (Cycle
=
'Cancelled'))

=(SELECT DiSTINCT COUNT(ClientID) as 'uniqueclients' FROM
qryPSM_YTD)
~~~from memory, not 100% this one is right....

Getting this to work. opens up a world of other options for
my
report -
so
I'd like to figure it out using subqueries on the main
recordset.

Thanks
-Greg
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top