Count number of records for a policy

J

Joe Mac

All...

I'm new to Access and SQL... I ran a core "Duplicates" query and captured
the SQL as well as a simple update query and captured the SQL as well... I
attempted to combine the SQL from both to create a new Update query to
calculate the number of Policies in the datbase unique for each Policy Number
and then update each policy record with that count... attached is the
composite SQL...

Example of what I'd like to do: There are 2 records in the database under
the Policy number of 123; I'd like to have each record updated in the
Policy_Count filed to read 2...
Policy Policy_Count
123 2
123 2

UPDATE [Policy Numbers] SET [Policy Numbers].POLICY_COUNT = (SELECT
First([Policy Numbers].CLIENT_ASGN_CUST_ID) AS [CLIENT_ASGN_CUST_ID Field],
Count([Policy Numbers].CLIENT_ASGN_CUST_ID) AS NumberOfDups
FROM [Policy Numbers]
GROUP BY [Policy Numbers].CLIENT_ASGN_CUST_ID
HAVING (((Count([Policy Numbers].CLIENT_ASGN_CUST_ID))>1)));

I hope this makes sense -
 
C

Cheese_whiz

Hi Joe,

Instead of doing all that, just use the domain aggregate function DCount to
count the records with matching policy numbers. You can use it as the
control source of a text box control on your form, or in the alternative, you
can use it as the value of a calculated field in a query and then bind the
control on the form to that calculated field. There's no need to store that
data in a table since it can always be calculated, and it will always be 'up
to date'.

To do the former:

If you have a tblPolicies (table) that holds the main data about your
policies, and there's a field in that table called policyNo and you have a
form with a control bound to that table field called txtPolicyNo, then add
another control to the form and name it txtPolicyCount and set it's control
source property to:

=DCount("PolicyNo", "tblPolicies", "policyNo = """ & Me.txtPolicyNo & """")

That assumes that the field policyNo in tblPolicies is a text field. If
it's a number, then it would be:

=DCount("PolicyNo", "tblPolicies", "policyNo = " & Me.txtPolicyNo)

Obviously, you need to change all the names to match your
table/field/controls/etc.

HTH,
CW
 
J

Joe Mac

Hey CW...

Thank you for the response - sorry for the delay, but I've been in back to
back meetings since my initial posting...
Let me clarify the situation I have a bit and maybe that will help better
direct the response... I don't have a form in an application or build... I
have @250k records imported from Excel (which can't handle the volume without
much manual manipulation)... I've imported the data into Access to allow for
better sorting and aggregation of the numbers... I need to sort/group and
count the policy numbers based upon the total number of records aligned to a
Policy... I did try the DCount function before my earlier post and received
the following error...

You have written a subquery that canb return more than one field without
usdingthe EXIST reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field. (Error 3306)

I used the Query Build wizard to construct the Update Query... here is the
SQL that was generated...

Where Policy Numbers = the table (domain)
Where POLICY_COUNT is the update field that I'd like to carry the total
value for number of records aligned to the policy (in each record)
Where CLIENT_ASGN_CUST_ID is the Policy Number

UPDATE [Policy Numbers] SET [Policy Numbers].POLICY_COUNT = DCount([Policy
Numbers]!CLIENT_ASGN_CUST_ID,[Policy Numbers],[Policy
Numbers]!CLIENT_ASGN_CUST_ID=[Policy Numbers]!CLIENT_ASGN_CUST_ID);

Any assistance is greatly appreciated-
--

Joe Mac


Cheese_whiz said:
Hi Joe,

Instead of doing all that, just use the domain aggregate function DCount to
count the records with matching policy numbers. You can use it as the
control source of a text box control on your form, or in the alternative, you
can use it as the value of a calculated field in a query and then bind the
control on the form to that calculated field. There's no need to store that
data in a table since it can always be calculated, and it will always be 'up
to date'.

To do the former:

If you have a tblPolicies (table) that holds the main data about your
policies, and there's a field in that table called policyNo and you have a
form with a control bound to that table field called txtPolicyNo, then add
another control to the form and name it txtPolicyCount and set it's control
source property to:

=DCount("PolicyNo", "tblPolicies", "policyNo = """ & Me.txtPolicyNo & """")

That assumes that the field policyNo in tblPolicies is a text field. If
it's a number, then it would be:

=DCount("PolicyNo", "tblPolicies", "policyNo = " & Me.txtPolicyNo)

Obviously, you need to change all the names to match your
table/field/controls/etc.

HTH,
CW



Joe Mac said:
All...

I'm new to Access and SQL... I ran a core "Duplicates" query and captured
the SQL as well as a simple update query and captured the SQL as well... I
attempted to combine the SQL from both to create a new Update query to
calculate the number of Policies in the datbase unique for each Policy Number
and then update each policy record with that count... attached is the
composite SQL...

Example of what I'd like to do: There are 2 records in the database under
the Policy number of 123; I'd like to have each record updated in the
Policy_Count filed to read 2...
Policy Policy_Count
123 2
123 2

UPDATE [Policy Numbers] SET [Policy Numbers].POLICY_COUNT = (SELECT
First([Policy Numbers].CLIENT_ASGN_CUST_ID) AS [CLIENT_ASGN_CUST_ID Field],
Count([Policy Numbers].CLIENT_ASGN_CUST_ID) AS NumberOfDups
FROM [Policy Numbers]
GROUP BY [Policy Numbers].CLIENT_ASGN_CUST_ID
HAVING (((Count([Policy Numbers].CLIENT_ASGN_CUST_ID))>1)));

I hope this makes sense -
Click to expand...
Click to expand...
 
J

John Spencer

The Update query would look like this.

UPDATE [Policy Numbers]
SET [Policy Numbers].POLICY_COUNT =
DCount("*","[Policy Numbers]","CLIENT_ASGN_CUST_ID=""" &
CLIENT_ASGN_CUST_ID & """");

You could use a subquery in main query select clause to get the count.

Build a query to get the count and then join that query to your table

SELECT CLIENT_ASGN_CUST_ID, Count(CLIENT_ASGN_CUST_ID) as Policy_Count
FROM [Policy Numbers]
GROUP BY CLIENT_ASGN_CUST_ID

Now using that saved query (name it qPolicyCount) as if it were a table
Join it to your table in a query that might look something like the
following:

SELECT P.*, Q.Policy_Count
FROM [Policy Numbers] as P INNER JOIN qPolicyCount as Q
ON P.CLIENT_ASGN_CUST_ID = Q.CLIENT_ASGN_CUST_ID



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
J

Joe Mac

Thank you John...

I've got what I need... this has helped me significantly
--

Joe Mac


John Spencer said:
The Update query would look like this.

UPDATE [Policy Numbers]
SET [Policy Numbers].POLICY_COUNT =
DCount("*","[Policy Numbers]","CLIENT_ASGN_CUST_ID=""" &
CLIENT_ASGN_CUST_ID & """");

You could use a subquery in main query select clause to get the count.

Build a query to get the count and then join that query to your table

SELECT CLIENT_ASGN_CUST_ID, Count(CLIENT_ASGN_CUST_ID) as Policy_Count
FROM [Policy Numbers]
GROUP BY CLIENT_ASGN_CUST_ID

Now using that saved query (name it qPolicyCount) as if it were a table
Join it to your table in a query that might look something like the
following:

SELECT P.*, Q.Policy_Count
FROM [Policy Numbers] as P INNER JOIN qPolicyCount as Q
ON P.CLIENT_ASGN_CUST_ID = Q.CLIENT_ASGN_CUST_ID



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Joe said:
Hey CW...

Thank you for the response - sorry for the delay, but I've been in back to
back meetings since my initial posting...
Let me clarify the situation I have a bit and maybe that will help better
direct the response... I don't have a form in an application or build... I
have @250k records imported from Excel (which can't handle the volume without
much manual manipulation)... I've imported the data into Access to allow for
better sorting and aggregation of the numbers... I need to sort/group and
count the policy numbers based upon the total number of records aligned to a
Policy... I did try the DCount function before my earlier post and received
the following error...

You have written a subquery that canb return more than one field without
usdingthe EXIST reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field. (Error 3306)

I used the Query Build wizard to construct the Update Query... here is the
SQL that was generated...

Where Policy Numbers = the table (domain)
Where POLICY_COUNT is the update field that I'd like to carry the total
value for number of records aligned to the policy (in each record)
Where CLIENT_ASGN_CUST_ID is the Policy Number

UPDATE [Policy Numbers] SET [Policy Numbers].POLICY_COUNT = DCount([Policy
Numbers]!CLIENT_ASGN_CUST_ID,[Policy Numbers],[Policy
Numbers]!CLIENT_ASGN_CUST_ID=[Policy Numbers]!CLIENT_ASGN_CUST_ID);

Any assistance is greatly appreciated-
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

How to count the numbers in a Word document? 0
Crosstab query possibly? 2
How do I restrict count of records if = to certain words 1
Avoiding Redundant Records 15
Can I do this in a sub-form? 2
Mail Merge Multiple Records 5
Insert Index into a Table 1
Show for then one policy number if applicable in a form 1

Top