Queries, Reports and the Count function

  • Thread starter Thread starter Christine
  • Start date Start date
C

Christine

I am creating a report based on a query.

In the query, I count the total number of records by Root
Cause:
Field: Root Cause
Table: CARs
Total: Group By

Field: Root Cause
Table: CARs
Total: Group

When the query result is viewed, it shows exactly as it
should:
Root Cause Total of Root Cause
Cause1 1
Cause2 2

I want this Total of Root Cause to be displayed in a
report in the Root Cause Group.

However, there is additional information to be displayed,
both in the query and in the report. As soon as I add the
other fields, the query results look like this:
Root Cause Count of Root Cause
Cause1 1
Cause1 1
Cause2 1

Can someone tell me what am I doing wrong?
Thanks,
Christine
 
It would help if you:
-why and what extra fields you show
-the sql of your final query
 
Duane, the other fields to be included in the output are:

Process, Source, CARNum, CreateDate, CARType, CARStatus,
etc. Here is what Access built in the SQL view of the
query:


SELECT CARs.RootCause, Count(CARs.RootCause) AS
CountOfRootCause, Causes.Cause, Processes.Process,
Sources.Source, CARs.CARNum, CARs.CreateDate,
CARs.CARType, CARs.Status
FROM Sources INNER JOIN (Processes INNER JOIN (Causes
INNER JOIN CARs ON Causes.CauseID = CARs.RootCause) ON
Processes.ProcessID = CARs.ProcessID) ON Sources.SourceID
= CARs.Source
GROUP BY CARs.RootCause, Causes.Cause, Processes.Process,
Sources.Source, CARs.CARNum, CARs.CreateDate,
CARs.CARType, CARs.Status
HAVING (((CARs.Status)<>"Rejected"))
ORDER BY Processes.Process;

The result of all this is going into a report that looks
like this:

CARs BY ROOT CAUSE (report name)

Cause Process CAR Num Source CAR Type
Inadequate Process
Marketing 2004 Audit CR

Total for Cause "Inadequate Process": <Count>

Procedure not followed
Assembly 2005 Audit PR

Total for Cause "Procedure not followed": <Count>

As I hope this illustrates, the report is grouped by
Cause, then Process. What I can't get my query to do is
to give me the <Count> for each the process.

Many thanks in advance,
Christine
 
Consider making a query that is similar to your existing query only group it
to the Process level only and count a field to give you the count of each
process. Then add this new query to your query below and join on the
appropriate fields. This allows you to include the counts from your new
query in your report's record source.
 
Back
Top