Sum If on Report

[Jerry (the latin men)]

I try to create a report where I alredy group my report by reference number.
My querie include three diferents transactions types. At the end of my report
I want three line. Each line to show the sum of every different transaction
type.
This should be the formula in excel: =SUMIF(A1:B9,"TOPS",B1:B9) where b1:b9
is the amount.

Thanks in advace!!

 

[Marshall Barton]

I try to create a report where I alredy group my report by reference number.
My querie include three diferents transactions types. At the end of my report
I want three line. Each line to show the sum of every different transaction
type.
This should be the formula in excel: =SUMIF(A1:B9,"TOPS",B1:B9) where b1:b9
is the amount.

Then what will you have to do when someone invents a fourth
transaction type?

Instead of trying to imitate a spreadsheet, it would be
better to create a Totals type query to calculate all those
values:

SELECT transtype, Sum(amount) as total
FROM thetable
GROUP BY transtype

and then use the query as the record source for a simple
subreport.

 

[Jerry (the latin men)]

Could you explain more about "Totals type query".
I create one, but is not working when I insert it as a subreport.

 

[Jerry (the latin men)]

Could you explain more about "Totals type query".
I create one, but is not working when I insert it as a subreport.

The current query that I have it has a parameter value by date. So I dont
know how to create the Total type query that would match with my current
report.

 

[Marshall Barton]

The current query that I have it has a parameter value by date. So I dont
know how to create the Total type query that would match with my current
report.

Most likely it's complaining about the date field not being
part of an aggregate. If so, select Where in the date
field's Total row.

If that's not the problem, post back with a Copy/Paste of
your query's SQL view and an explanation of what "not
working" means.

 

[Jerry (the latin men)]

When I run my report by a specific date range, I'm able to see the three
total lines on the bottom of my report, but the totals on these lines are
running total of all my data on the querie and not what the data on the
report is showing.

 

[Marshall Barton]

How are you calculating the totals?

What is the query's SQL statement?

How is the total a "running total"?

Note that a text box with an expression like =Sum(somefield)
will total the value of somefield across all of the detail
records. If you want something else please explain the
total are you trying to get.

 

[Jerry (the latin men)]

Marshall, this whats is happening so far. I already fix the issue of the
totals, I modified my subreport. I included a group by transaction type.
Now I have the following issue.
When I run my report, I have to input my begining date and ending date 5
times. So, what I am doing now is that in the form (unbound) I create two
text boxes. where I want to put my Beg-Date and End-Date. I know that I can
"name" these boxes and use that "name" on both of my queries criterias.
Can I do it this way? and if so, how can I name my text boxes and how can a
word it on my queries.?

How are you calculating the totals?

What is the query's SQL statement?

How is the total a "running total"?

Note that a text box with an expression like =Sum(somefield)
will total the value of somefield across all of the detail
records. If you want something else please explain the
total are you trying to get.
--
Marsh
MVP [MS Access]

When I run my report by a specific date range, I'm able to see the three
total lines on the bottom of my report, but the totals on these lines are
running total of all my data on the querie and not what the data on the
report is showing.

 

[Marshall Barton]

Marshall, this whats is happening so far. I already fix the issue of the
totals, I modified my subreport. I included a group by transaction type.
Now I have the following issue.
When I run my report, I have to input my begining date and ending date 5
times. So, what I am doing now is that in the form (unbound) I create two
text boxes. where I want to put my Beg-Date and End-Date. I know that I can
"name" these boxes and use that "name" on both of my queries criterias.
Can I do it this way? and if so, how can I name my text boxes and how can a
word it on my queries.?

That's a good idea.

First, make sure the form is open in design view.

You can name your text boxes any way you like by using the
text box Properties list. The Name property is the first
property on the Property sheet's Other and All tabs. Many
people name their form/report controls with a prefix for the
type of the control followed by a relatively short
descriptive string. I strongly recommend that you do not
use ordinary words (many are reserved) or spaces or any
other non-alphanumeric characters in anything you name. In
your case I would tend toward names like txtBegDate and
txtEndDate.

Once that is taken care of, replace the queries' parameter
prompt strings with this kind of reference:
Forms!nameoftheform.nameofthetextbox

 

[Jerry (the latin men)]

Great, it worked!!! Thanks a lot!!
Now I have another issue. I have another field on my queries named
"trasaction type". On my queries on that field under criteria I have
"[Forms]![Main_Form]![TTYPE]" Is there any way to tell access that whenever I
leave blank my texbox [TTYPE] to show me all the records in this field.

 

[Marshall Barton]

Great, it worked!!! Thanks a lot!!
Now I have another issue. I have another field on my queries named
"trasaction type". On my queries on that field under criteria I have
"[Forms]![Main_Form]![TTYPE]" Is there any way to tell access that whenever I
leave blank my texbox [TTYPE] to show me all the records in this field.

The quick and dirty way to do that is to set the criteria
to:
=[Forms]![Main_Form]![TTYPE] OR
[Forms]![Main_Form]![TTYPE] Is Null

(all on one line)