If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting A Field
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 |
#2
|
|||
|
|||
Counting A Field
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 "Ed S." wrote: 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 |
#3
|
|||
|
|||
Counting A Field
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. "Ofer Cohen" wrote: 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 "Ed S." wrote: 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 |
#4
|
|||
|
|||
Counting A Field
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 "Ed S." wrote: 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. "Ofer Cohen" wrote: 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 "Ed S." wrote: 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 |
Thread Tools | |
Display Modes | |
|
|