Problems counting records in query

[Martin Prunty]

I am attempting to build a query where the total records in that query are
counted. When running the query, I receive a "1" as the count in each row of
the field "Total Records", but the query does not provide a count the total
number of records in the query.

Following is the code I've used, which is intended to count the number of
records in the field "Insurance_Company".

SELECT Results.Insurance_Company, Results.Timestamp, Results.Branch_Dept,
Results.Claim_Number, Results.Requester, Results.Requester_Telephone,
Results.Requester_Email, Count(Results.Insurance_Company) AS [Total Records]
FROM Results

Your help and/or suggestions will be appreciated.

 

[Jeff Boyce]

Martin

Depending on where you are trying to do this, you might take a look at the
DCount() function (in Access HELP).

Regards

Jeff Boyce
<Office/Access MVP>

 

[Ken Sheridan]

Martin:

Try this:

SELECT Insurance_Company,Timestamp,
Branch_Dept, Claim_Number, Requester,
Requester_Telephone, Requester_Email,
(SELECT COUNT(*)
FROMResults) AS [Total Records]
FROM Results;

Ken Sheridan
Stafford, England

 

[Martin Prunty]

Thank you. This works. Can I ask another related question?

What would I need to add to this query to now use "Timestamp" to sort the
results by month? In other words, I would like the report to list the count
of records by Insurance_Company, by month.

Thanks again,

Martin

Martin:

Try this:

SELECT Insurance_Company,Timestamp,
Branch_Dept, Claim_Number, Requester,
Requester_Telephone, Requester_Email,
(SELECT COUNT(*)
FROMResults) AS [Total Records]
FROM Results;

Ken Sheridan
Stafford, England

I am attempting to build a query where the total records in that query are
counted. When running the query, I receive a "1" as the count in each row of
the field "Total Records", but the query does not provide a count the total
number of records in the query.

Following is the code I've used, which is intended to count the number of
records in the field "Insurance_Company".

SELECT Results.Insurance_Company, Results.Timestamp, Results.Branch_Dept,
Results.Claim_Number, Results.Requester, Results.Requester_Telephone,
Results.Requester_Email, Count(Results.Insurance_Company) AS [Total Records]
FROM Results

Your help and/or suggestions will be appreciated.

 

[Ken Sheridan]

Martin:

If you still want to list all the detail records as well as the count per
company per month then you can restrict the subquery, using the YEAR and
MONTH functions so that it counts the rows for the current company/month. As
the subquery now needs to be correlated with the outer query the two
instances of the Results table are distinguished by using aliases R1 and R2:

SELECT Insurance_Company,Timestamp,
Branch_Dept, Claim_Number, Requester,
Requester_Telephone, Requester_Email,
(SELECT COUNT(*)
FROM Results AS R2
WHERE R2.Insurance-Company = R1.Insurance_Company
AND YEAR(R2.TimeStamp) = YEAR(R1.TimeStamp)
AND MONTH(R2.TimeStamp) = MONTH(R1.TimeStamp)) AS [Monthly Count]
FROM Results As R1;

You could also do this in the report itself. Base the report on the
following query:

SELECT Insurance_Company,Timestamp,
YEAR(Timestamp) As GroupYear,
MONTH(TimeStamp) AS GroupMonth,
Branch_Dept, Claim_Number, Requester,
Requester_Telephone, Requester_Email
FROM Results;

Then group the report via the sorting and grouping dialogue in report design
view firstly on Insurance_Company, then on GroupYear, then on GroupMonth,
giving the last group level a Group Footer. In the group footer put a text
box with its ControlSource property:

=Count(*)

If you merely want the aggregated monthly counts without any of the details
then you can group the rows by company, Year and month and count the rows for
each group:

SELECT
Insurance_Company,
YEAR(Timestamp) AS GroupYear,
MONTH(Timestamp) AS GroupMonth,
COUNT(*) AS MonthlyCount
GROUP BY
Insurance_Company,
YEAR(Timestamp) AS GroupYear,
MONTH(Timestamp) AS GroupMonth;

Ken Sheridan
Stafford, England