How can I get a total of number of times an entry appears in a tab

[ScotStuart]

Hi
I have a large table that details many operational functions. One field is
called "Consultant Responsible" Who can I write a query that would show me
the consultant's name with the number of times their name appears in table
next to their name? Also I have 2 other fields in same table on called
"category" which shows an alpha-numeric entry and one that has a department
name called "team". Is it possible to produce a report that would detail the
number of times the "category" appears in table against all "Teams" but have
all categories and all teams on the one query / report?
Thanks

 

['69 Camaro]

Hi, Scot.

If I understand your questions correctly then for the first query, try:

SELECT [Consultant Responsible], Count([Consultant Responsible]) AS NumTimes
FROM tblBusTeams
GROUP BY [Consultant Responsible];

.. . . where tblBusTeams is the name of the table. And for the second query,
try:

SELECT BT.Team, BT.Category, Count(BT.Category) AS TotTimesForTeam,
(SELECT COUNT(*)
FROM tblBusTeams AS Tmp
WHERE (BT.Category = Tmp.Category)) AS TotTimesForCateg
FROM tblBusTeams AS BT
GROUP BY BT.Team, BT.Category;

.. . . where tblBusTeams is the name of the table. The first column will
show the name of the team (department), the second column will show the name
of the category that the team participated in, the third column will show the
number of times this team participated in that category, and the fourth
column will show the number of times _any_ team participated in that category.

The fourth column is not accumulative, in that if one team participated in
category A and the total number of times any team participated in category A
was 10 times, then the next team that participated in category A will also
show 10 in the fourth column, because that's showing again the total number
of times this category has been participated in.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[ScotStuart]

Hi Camaro

Thanks for your response. However.... I am afraid I am not sure how to enter
the information e.g SELECT in the query. I have tried typing them as an
expression but Access does not accept . Could you please help this dummy by
giving the way to enter so someone with the brain of a 5 yo could understand
( ME!).

The Table name concerned in both cases is TIDInput.

Thanks
Scot

Hi, Scot.

If I understand your questions correctly then for the first query, try:

SELECT [Consultant Responsible], Count([Consultant Responsible]) AS NumTimes
FROM tblBusTeams
GROUP BY [Consultant Responsible];

. . . where tblBusTeams is the name of the table. And for the second query,
try:

SELECT BT.Team, BT.Category, Count(BT.Category) AS TotTimesForTeam,
(SELECT COUNT(*)
FROM tblBusTeams AS Tmp
WHERE (BT.Category = Tmp.Category)) AS TotTimesForCateg
FROM tblBusTeams AS BT
GROUP BY BT.Team, BT.Category;

. . . where tblBusTeams is the name of the table. The first column will
show the name of the team (department), the second column will show the name
of the category that the team participated in, the third column will show the
number of times this team participated in that category, and the fourth
column will show the number of times _any_ team participated in that category.

The fourth column is not accumulative, in that if one team participated in
category A and the total number of times any team participated in category A
was 10 times, then the next team that participated in category A will also
show 10 in the fourth column, because that's showing again the total number
of times this category has been participated in.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Hi
I have a large table that details many operational functions. One field is
called "Consultant Responsible" Who can I write a query that would show me
the consultant's name with the number of times their name appears in table
next to their name? Also I have 2 other fields in same table on called
"category" which shows an alpha-numeric entry and one that has a department
name called "team". Is it possible to produce a report that would detail the
number of times the "category" appears in table against all "Teams" but have
all categories and all teams on the one query / report?
Thanks

 

['69 Camaro]

Hi, Scot.

Select the Queries tab in the Database Window, which lists all of the
queries in your database. Select the "New" button on the Database Window to
create a new query. Select the "OK" button in the New Query dialog window to
accept the default choice. Select the "Close" button in the Show Table
dialog window.

When you have the query open to the QBE grid, select the View -> SQL View
menu to open the SQL View pane. Copy the following and paste into the white
text area (where you'll overwrite "SELECT;"), then save it:

SELECT [Consultant Responsible], Count([Consultant Responsible]) AS NumTimes
FROM TIDInput
GROUP BY [Consultant Responsible];

.. . . and create another new query, then copy the following and paste into
the white text area for the second, more complex query, then save it:

SELECT TI.Team, TI.Category, Count(TI.Category) AS TotTimesForTeam,
(SELECT COUNT(*)
FROM TIDInput AS Tmp
WHERE (TI.Category = Tmp.Category)) AS TotTimesForCateg
FROM TIDInput AS TI
GROUP BY TI.Team, TI.Category;

To see the resulting data sets, select the View -> Datasheet View menu for
each query. If the table and fields are spelled correctly, then you should
see the results without encountering any hiccups.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Hi Camaro

Thanks for your response. However.... I am afraid I am not sure how to enter
the information e.g SELECT in the query. I have tried typing them as an
expression but Access does not accept . Could you please help this dummy by
giving the way to enter so someone with the brain of a 5 yo could understand
( ME!).

The Table name concerned in both cases is TIDInput.

Thanks
Scot

Hi, Scot.

If I understand your questions correctly then for the first query, try:

SELECT [Consultant Responsible], Count([Consultant Responsible]) AS NumTimes
FROM tblBusTeams
GROUP BY [Consultant Responsible];

. . . where tblBusTeams is the name of the table. And for the second query,
try:

SELECT BT.Team, BT.Category, Count(BT.Category) AS TotTimesForTeam,
(SELECT COUNT(*)
FROM tblBusTeams AS Tmp
WHERE (BT.Category = Tmp.Category)) AS TotTimesForCateg
FROM tblBusTeams AS BT
GROUP BY BT.Team, BT.Category;

. . . where tblBusTeams is the name of the table. The first column will
show the name of the team (department), the second column will show the name
of the category that the team participated in, the third column will show the
number of times this team participated in that category, and the fourth
column will show the number of times _any_ team participated in that category.

The fourth column is not accumulative, in that if one team participated in
category A and the total number of times any team participated in category A
was 10 times, then the next team that participated in category A will also
show 10 in the fourth column, because that's showing again the total number
of times this category has been participated in.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Hi
I have a large table that details many operational functions. One field is
called "Consultant Responsible" Who can I write a query that would show me
the consultant's name with the number of times their name appears in table
next to their name? Also I have 2 other fields in same table on called
"category" which shows an alpha-numeric entry and one that has a department
name called "team". Is it possible to produce a report that would detail the
number of times the "category" appears in table against all "Teams" but have
all categories and all teams on the one query / report?
Thanks

 

[ScotStuart]

Fantastic. Thanks =)

Hi, Scot.

Select the Queries tab in the Database Window, which lists all of the
queries in your database. Select the "New" button on the Database Window to
create a new query. Select the "OK" button in the New Query dialog window to
accept the default choice. Select the "Close" button in the Show Table
dialog window.

When you have the query open to the QBE grid, select the View -> SQL View
menu to open the SQL View pane. Copy the following and paste into the white
text area (where you'll overwrite "SELECT;"), then save it:

SELECT [Consultant Responsible], Count([Consultant Responsible]) AS NumTimes
FROM TIDInput
GROUP BY [Consultant Responsible];

. . . and create another new query, then copy the following and paste into
the white text area for the second, more complex query, then save it:

SELECT TI.Team, TI.Category, Count(TI.Category) AS TotTimesForTeam,
(SELECT COUNT(*)
FROM TIDInput AS Tmp
WHERE (TI.Category = Tmp.Category)) AS TotTimesForCateg
FROM TIDInput AS TI
GROUP BY TI.Team, TI.Category;

To see the resulting data sets, select the View -> Datasheet View menu for
each query. If the table and fields are spelled correctly, then you should
see the results without encountering any hiccups.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Hi Camaro

Thanks for your response. However.... I am afraid I am not sure how to enter
the information e.g SELECT in the query. I have tried typing them as an
expression but Access does not accept . Could you please help this dummy by
giving the way to enter so someone with the brain of a 5 yo could understand
( ME!).

The Table name concerned in both cases is TIDInput.

Thanks
Scot

Hi, Scot.

If I understand your questions correctly then for the first query, try:

SELECT [Consultant Responsible], Count([Consultant Responsible]) AS NumTimes
FROM tblBusTeams
GROUP BY [Consultant Responsible];

. . . where tblBusTeams is the name of the table. And for the second query,
try:

SELECT BT.Team, BT.Category, Count(BT.Category) AS TotTimesForTeam,
(SELECT COUNT(*)
FROM tblBusTeams AS Tmp
WHERE (BT.Category = Tmp.Category)) AS TotTimesForCateg
FROM tblBusTeams AS BT
GROUP BY BT.Team, BT.Category;

. . . where tblBusTeams is the name of the table. The first column will
show the name of the team (department), the second column will show the name
of the category that the team participated in, the third column will show the
number of times this team participated in that category, and the fourth
column will show the number of times _any_ team participated in that category.

The fourth column is not accumulative, in that if one team participated in
category A and the total number of times any team participated in category A
was 10 times, then the next team that participated in category A will also
show 10 in the fourth column, because that's showing again the total number
of times this category has been participated in.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

Hi
I have a large table that details many operational functions. One field is
called "Consultant Responsible" Who can I write a query that would show me
the consultant's name with the number of times their name appears in table
next to their name? Also I have 2 other fields in same table on called
"category" which shows an alpha-numeric entry and one that has a department
name called "team". Is it possible to produce a report that would detail the
number of times the "category" appears in table against all "Teams" but have
all categories and all teams on the one query / report?
Thanks

 

['69 Camaro]

You're very welcome. It appears that you've marked my post above as an
answer to your question. Unfortunately, the Web portal is still rather
buggy and it didn't recognize you as the original poster of the question.
Would you please do me a favor and sign in again to the Microsoft Online
Community with your .Net Passport and try to mark the "Did this post answer
your question?" question on my previous post until a green check mark shows
up? (Refresh the page about a minute later and the green check mark should
appear.)

Thanks! It's greatly appreciated.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

Fantastic. Thanks =)

Hi, Scot.

Select the Queries tab in the Database Window, which lists all of the
queries in your database. Select the "New" button on the Database Window
to
create a new query. Select the "OK" button in the New Query dialog
window to
accept the default choice. Select the "Close" button in the Show Table
dialog window.

When you have the query open to the QBE grid, select the View -> SQL View
menu to open the SQL View pane. Copy the following and paste into the
white
text area (where you'll overwrite "SELECT;"), then save it:

SELECT [Consultant Responsible], Count([Consultant Responsible]) AS
NumTimes
FROM TIDInput
GROUP BY [Consultant Responsible];

. . . and create another new query, then copy the following and paste
into
the white text area for the second, more complex query, then save it:

SELECT TI.Team, TI.Category, Count(TI.Category) AS TotTimesForTeam,
(SELECT COUNT(*)
FROM TIDInput AS Tmp
WHERE (TI.Category = Tmp.Category)) AS TotTimesForCateg
FROM TIDInput AS TI
GROUP BY TI.Team, TI.Category;

To see the resulting data sets, select the View -> Datasheet View menu
for
each query. If the table and fields are spelled correctly, then you
should
see the results without encountering any hiccups.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.

Hi Camaro

Thanks for your response. However.... I am afraid I am not sure how to
enter
the information e.g SELECT in the query. I have tried typing them as an
expression but Access does not accept . Could you please help this
dummy by
giving the way to enter so someone with the brain of a 5 yo could
understand
( ME!).

The Table name concerned in both cases is TIDInput.

Thanks
Scot

:

Hi, Scot.

If I understand your questions correctly then for the first query,
try:

SELECT [Consultant Responsible], Count([Consultant Responsible]) AS
NumTimes
FROM tblBusTeams
GROUP BY [Consultant Responsible];

. . . where tblBusTeams is the name of the table. And for the second
query,
try:

SELECT BT.Team, BT.Category, Count(BT.Category) AS TotTimesForTeam,
(SELECT COUNT(*)
FROM tblBusTeams AS Tmp
WHERE (BT.Category = Tmp.Category)) AS TotTimesForCateg
FROM tblBusTeams AS BT
GROUP BY BT.Team, BT.Category;

. . . where tblBusTeams is the name of the table. The first column
will
show the name of the team (department), the second column will show
the name
of the category that the team participated in, the third column will
show the
number of times this team participated in that category, and the
fourth
column will show the number of times _any_ team participated in that
category.

The fourth column is not accumulative, in that if one team
participated in
category A and the total number of times any team participated in
category A
was 10 times, then the next team that participated in category A will
also
show 10 in the fourth column, because that's showing again the total
number
of times this category has been participated in.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a
message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message,
which
adds your question and the answers to the database of answers.
Remember that
questions answered the quickest are often from those who have a
history of
rewarding the contributors who have taken the time to answer
questions
correctly.


:

Hi
I have a large table that details many operational functions. One
field is
called "Consultant Responsible" Who can I write a query that would
show me
the consultant's name with the number of times their name appears
in table
next to their name? Also I have 2 other fields in same table on
called
"category" which shows an alpha-numeric entry and one that has a
department
name called "team". Is it possible to produce a report that would
detail the
number of times the "category" appears in table against all "Teams"
but have
all categories and all teams on the one query / report?
Thanks

 

[¶£ßË]

Hi Camaro

Thanks for your response. However.... I am afraid I am not sure how to enter
the information e.g SELECT in the query. I have tried typing them as an
expression but Access does not accept . Could you please help this dummy by
giving the way to enter so someone with the brain of a 5 yo could understand
( ME!).

The Table name concerned in both cases is TIDInput.

Thanks
Scot

Hi, Scot.

If I understand your questions correctly then for the first query, try:

SELECT [Consultant Responsible], Count([Consultant Responsible]) AS NumTimes
FROM tblBusTeams
GROUP BY [Consultant Responsible];

. . . where tblBusTeams is the name of the table. And for the second query,
try:

SELECT BT.Team, BT.Category, Count(BT.Category) AS TotTimesForTeam,
(SELECT COUNT(*)
FROM tblBusTeams AS Tmp
WHERE (BT.Category = Tmp.Category)) AS TotTimesForCateg
FROM tblBusTeams AS BT
GROUP BY BT.Team, BT.Category;

. . . where tblBusTeams is the name of the table. The first column will
show the name of the team (department), the second column will show the name
of the category that the team participated in, the third column will show the
number of times this team participated in that category, and the fourth
column will show the number of times _any_ team participated in that category.

The fourth column is not accumulative, in that if one team participated in
category A and the total number of times any team participated in category A
was 10 times, then the next team that participated in category A will also
show 10 in the fourth column, because that's showing again the total number
of times this category has been participated in.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Hi
I have a large table that details many operational functions. One field is
called "Consultant Responsible" Who can I write a query that would show me
the consultant's name with the number of times their name appears in table
next to their name? Also I have 2 other fields in same table on called
"category" which shows an alpha-numeric entry and one that has a department
name called "team". Is it possible to produce a report that would detail the
number of times the "category" appears in table against all "Teams" but have
all categories and all teams on the one query / report?
Thanks