Where to Get "Count" Function

G

Guest

Hi I am a VERY new user to Access, and worked thru an Access for Dummies
reference book about two years ago. Now, is my first time to actually use
Access in practice.

I have a database that has course listings; the relevant fields in each
record include:
(Region);
(Group#) the # of times the instructor has taught this course, ranges from
1 to 6; and
(Session End) when the course ended.

For example:
Region Instructor Name Group# Session End
Region A Albert Banning 1
01/25/06
Region B Connie Duncan 4
09/04/05

I have figured out how to use Queries to draw out the records I want, and
how to use Report based on the Query to get a printed listing.

What I need is a summary of the number of courses in each region (like an
Excel "Count" function). I can't figure out how to do this in either Query
or Report.

To see the total number of courses that instructors had taught 1 course in a
region I tried: In Query I tried using "Count" in the Total for the Group#,
but when I ran the query it showed up as CountofGroup# and all the entries
were "1".

Ideally I would like the following table filled in.
# of instructors who ran 1 course # of instructors who
ran 2 courses
Region A
Region B
Region C

I think I need a crosstab for this but couldn't figure it out (I had
inserted the Crosstab in the Query but couldn't set it up to run). So in
hopes of just getting data out and I would fill in the numbers in a Word
table, I tried to just do it with a simple count as above but no success
either.

I have lots of other similar types of summaries and I think if I know how to
do the one above, I could figure out the other ones.

Using Acess 2000 on XP stand alone.

Thx for your patience; I hope someone can offer some suggestions.

Thx,
karen
 
G

Guest

Try using a Totals query. Build a select query, open in design view, click
on the icon that is the Greek letter Epsilon.
In the Totals row and the column you want to count select Count instead of
Group By.
 
G

Guest

Thx for the reply Karl. When I did that I got this result:
To see the total number of courses that instructors had taught 1 course in a
region I tried: In Query I tried using "Count" in the Total for the Group#,
but when I ran the query it showed up as CountofGroup# and all the entries
were "1".
Click to expand...

I want it to count all the incidences of "1", "2", etc. Any idea of what
I'm missing?

cheers,
karen
 
G

Guest

Open the query in design view. Click on menu VIEW - SQL View.
Copy and paste the SQL statement in a post so that it can be analyzed.
 
G

Guest

thx:
SELECT [PLA Practitioners Contact List vJan23].[Session End Date],
Count([PLA Practitioners Contact List vJan23].Region) AS CountOfRegion, [PLA
Practitioners Contact List vJan23].[Group #], [PLA Practitioners Contact List
vJan23].FirstName, [PLA Practitioners Contact List vJan23].LastName, [PLA
Practitioners Contact List vJan23].[Practicum # of Participants]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY [PLA Practitioners Contact List vJan23].[Session End Date], [PLA
Practitioners Contact List vJan23].[Group #], [PLA Practitioners Contact List
vJan23].FirstName, [PLA Practitioners Contact List vJan23].LastName, [PLA
Practitioners Contact List vJan23].[Practicum # of Participants], [PLA
Practitioners Contact List vJan23].[PRAXIS ID]
HAVING ((([PLA Practitioners Contact List vJan23].[Session End
Date])<#1/7/2005#) AND (([PLA Practitioners Contact List vJan23].[PRAXIS
ID])<200))
ORDER BY [PLA Practitioners Contact List vJan23].[Session End Date],
Count([PLA Practitioners Contact List vJan23].Region);
 
G

Guest

You can not total a group and list separate dates, names, attendance, etc at
the same time. Here is the totals.

SELECT [PLA Practitioners Contact List vJan23].[Group #], Count([PLA
Practitioners Contact List vJan23].Region) AS [Total of Region], Sum([PLA
Practitioners Contact List vJan23].[Practicum # of Participants]) AS [Total
of Participants]
FROM [PLA Practitioners Contact List vJan23]
WHERE ((([PLA Practitioners Contact List vJan23].[Session End
Date])<#1/7/2005#) AND (([PLA Practitioners Contact List vJan23].[PRAXIS
ID])<200))
GROUP BY [PLA Practitioners Contact List vJan23].[Group #];


klam said:
thx:
SELECT [PLA Practitioners Contact List vJan23].[Session End Date],
Count([PLA Practitioners Contact List vJan23].Region) AS CountOfRegion, [PLA
Practitioners Contact List vJan23].[Group #], [PLA Practitioners Contact List
vJan23].FirstName, [PLA Practitioners Contact List vJan23].LastName, [PLA
Practitioners Contact List vJan23].[Practicum # of Participants]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY [PLA Practitioners Contact List vJan23].[Session End Date], [PLA
Practitioners Contact List vJan23].[Group #], [PLA Practitioners Contact List
vJan23].FirstName, [PLA Practitioners Contact List vJan23].LastName, [PLA
Practitioners Contact List vJan23].[Practicum # of Participants], [PLA
Practitioners Contact List vJan23].[PRAXIS ID]
HAVING ((([PLA Practitioners Contact List vJan23].[Session End
Date])<#1/7/2005#) AND (([PLA Practitioners Contact List vJan23].[PRAXIS
ID])<200))
ORDER BY [PLA Practitioners Contact List vJan23].[Session End Date],
Count([PLA Practitioners Contact List vJan23].Region);

KARL DEWEY said:
Open the query in design view. Click on menu VIEW - SQL View.
Copy and paste the SQL statement in a post so that it can be analyzed.
Click to expand...
Click to expand...
 
G

Guest

Thx a mint Karl. I popped it in and after the error message, changed the
spacing and it worked (no surprise for you, I'm sure ;)

Of course, knowing that I can only get one summary item at a time is VERY
helpful - I've literally spent about 5 hours trying to figure this out! I
will study it (and I'm going to buy an Acess book this weekend) and hopefully
will be able to get all my summaries out...thx again.

cheers,
karen

KARL DEWEY said:
You can not total a group and list separate dates, names, attendance, etc at
the same time. Here is the totals.

SELECT [PLA Practitioners Contact List vJan23].[Group #], Count([PLA
Practitioners Contact List vJan23].Region) AS [Total of Region], Sum([PLA
Practitioners Contact List vJan23].[Practicum # of Participants]) AS [Total
of Participants]
FROM [PLA Practitioners Contact List vJan23]
WHERE ((([PLA Practitioners Contact List vJan23].[Session End
Date])<#1/7/2005#) AND (([PLA Practitioners Contact List vJan23].[PRAXIS
ID])<200))
GROUP BY [PLA Practitioners Contact List vJan23].[Group #];


klam said:
thx:
SELECT [PLA Practitioners Contact List vJan23].[Session End Date],
Count([PLA Practitioners Contact List vJan23].Region) AS CountOfRegion, [PLA
Practitioners Contact List vJan23].[Group #], [PLA Practitioners Contact List
vJan23].FirstName, [PLA Practitioners Contact List vJan23].LastName, [PLA
Practitioners Contact List vJan23].[Practicum # of Participants]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY [PLA Practitioners Contact List vJan23].[Session End Date], [PLA
Practitioners Contact List vJan23].[Group #], [PLA Practitioners Contact List
vJan23].FirstName, [PLA Practitioners Contact List vJan23].LastName, [PLA
Practitioners Contact List vJan23].[Practicum # of Participants], [PLA
Practitioners Contact List vJan23].[PRAXIS ID]
HAVING ((([PLA Practitioners Contact List vJan23].[Session End
Date])<#1/7/2005#) AND (([PLA Practitioners Contact List vJan23].[PRAXIS
ID])<200))
ORDER BY [PLA Practitioners Contact List vJan23].[Session End Date],
Count([PLA Practitioners Contact List vJan23].Region);

KARL DEWEY said:
Open the query in design view. Click on menu VIEW - SQL View.
Copy and paste the SQL statement in a post so that it can be analyzed.

:

Thx for the reply Karl. When I did that I got this result:

To see the total number of courses that instructors had taught 1 course in a
region I tried: In Query I tried using "Count" in the Total for the Group#,
but when I ran the query it showed up as CountofGroup# and all the entries
were "1".

I want it to count all the incidences of "1", "2", etc. Any idea of what
I'm missing?

cheers,
karen

:

Try using a Totals query. Build a select query, open in design view, click
on the icon that is the Greek letter Epsilon.
In the Totals row and the column you want to count select Count instead of
Group By.
:

Hi I am a VERY new user to Access, and worked thru an Access for Dummies
reference book about two years ago. Now, is my first time to actually use
Access in practice.

I have a database that has course listings; the relevant fields in each
record include:
(Region);
(Group#) the # of times the instructor has taught this course, ranges from
1 to 6; and
(Session End) when the course ended.

For example:
Region Instructor Name Group# Session End
Region A Albert Banning 1
01/25/06
Region B Connie Duncan 4
09/04/05

I have figured out how to use Queries to draw out the records I want, and
how to use Report based on the Query to get a printed listing.

What I need is a summary of the number of courses in each region (like an
Excel "Count" function). I can't figure out how to do this in either Query
or Report.

To see the total number of courses that instructors had taught 1 course in a
region I tried: In Query I tried using "Count" in the Total for the Group#,
but when I ran the query it showed up as CountofGroup# and all the entries
were "1".

Ideally I would like the following table filled in.
# of instructors who ran 1 course # of instructors who
ran 2 courses
Region A
Region B
Region C

I think I need a crosstab for this but couldn't figure it out (I had
inserted the Crosstab in the Query but couldn't set it up to run). So in
hopes of just getting data out and I would fill in the numbers in a Word
table, I tried to just do it with a simple count as above but no success
either.

I have lots of other similar types of summaries and I think if I know how to
do the one above, I could figure out the other ones.

Using Acess 2000 on XP stand alone.

Thx for your patience; I hope someone can offer some suggestions.

Thx,
karen
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Summary break down and grand total 6
"First" Function 2
DLookup or similar function 3
QBE Count across two "drill-down" levels 2
Multiple Unrelated Tables w/Similar Field in Single Query 5
Continued frustration with relationships and queries 1
accessing the value of the link child in a subform 3
Quer related to Count Function 0

Top