Summing in Reports

[Emma]

Hi, I have a query which produces a negative number in Expr5:[Sex]="Female"
....question 1 how can I make it positive?

question 2... I have a row in the query table called total, (which adds all
Expr5 values together), how can I bring this total number into the report?

question 3.... If I just want to add the Expr5 values in the report how can
I do this, as the Sumation button isn't litup?

 

[Klatuu]

Well, I have found search for females to be a pretty negative experience

But, to answer your question.
It is returnin a negative number because the expression [Sex]="Female"
will return -1 (True) when the field = "Female" and 0 (False when it doesn't

You can make that postive by using the Abs function which removes the sign
bit and returns a positive number, but rather than in the query, you should
do it in the report rather than creating a calculated field in the query. It
will be faster that way. For example, your control source for the text box
would be:

=Abs([Sex] = "Female")

question 2.
You don't. Again, just put the field [sex] in the query.
Then in the group footer or report footer, or wherever you want the total:
=Sum(Abs([Sex] = "Female"))

question 3.

Irrelevant. The answer to question 2 takes care of it.

Just to restate. It is always faster to do your calculations in the report
rather than the query.

 

[Douglas J. Steele]

1. To Access, True is -1 and False is 0. If you need to override that, you
can use Abs([Sex]="Female") or IIf([Sex]="Female", 1, 0)

2. If the data's in a query, use the query as the report's RecordSource. If
all you want is the total (with some other query as the report's
RecordSource), either use a subreport, or use DLookup on the query as the
ControlSource for a text box.

3. There is no summation button in reports, so I don't know what you're
trying to do.

 

[Jeff Boyce]

Emma

You've described "how"...

"What" are you trying to accomplish? Are you trying to get a count of the
number of responses to the field entitled [Sex]? ?The number of responses
that are "Female" to that field?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[pietlinden]

Well, I have found search for females to be a pretty negative experience

LOL...

I could deal with it if it were consistent... but there seems to be
some random number generation going on behind the scenes, so sometimes
it's positive and other times the exact same thing is negative... you
mean people aren't like software?

 

[Emma]

I do have a single female friend however she requires you make over a certain
amount of money for the result to be positive... Seriously thank you guys
you've been a real help!

Emma

You've described "how"...

"What" are you trying to accomplish? Are you trying to get a count of the
number of responses to the field entitled [Sex]? ?The number of responses
that are "Female" to that field?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, I have a query which produces a negative number in
Expr5:[Sex]="Female"
...question 1 how can I make it positive?

question 2... I have a row in the query table called total, (which adds
all
Expr5 values together), how can I bring this total number into the report?

question 3.... If I just want to add the Expr5 values in the report how
can
I do this, as the Sumation button isn't litup?

 

[Emma]

Yes Jeff I'm trying to get a count of the females

Emma

You've described "how"...

"What" are you trying to accomplish? Are you trying to get a count of the
number of responses to the field entitled [Sex]? ?The number of responses
that are "Female" to that field?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, I have a query which produces a negative number in
Expr5:[Sex]="Female"
...question 1 how can I make it positive?

question 2... I have a row in the query table called total, (which adds
all
Expr5 values together), how can I bring this total number into the report?

question 3.... If I just want to add the Expr5 values in the report how
can
I do this, as the Sumation button isn't litup?

 

[Jeff Boyce]

"How" depends on how your data is structured.

Given the example you provided, I'll guess that you have a field named [Sex]
in a table, and that field holds text like "Female".

One way to count those would be to create a query that returns the rowID
(does your table have one?) and [Sex], then convert that query to a Totals
query, using Group By on [Sex] and Count on the rowID field.

By the way, storing "Female" and "Male" (?and "Undecided") in each record
takes up more space than you need to. If you created a lookup table for
Gender, you could be storing "1" (=Female), "2" (=Male), ...

Or, if the only options you want are Male/Female, then you don't even need
the lookup table. You could just use a Yes/No field in your table, and have
the users pick an option on your form.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes Jeff I'm trying to get a count of the females

Emma

You've described "how"...

"What" are you trying to accomplish? Are you trying to get a count of
the
number of responses to the field entitled [Sex]? ?The number of
responses
that are "Female" to that field?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, I have a query which produces a negative number in
Expr5:[Sex]="Female"
...question 1 how can I make it positive?

question 2... I have a row in the query table called total, (which adds
all
Expr5 values together), how can I bring this total number into the
report?

question 3.... If I just want to add the Expr5 values in the report how
can
I do this, as the Sumation button isn't litup?

 

[Emma]

I would like it to gather more parameters then just male, female, other... So
I would like to count the number of males, females, other for each team
member.
I don't have a rowID in the query, how do I do this?

"How" depends on how your data is structured.

Given the example you provided, I'll guess that you have a field named [Sex]
in a table, and that field holds text like "Female".

One way to count those would be to create a query that returns the rowID
(does your table have one?) and [Sex], then convert that query to a Totals
query, using Group By on [Sex] and Count on the rowID field.

By the way, storing "Female" and "Male" (?and "Undecided") in each record
takes up more space than you need to. If you created a lookup table for
Gender, you could be storing "1" (=Female), "2" (=Male), ...

Or, if the only options you want are Male/Female, then you don't even need
the lookup table. You could just use a Yes/No field in your table, and have
the users pick an option on your form.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes Jeff I'm trying to get a count of the females

Emma

You've described "how"...

"What" are you trying to accomplish? Are you trying to get a count of
the
number of responses to the field entitled [Sex]? ?The number of
responses
that are "Female" to that field?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, I have a query which produces a negative number in
Expr5:[Sex]="Female"
...question 1 how can I make it positive?

question 2... I have a row in the query table called total, (which adds
all
Expr5 values together), how can I bring this total number into the
report?

question 3.... If I just want to add the Expr5 values in the report how
can
I do this, as the Sumation button isn't litup?

 

[Jeff Boyce]

Again, "how" depends on your data structure...

We've been discussing the queries, but don't have a clear picture of the
data the query needs to pull from (i.e., the table structure).

What is the structure of the underlying table(s)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like it to gather more parameters then just male, female, other...
So
I would like to count the number of males, females, other for each team
member.
I don't have a rowID in the query, how do I do this?

"How" depends on how your data is structured.

Given the example you provided, I'll guess that you have a field named
[Sex]
in a table, and that field holds text like "Female".

One way to count those would be to create a query that returns the rowID
(does your table have one?) and [Sex], then convert that query to a
Totals
query, using Group By on [Sex] and Count on the rowID field.

By the way, storing "Female" and "Male" (?and "Undecided") in each record
takes up more space than you need to. If you created a lookup table for
Gender, you could be storing "1" (=Female), "2" (=Male), ...

Or, if the only options you want are Male/Female, then you don't even
need
the lookup table. You could just use a Yes/No field in your table, and
have
the users pick an option on your form.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes Jeff I'm trying to get a count of the females

:

Emma

You've described "how"...

"What" are you trying to accomplish? Are you trying to get a count of
the
number of responses to the field entitled [Sex]? ?The number of
responses
that are "Female" to that field?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, I have a query which produces a negative number in
Expr5:[Sex]="Female"
...question 1 how can I make it positive?

question 2... I have a row in the query table called total, (which
adds
all
Expr5 values together), how can I bring this total number into the
report?

question 3.... If I just want to add the Expr5 values in the report
how
can
I do this, as the Sumation button isn't litup?

 

[Emma]

The underlieing table has a unique id field, so I need to bring this in the
query... not sure what to do next? When I put [Sex]="female" then Count it
returns a 1 each time the value is female instead of what I would like is to
create a running list or count. Also, the total row on the bottom of the
datasheet view is 0. I know it's doing what it's suppose to do, I just can't
seem to explain what I'd like it to do.

The Table has alot of data what I'm bringing in is ClientID, First Name Last
Name, Team Membere and Sex... I will also bring in the unique id field

Again, "how" depends on your data structure...

We've been discussing the queries, but don't have a clear picture of the
data the query needs to pull from (i.e., the table structure).

What is the structure of the underlying table(s)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like it to gather more parameters then just male, female, other...
So
I would like to count the number of males, females, other for each team
member.
I don't have a rowID in the query, how do I do this?

"How" depends on how your data is structured.

Given the example you provided, I'll guess that you have a field named
[Sex]
in a table, and that field holds text like "Female".

One way to count those would be to create a query that returns the rowID
(does your table have one?) and [Sex], then convert that query to a
Totals
query, using Group By on [Sex] and Count on the rowID field.

By the way, storing "Female" and "Male" (?and "Undecided") in each record
takes up more space than you need to. If you created a lookup table for
Gender, you could be storing "1" (=Female), "2" (=Male), ...

Or, if the only options you want are Male/Female, then you don't even
need
the lookup table. You could just use a Yes/No field in your table, and
have
the users pick an option on your form.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Yes Jeff I'm trying to get a count of the females

:

Emma

You've described "how"...

"What" are you trying to accomplish? Are you trying to get a count of
the
number of responses to the field entitled [Sex]? ?The number of
responses
that are "Female" to that field?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, I have a query which produces a negative number in
Expr5:[Sex]="Female"
...question 1 how can I make it positive?

question 2... I have a row in the query table called total, (which
adds
all
Expr5 values together), how can I bring this total number into the
report?

question 3.... If I just want to add the Expr5 values in the report
how
can
I do this, as the Sumation button isn't litup?

 

[Jeff Boyce]

A 'running count' is different than a 'count'. To do the running count, you
might want to look into using the 'running sum' property in a report's
controls, rather than trying to do that in a query.

You could create a query that returns the fields you want to see and has
that [Sex]="Female" field added to it, then use the query as the source for
a report in which you use the running sum property I mentioned above.

The folks who help out here in the newsgroup really do need you to explain
what you want to have happen before they can offer specific suggestion for
"how to"...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The underlieing table has a unique id field, so I need to bring this in
the
query... not sure what to do next? When I put [Sex]="female" then Count it
returns a 1 each time the value is female instead of what I would like is
to
create a running list or count. Also, the total row on the bottom of the
datasheet view is 0. I know it's doing what it's suppose to do, I just
can't
seem to explain what I'd like it to do.

The Table has alot of data what I'm bringing in is ClientID, First Name
Last
Name, Team Membere and Sex... I will also bring in the unique id field

Again, "how" depends on your data structure...

We've been discussing the queries, but don't have a clear picture of the
data the query needs to pull from (i.e., the table structure).

What is the structure of the underlying table(s)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like it to gather more parameters then just male, female,
other...
So
I would like to count the number of males, females, other for each team
member.
I don't have a rowID in the query, how do I do this?

:

"How" depends on how your data is structured.

Given the example you provided, I'll guess that you have a field named
[Sex]
in a table, and that field holds text like "Female".

One way to count those would be to create a query that returns the
rowID
(does your table have one?) and [Sex], then convert that query to a
Totals
query, using Group By on [Sex] and Count on the rowID field.

By the way, storing "Female" and "Male" (?and "Undecided") in each
record
takes up more space than you need to. If you created a lookup table
for
Gender, you could be storing "1" (=Female), "2" (=Male), ...

Or, if the only options you want are Male/Female, then you don't even
need
the lookup table. You could just use a Yes/No field in your table,
and
have
the users pick an option on your form.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Yes Jeff I'm trying to get a count of the females

:

Emma

You've described "how"...

"What" are you trying to accomplish? Are you trying to get a count
of
the
number of responses to the field entitled [Sex]? ?The number of
responses
that are "Female" to that field?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, I have a query which produces a negative number in
Expr5:[Sex]="Female"
...question 1 how can I make it positive?

question 2... I have a row in the query table called total,
(which
adds
all
Expr5 values together), how can I bring this total number into
the
report?

question 3.... If I just want to add the Expr5 values in the
report
how
can
I do this, as the Sumation button isn't litup?