Report only where have multiple entries for a field

  • Thread starter Thread starter John McCormack
  • Start date Start date
J

John McCormack

I have a service call database, and have created a report which shows the
calls for each site.
I want to report only on those sites which had multiple calls as I am trying
to review the number of repeat calls to solve the initial problem.
A simplified layout of my report is:

SiteID

Date, CallID, Call_Details, Completion_Date

I am using Access97, all of the above fields are in one table : Call_Log

Regards

John McCormack
 
John,

First of all, make a Totals Query to get the Sites with multiple calls.
The SQL view of such a query will look something like...
SELECT SiteID, Count(CallID) AS Calls
FROM Call_Log
WHERE [Date]>(Date()-7)
GROUP BY SiteID
HAVING Count(CallID)>1

Then, make another query, which your report will be based on, which
includes this first query, and also the Call_Log table, joined on the
SiteID field from both.

Something unrelated to your question... The word 'date' is a Reserved
Word (i.e. has a special meaning) in Access, and as such should not be
used as the name of a field or control or database object.
 
Thanks Steve,

That worked perfectly.

Regards

John McC

Steve Schapel said:
John,

First of all, make a Totals Query to get the Sites with multiple calls.
The SQL view of such a query will look something like...
SELECT SiteID, Count(CallID) AS Calls
FROM Call_Log
WHERE [Date]>(Date()-7)
GROUP BY SiteID
HAVING Count(CallID)>1

Then, make another query, which your report will be based on, which
includes this first query, and also the Call_Log table, joined on the
SiteID field from both.

Something unrelated to your question... The word 'date' is a Reserved
Word (i.e. has a special meaning) in Access, and as such should not be
used as the name of a field or control or database object.

--
Steve Schapel, Microsoft Access MVP


John said:
I have a service call database, and have created a report which shows the
calls for each site.
I want to report only on those sites which had multiple calls as I am trying
to review the number of repeat calls to solve the initial problem.
A simplified layout of my report is:

SiteID

Date, CallID, Call_Details, Completion_Date

I am using Access97, all of the above fields are in one table : Call_Log

Regards

John McCormack
Click to expand...
Click to expand...
 
Back
Top