Counting & Grouping in a report

[Steve]

The calc below gives the total number of records in the table which is not
what I am after.
Resident Camp Registration has 35 different camp sessions in 1500 records
which when totaled in the report would be the # registered for each camp. I
have tried adding several different criteria to count a total for each of the
different camp sessions without success.

=DCount("[1st Choice Camp Session]","[Resident Camp Registration]")

Does anybody have any ideas?

Thx in advance for the help.

 

[Jerry Whittle]

SELECT [1st Choice Camp Session],
Count([1st Choice Camp Session]) as Counts
FROM [Resident Camp Registration]
GROUP BY [1st Choice Camp Session] ;

Now if you have something like 1st Choice Camp Session, 2nd Choice Camp
Session, 3rd Choice Camp Session, etc., across as field headings and that's
what you need to add up, you have a bad design problem.

 

[Arvin Meyer [MVP]]

A query like:

SELECT CampSession, Count(CampSession) AS NumberRegistered
FROM YourTable
GROUP BY CampSession

will give you what you need, then base a report on the query.

 

[Steve]

This looks as if it is part of a query. I was trying to do it in the report.
Is the query a better approach?

SELECT [1st Choice Camp Session],
Count([1st Choice Camp Session]) as Counts
FROM [Resident Camp Registration]
GROUP BY [1st Choice Camp Session] ;

Now if you have something like 1st Choice Camp Session, 2nd Choice Camp
Session, 3rd Choice Camp Session, etc., across as field headings and that's
what you need to add up, you have a bad design problem.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

The calc below gives the total number of records in the table which is not
what I am after.
Resident Camp Registration has 35 different camp sessions in 1500 records
which when totaled in the report would be the # registered for each camp. I
have tried adding several different criteria to count a total for each of the
different camp sessions without success.

=DCount("[1st Choice Camp Session]","[Resident Camp Registration]")

Does anybody have any ideas?

Thx in advance for the help.

 

[Arvin Meyer [MVP]]

This looks as if it is part of a query. I was trying to do it in the
report.
Is the query a better approach?

A query is much faster than calculating in a report, and there are no timing
issues if you are also running code.

 

[Steve]

I did as you suggested. the query returns 32 lines. How do I all 32 items to
appear on the report? The rest of the report is looking at a table. I'm
thinking a dlookup but I'm not of the syntax.

 

[Steve]

I did as you suggested. The query returns 32 lines. How do I get all 32 items
to
appear on the report? The rest of the report is looking at a table. I'm
thinking a dlookup but I'm not sure of the syntax.

 

[Arvin Meyer [MVP]]

Assuming you have a matching key, use a subreport. Running DLookup will be
even slower than a report calculation because it has to go back to the
source 32 times.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Steve]

Sorry for the late response. I was on vacation.

I am familiar will the Dlookup but not the subreport or the matching key.
Could you please provide more information?