Sum

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report which is based on a parameter query. Its a sales report to
sum up sales for sales reps, supervisors and managers. It is grouped on
managers, then supervisors. The parameters are supervisor name or manager
name. When I provide the value for the manager parameter, its gives the
correct totals for individual sales reps, supervisors and finally that
manager. But when I pass on the value of the supervisor parameter, it gives
correct total for supervisor but for the manager, it gives the same figure as
the supervisor whereas it should be much higher as there are several
supervisors under a manager
 
It's tough to diagnose what might not be working the way you want it to
without a look at what you're using. Please post the SQL for the query.

Jeff Boyce
<Access MVP>
 
If you pass a criteria for only a single supervisor and only that supervisor
is in the report's record source, you won't have records to build all of the
manager's records.

You can create a totals query the sums your records by manager. Add this
totals query to your report's record source and join the manager fields.
This would provide a complete total for each manager.
 
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime, [Enter
Manager's Name] Text ( 255 ), [Enter Supervisor's Name] Text ( 255 );
SELECT [Sales Reps].[Sales Rep Id#], [Sales Reps].[Sales Rep], [Sales
Reps].[Supervisor Name], [Sales Reps].[Manager Name],
AllInstalls.ORDER_NO_OHI, Sum(AllInstalls.CHARGE_AMT_OHI) AS Revenue,
AllInstalls.LS_CHG_DTE_OCR AS [Last Change Date]
FROM AllInstalls INNER JOIN [Sales Reps] ON AllInstalls.SALESREP_OHI =
[Sales Reps].[Sales Rep Id#]
WHERE (((AllInstalls.LS_CHG_DTE_OCR) Between [Enter Start Date] And [Enter
End date]))
GROUP BY [Sales Reps].[Sales Rep Id#], [Sales Reps].[Sales Rep], [Sales
Reps].[Supervisor Name], [Sales Reps].[Manager Name],
AllInstalls.ORDER_NO_OHI, AllInstalls.LS_CHG_DTE_OCR
HAVING ((([Sales Reps].[Supervisor Name])=[Enter Supervisor's Name])) OR
((([Sales Reps].[Manager Name])=[Enter Manager's Name]));
 
I attempted this but it didn't work. I created another query which would sum
up revenue only by manager and joined this to my original parameter query
which is my report's record source. It still gives the same results when run
off against a supervisor. I dont even need the manager's total to show up
when I supply only the supervisor criteria. Is there a way that I could
suppress manager's info when I choose to pass on the supervisor critirea only
but the manager info to show up when I pass the manager criteria in the same
report
 
I would stop using parameter prompt queries and begin using references to
controls on forms. This method would allow you to easily hide report
sections based on values in controls on your form.
 
How do I do "references to control on forms"

Duane Hookom said:
I would stop using parameter prompt queries and begin using references to
controls on forms. This method would allow you to easily hide report
sections based on values in controls on your form.
Click to expand...
 
I went into that website, it looks very good and uses a refreshingly friendly
language instead of the textbok language that 'help' uses. Can you specify
the relevant page for my question. Thanks.
 
I went through the article. It explains how to associate parameter queries
with forms but doesn't go into explaining associating passing parameters to
reports with forms. Does that website have such an article? Which other
articles in this website would apply to me? Or can you refer me to another
website
 
Assuming you have a form with a check box control
Forms!frmRptSlct!chkHideDetail.
In your report, you want to hide the detail section if the box is checked.
Use code in the On Format event of the detail section of the report like:
Cancel = (Forms!frmRptSlct!chkHideDetail = True)
 
I dont know VB as such I didn't understand this code from you. I am now
trying to accomplish what I was doing by macros only and I wonder if I can do
that.
This is what i have done so far:
Created a macro1: Open Form called Form1.
Form1 is where I input all my parameters.
On the 'On Click' property of 'OK' button of 'Form1', I have a Macro2 which
is OpenReport. This report's record source is a parameter query. The
parameters of this query are gotten from Form1. For example, one of the
criteria in this query which provides the Supervisor Name is
"[Forms]![Form1]![cboSupervisor].
So to run the report, I run the Macro1 which opens the form Form1. After
inputting all the values in the form, I click the OK button which should open
the report that I want. So far it is not working. Will this approach work?
 
Learn how to add code to an event in Access. For instance find the On Format
event of the report detail section. Click the builder button ([...]) and
select "Code Builder". You can then enter code between the Access provided
lines like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Cancel = (Forms!frmRptSlct!chkHideDetail = True)
End Sub

This code would hide the detail section if the check box was checked.


--
Duane Hookom
MS Access MVP


neeraj said:
I dont know VB as such I didn't understand this code from you. I am now
trying to accomplish what I was doing by macros only and I wonder if I can
do
that.
This is what i have done so far:
Created a macro1: Open Form called Form1.
Form1 is where I input all my parameters.
On the 'On Click' property of 'OK' button of 'Form1', I have a Macro2
which
is OpenReport. This report's record source is a parameter query. The
parameters of this query are gotten from Form1. For example, one of the
criteria in this query which provides the Supervisor Name is
"[Forms]![Form1]![cboSupervisor].
So to run the report, I run the Macro1 which opens the form Form1. After
inputting all the values in the form, I click the OK button which should
open
the report that I want. So far it is not working. Will this approach work?

Duane Hookom said:
Assuming you have a form with a check box control
Forms!frmRptSlct!chkHideDetail.
In your report, you want to hide the detail section if the box is
checked.
Use code in the On Format event of the detail section of the report like:
Cancel = (Forms!frmRptSlct!chkHideDetail = True)
Click to expand...
Click to expand...
 
I still haven't written the code yet. My macro is working now, except a
couple of problems:
1. In the form, I have an end date text box and a start date text box. I
have short date input mask for both. I also have >start date validation rule
for end date text box but yet it allows entries in end date which are prior
to end date?
2. there are 4 boxes in my form, manager name, supervisor name, start date
and end date. When I supply the supervisor alone, I would like the report to
print only supervisor info but it gives manager info also. I know you have
given some code to hide detail but I would like to hide manager info only
when the report has the supervisor parameter passed to it only.



Duane Hookom said:
Learn how to add code to an event in Access. For instance find the On Format
event of the report detail section. Click the builder button ([...]) and
select "Code Builder". You can then enter code between the Access provided
lines like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Cancel = (Forms!frmRptSlct!chkHideDetail = True)
End Sub

This code would hide the detail section if the check box was checked.


--
Duane Hookom
MS Access MVP


neeraj said:
I dont know VB as such I didn't understand this code from you. I am now
trying to accomplish what I was doing by macros only and I wonder if I can
do
that.
This is what i have done so far:
Created a macro1: Open Form called Form1.
Form1 is where I input all my parameters.
On the 'On Click' property of 'OK' button of 'Form1', I have a Macro2
which
is OpenReport. This report's record source is a parameter query. The
parameters of this query are gotten from Form1. For example, one of the
criteria in this query which provides the Supervisor Name is
"[Forms]![Form1]![cboSupervisor].
So to run the report, I run the Macro1 which opens the form Form1. After
inputting all the values in the form, I click the OK button which should
open
the report that I want. So far it is not working. Will this approach work?

Duane Hookom said:
Assuming you have a form with a check box control
Forms!frmRptSlct!chkHideDetail.
In your report, you want to hide the detail section if the box is
checked.
Use code in the On Format event of the detail section of the report like:
Cancel = (Forms!frmRptSlct!chkHideDetail = True)

--
Duane Hookom
MS Access MVP


I went through the article. It explains how to associate parameter
queries
with forms but doesn't go into explaining associating passing
parameters
to
reports with forms. Does that website have such an article? Which other
articles in this website would apply to me? Or can you refer me to
another
website

:

There are a number of them. Look at the one "Customizing parameter
queries"

--
Duane Hookom
MS Access MVP
--

I went into that website, it looks very good and uses a refreshingly
friendly
language instead of the textbok language that 'help' uses. Can you
specify
the relevant page for my question. Thanks.

:

There are some great examples at Martin Green's site
http://www.fontstuff.com/access/index.htm

--
Duane Hookom
MS Access MVP


How do I do "references to control on forms"

:

I would stop using parameter prompt queries and begin using
references
to
controls on forms. This method would allow you to easily hide
report
sections based on values in controls on your form.

--
Duane Hookom
MS Access MVP
--

I attempted this but it didn't work. I created another query
which
would
sum
up revenue only by manager and joined this to my original
parameter
query
which is my report's record source. It still gives the same
results
when
run
off against a supervisor. I dont even need the manager's total
to
show
up
when I supply only the supervisor criteria. Is there a way
that I
could
suppress manager's info when I choose to pass on the
supervisor
critirea
only
but the manager info to show up when I pass the manager
criteria
in
the
same
report

:

If you pass a criteria for only a single supervisor and only
that
supervisor
is in the report's record source, you won't have records to
build
all
of
the
manager's records.

You can create a totals query the sums your records by
manager.
Add
this
totals query to your report's record source and join the
manager
fields.
This would provide a complete total for each manager.

--
Duane Hookom
MS Access MVP


"Jeff Boyce" <[email protected]_HYPHEN_TO_END>
wrote
in
message
It's tough to diagnose what might not be working the way
you
want
it
to
without a look at what you're using. Please post the SQL
for
the
query.

Jeff Boyce
<Access MVP>

message
I have a report which is based on a parameter query. Its a
sales
report
to
sum up sales for sales reps, supervisors and managers. It
is
grouped
on
managers, then supervisors. The parameters are supervisor
name
or
manager
name. When I provide the value for the manager parameter,
its
gives
the
correct totals for individual sales reps, supervisors and
finally
that
manager. But when I pass on the value of the supervisor
parameter,
it
gives
correct total for supervisor but for the manager, it gives
the
same
figure
as
the supervisor whereas it should be much higher as there
are
several
supervisors under a manager
Click to expand...
Click to expand...
Click to expand...
 
Start a new thread in the forms news group for form questions.

You can add code to the On Format event of the report's detail section like
Me.txtManager.Visible = Not IsNull(Forms!frmNoName!txtSupervisor)
You might need to remove the "Not" above depending on your logic. Of course
you will need to change the control and form names.

--
Duane Hookom
MS Access MVP


neeraj said:
I still haven't written the code yet. My macro is working now, except a
couple of problems:
1. In the form, I have an end date text box and a start date text box. I
have short date input mask for both. I also have >start date validation
rule
for end date text box but yet it allows entries in end date which are
prior
to end date?
2. there are 4 boxes in my form, manager name, supervisor name, start date
and end date. When I supply the supervisor alone, I would like the report
to
print only supervisor info but it gives manager info also. I know you have
given some code to hide detail but I would like to hide manager info only
when the report has the supervisor parameter passed to it only.



Duane Hookom said:
Learn how to add code to an event in Access. For instance find the On
Format
event of the report detail section. Click the builder button ([...]) and
select "Code Builder". You can then enter code between the Access
provided
lines like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Cancel = (Forms!frmRptSlct!chkHideDetail = True)
End Sub

This code would hide the detail section if the check box was checked.


--
Duane Hookom
MS Access MVP


neeraj said:
I dont know VB as such I didn't understand this code from you. I am now
trying to accomplish what I was doing by macros only and I wonder if I
can
do
that.
This is what i have done so far:
Created a macro1: Open Form called Form1.
Form1 is where I input all my parameters.
On the 'On Click' property of 'OK' button of 'Form1', I have a Macro2
which
is OpenReport. This report's record source is a parameter query. The
parameters of this query are gotten from Form1. For example, one of the
criteria in this query which provides the Supervisor Name is
"[Forms]![Form1]![cboSupervisor].
So to run the report, I run the Macro1 which opens the form Form1.
After
inputting all the values in the form, I click the OK button which
should
open
the report that I want. So far it is not working. Will this approach
work?

:

Assuming you have a form with a check box control
Forms!frmRptSlct!chkHideDetail.
In your report, you want to hide the detail section if the box is
checked.
Use code in the On Format event of the detail section of the report
like:
Cancel = (Forms!frmRptSlct!chkHideDetail = True)

--
Duane Hookom
MS Access MVP


I went through the article. It explains how to associate parameter
queries
with forms but doesn't go into explaining associating passing
parameters
to
reports with forms. Does that website have such an article? Which
other
articles in this website would apply to me? Or can you refer me to
another
website

:

There are a number of them. Look at the one "Customizing parameter
queries"

--
Duane Hookom
MS Access MVP
--

I went into that website, it looks very good and uses a
refreshingly
friendly
language instead of the textbok language that 'help' uses. Can
you
specify
the relevant page for my question. Thanks.

:

There are some great examples at Martin Green's site
http://www.fontstuff.com/access/index.htm

--
Duane Hookom
MS Access MVP


How do I do "references to control on forms"

:

I would stop using parameter prompt queries and begin using
references
to
controls on forms. This method would allow you to easily hide
report
sections based on values in controls on your form.

--
Duane Hookom
MS Access MVP
--

I attempted this but it didn't work. I created another query
which
would
sum
up revenue only by manager and joined this to my original
parameter
query
which is my report's record source. It still gives the same
results
when
run
off against a supervisor. I dont even need the manager's
total
to
show
up
when I supply only the supervisor criteria. Is there a way
that I
could
suppress manager's info when I choose to pass on the
supervisor
critirea
only
but the manager info to show up when I pass the manager
criteria
in
the
same
report

:

If you pass a criteria for only a single supervisor and
only
that
supervisor
is in the report's record source, you won't have records
to
build
all
of
the
manager's records.

You can create a totals query the sums your records by
manager.
Add
this
totals query to your report's record source and join the
manager
fields.
This would provide a complete total for each manager.

--
Duane Hookom
MS Access MVP


"Jeff Boyce" <[email protected]_HYPHEN_TO_END>
wrote
in
message
It's tough to diagnose what might not be working the way
you
want
it
to
without a look at what you're using. Please post the
SQL
for
the
query.

Jeff Boyce
<Access MVP>

message
I have a report which is based on a parameter query.
Its a
sales
report
to
sum up sales for sales reps, supervisors and managers.
It
is
grouped
on
managers, then supervisors. The parameters are
supervisor
name
or
manager
name. When I provide the value for the manager
parameter,
its
gives
the
correct totals for individual sales reps, supervisors
and
finally
that
manager. But when I pass on the value of the supervisor
parameter,
it
gives
correct total for supervisor but for the manager, it
gives
the
same
figure
as
the supervisor whereas it should be much higher as
there
are
several
supervisors under a manager
Click to expand...
Click to expand...
Click to expand...
 
Back
Top