Counting A Field

[Guest]

Hi all,

This is one of those things that I should know, but just can't remember
right now.

I'm creating a database for an extra-curricular program that will allow it
to track all of its students. One of the fields that they are tracking is
T-Shirt Size. [tshirt] The T-Shirt sizes are a drop-down menu that they are
able to change if they need to. The values are contained in a separate table.

I've got a report which shows all the students by class and also by
division. I'm trying to figure out how to have the report count how many of
each size of shirt is needed. What I don't want to do is say, "Count how
many Child-Small", but rather identify each of the fields in the t-shirt size
table and count them.

I tried to do this while using the report wizard, but it never gave me the
option.

Thanks!

Godspeed,
Ed

 

[Guest]

To count a specific value in the report use something like

=Sum(IIf([TShirtType]="Small",1,0))

So it will sum all the 1'ns when the criteria met.
This formula need to be in the ControlSource of the text box.
The "Small" can be changed to different values

=Sum(IIf([TShirtType]=1,1,0))

Or, you can use
=Sum(Abs([TShirtType]=1))

the False will return 0 and the true will return -1 and the abs will turn it
into positive value

 

[Guest]

Is there anyway that you can do it so that you're not hardcoding each of the
shirt sizes. While the shirt sizes are pretty consistent, I'm concerned that
they might change them in the future (i.e. add a size or delete a size) and I
really don't want to have to go back in and fix the report later.

To count a specific value in the report use something like

=Sum(IIf([TShirtType]="Small",1,0))

So it will sum all the 1'ns when the criteria met.
This formula need to be in the ControlSource of the text box.
The "Small" can be changed to different values

=Sum(IIf([TShirtType]=1,1,0))

Or, you can use
=Sum(Abs([TShirtType]=1))

the False will return 0 and the true will return -1 and the abs will turn it
into positive value


--
Good Luck
BS"D

Hi all,

This is one of those things that I should know, but just can't remember
right now.

I'm creating a database for an extra-curricular program that will allow it
to track all of its students. One of the fields that they are tracking is
T-Shirt Size. [tshirt] The T-Shirt sizes are a drop-down menu that they are
able to change if they need to. The values are contained in a separate table.

I've got a report which shows all the students by class and also by
division. I'm trying to figure out how to have the report count how many of
each size of shirt is needed. What I don't want to do is say, "Count how
many Child-Small", but rather identify each of the fields in the t-shirt size
table and count them.

I tried to do this while using the report wizard, but it never gave me the
option.

Thanks!

Godspeed,
Ed

 

[Guest]

To make it more dynamic, you can create a Group By query that sum the shirts
by type, and then display the list using a SubReport

SELECT TableName.ShirtType, Count(TableName.ShirtType) AS CountOfShirtType
FROM TableName
GROUP BY TableName.ShirtType

--
Good Luck
BS"D

Is there anyway that you can do it so that you're not hardcoding each of the
shirt sizes. While the shirt sizes are pretty consistent, I'm concerned that
they might change them in the future (i.e. add a size or delete a size) and I
really don't want to have to go back in and fix the report later.

To count a specific value in the report use something like

=Sum(IIf([TShirtType]="Small",1,0))

So it will sum all the 1'ns when the criteria met.
This formula need to be in the ControlSource of the text box.
The "Small" can be changed to different values

=Sum(IIf([TShirtType]=1,1,0))

Or, you can use
=Sum(Abs([TShirtType]=1))

the False will return 0 and the true will return -1 and the abs will turn it
into positive value


--
Good Luck
BS"D

Hi all,

This is one of those things that I should know, but just can't remember
right now.

I'm creating a database for an extra-curricular program that will allow it
to track all of its students. One of the fields that they are tracking is
T-Shirt Size. [tshirt] The T-Shirt sizes are a drop-down menu that they are
able to change if they need to. The values are contained in a separate table.

I've got a report which shows all the students by class and also by
division. I'm trying to figure out how to have the report count how many of
each size of shirt is needed. What I don't want to do is say, "Count how
many Child-Small", but rather identify each of the fields in the t-shirt size
table and count them.

I tried to do this while using the report wizard, but it never gave me the
option.

Thanks!

Godspeed,
Ed