Counting

[Guest]

I have a DB with 5000 records. There is a field called 1st Choice that I want
to count. There are 30 different choices for for "1st Choice". I need to
count the total number for each of the 30 choices and display those totals in
a report that I am creating.

I am new to Access. In the control source field in the report I tried:
=DCount("[1st Choice]","[Resident Registration]") all this does is total all
the records and repeat that number throughout the report.

Does anybody have any ideas on the best way to accomplish this? A friend has
suggested a query ..... but I am not sure how I would pull the information
for/from the query to the report or even if he is correct.

Steve

 

[Guest]

Steve:

To count rows from the report containing a particular value Sum the return
value of an expression which returns 1 or 0. In a group or report footer of
a report to count the rows in each group or the report's complete recordset
you'd put:

=Sum(IIf([1st Choice] = "Resident Registration",1,0))

Using a domain aggregation function to look directly at the table sum the
constant 1 for each row containing the value:

=DSum("1", "[YourTable]", "[1st Choice] = ""Resident Registration""")

Note that a Null is returned if no rows contain the value. If you want to
show a zero wrap the expression in the Nz function:

=Nz(DSum("1", "[YourTable]", "[1st Choice] = ""Resident Registration"""),0)

BTW you might see it suggested that you use an expression which relies on
that fact that in Access a Boolean TRUE is represented by -1 and FALSE by 0,
e.g.

=Sum(Abs([1st Choice] = "Resident Registration"))

This not recommended as it is not good programming practice to rely on the
implementation, which cannot be guaranteed not to change in the future.

Ken Sheridan
Stafford, England

 

[Guest]

Steve,

I would also recommend a query.
1. Add your table to a new query grid
2. Select your 1stChoice field and drag it down to the first colum of your
grid
3. In the next column to the right select the asterisk (*) value from the
list of fields.
4. Now go to the menubar at the top of the Access window and click the
"Totals" button (This is the Greek Sigma and looks like a capital M rotated
90 degrees to the left. If you get a message that you cannot calculate
totals on a asterisk column, select the primary key field from your table
instead.
5. You should now see a "Total" row in the query grid, with Group By
entered under each of the columns. Change the value from Group By to Count
in the column which contains the asterisk or you PK field.
6. Now run your query. It should give you a list of the values in your
1stChoice field, followed by a count of the number of records that have that
value as the first choice.
7. Once this is running, you can look at the SQL view and change the name
of the PK field to an asterisk, so that the query would look like:

SELECT [1st Choice], Count(*) as Frequency
FROM yourTable
GROUP BY [1st Choice]

By any chance do you have [2nd Choice], [3rd Choice] columns as well? If
so, you might want to reconsider your table design and include a table
(tbl_Resident_Choices) that includes your Resident_ID, Choice#, and
ChoiceType fields. This is better normalized and allows you to take
advantage of the query strengths of a relational database.

HTH
Dale

 

[Guest]

I did the query as you suggested and got all the totals for the 32 different
items. But how would I bring the counts into the report? and have it advance
to each of the 32 items?

The rest of the report looks at a table so I know I will have to have 2 text
boxes the first will point to the 1st item (session) on the query you had me
create. And the 2nd text box for the total of that session.

The information for the report is in the detail area of the report. So I am
at a loss to understand how it would advance to 2nd session and second
session total on the report and so on.

I hope this is making sense.

Steve,

I would also recommend a query.
1. Add your table to a new query grid
2. Select your 1stChoice field and drag it down to the first colum of your
grid
3. In the next column to the right select the asterisk (*) value from the
list of fields.
4. Now go to the menubar at the top of the Access window and click the
"Totals" button (This is the Greek Sigma and looks like a capital M rotated
90 degrees to the left. If you get a message that you cannot calculate
totals on a asterisk column, select the primary key field from your table
instead.
5. You should now see a "Total" row in the query grid, with Group By
entered under each of the columns. Change the value from Group By to Count
in the column which contains the asterisk or you PK field.
6. Now run your query. It should give you a list of the values in your
1stChoice field, followed by a count of the number of records that have that
value as the first choice.
7. Once this is running, you can look at the SQL view and change the name
of the PK field to an asterisk, so that the query would look like:

SELECT [1st Choice], Count(*) as Frequency
FROM yourTable
GROUP BY [1st Choice]

By any chance do you have [2nd Choice], [3rd Choice] columns as well? If
so, you might want to reconsider your table design and include a table
(tbl_Resident_Choices) that includes your Resident_ID, Choice#, and
ChoiceType fields. This is better normalized and allows you to take
advantage of the query strengths of a relational database.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

I have a DB with 5000 records. There is a field called 1st Choice that I want
to count. There are 30 different choices for for "1st Choice". I need to
count the total number for each of the 30 choices and display those totals in
a report that I am creating.

I am new to Access. In the control source field in the report I tried:
=DCount("[1st Choice]","[Resident Registration]") all this does is total all
the records and repeat that number throughout the report.

Does anybody have any ideas on the best way to accomplish this? A friend has
suggested a query ..... but I am not sure how I would pull the information
for/from the query to the report or even if he is correct.

Steve

 

[Dale Fye]

Steve,

Create a new report using the report wizard. Select the query you just
created as your data source for the report, include both fields.

When you look at the report in design view, it should have two text boxes in
the detail section, one for the [1stChoice] field, and the other for your
count.

HTH
Dale

I did the query as you suggested and got all the totals for the 32
different
items. But how would I bring the counts into the report? and have it
advance
to each of the 32 items?

The rest of the report looks at a table so I know I will have to have 2
text
boxes the first will point to the 1st item (session) on the query you had
me
create. And the 2nd text box for the total of that session.

The information for the report is in the detail area of the report. So I
am
at a loss to understand how it would advance to 2nd session and second
session total on the report and so on.

I hope this is making sense.

Steve,

I would also recommend a query.
1. Add your table to a new query grid
2. Select your 1stChoice field and drag it down to the first colum of
your
grid
3. In the next column to the right select the asterisk (*) value from the
list of fields.
4. Now go to the menubar at the top of the Access window and click the
"Totals" button (This is the Greek Sigma and looks like a capital M
rotated
90 degrees to the left. If you get a message that you cannot calculate
totals on a asterisk column, select the primary key field from your table
instead.
5. You should now see a "Total" row in the query grid, with Group By
entered under each of the columns. Change the value from Group By to
Count
in the column which contains the asterisk or you PK field.
6. Now run your query. It should give you a list of the values in your
1stChoice field, followed by a count of the number of records that have
that
value as the first choice.
7. Once this is running, you can look at the SQL view and change the
name
of the PK field to an asterisk, so that the query would look like:

SELECT [1st Choice], Count(*) as Frequency
FROM yourTable
GROUP BY [1st Choice]

By any chance do you have [2nd Choice], [3rd Choice] columns as well? If
so, you might want to reconsider your table design and include a table
(tbl_Resident_Choices) that includes your Resident_ID, Choice#, and
ChoiceType fields. This is better normalized and allows you to take
advantage of the query strengths of a relational database.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

I have a DB with 5000 records. There is a field called 1st Choice that
I want
to count. There are 30 different choices for for "1st Choice". I need
to
count the total number for each of the 30 choices and display those
totals in
a report that I am creating.

I am new to Access. In the control source field in the report I tried:
=DCount("[1st Choice]","[Resident Registration]") all this does is
total all
the records and repeat that number throughout the report.

Does anybody have any ideas on the best way to accomplish this? A
friend has
suggested a query ..... but I am not sure how I would pull the
information
for/from the query to the report or even if he is correct.

Steve