eliminating duplicates

  • Thread starter Thread starter my-wings
  • Start date Start date
M

my-wings

I don't know if this is do-able nor not, but I have a situation where if I
have duplicates, I want to eliminate BOTH records from the query results.
I'll post the details in case someone can think up a solution in a totally
different direction from the one I'm heading in.

The situation is that I have a set of records that represent insurance
policies. These are generally 12 month policies, but they are occasionally
longer or shorter. Each policy has to be renewed on its expiration date. I
have a form where I can set month and year, and a few other details like
salesperson or underwriter, and I query the database to bring me the
policies that meet my other criteria and that expire for the given
month/year.

The problem is, if the policy has already been renewed, I don't want it
listed. Right now, I'm just listing policies in order by customer name, for
the current year and one year in the future. Then the underwriters are
visually scanning the list and clicking a button to renew the files that
don't already show a policy for next year. I'm wondering if there is any way
I could eliminate both current year and future year policies in a query and
only pick up those files that haven't been renewed. So far, the only thing I
can think of (besides some feature of "distinct" that I don't know about) is
to add a checkbox or something to the original record and check it when the
record is copied to make the renewal.

But I'm going to lose the tracking if someone creates a renewal and then
deletes it.

Any thoughts?

Alice
 
Alice,

I would imagine that when a policy gets renewed, the expiration date
is updated? Or else, on what basis do you know whether it has already
been renewed or not? In other words, what is the meaning of "files
that don't already show a policy for next year"? Maybe if you listed
the relevant tables and fields, it may help to understand your data
requirements?

- Steve Schapel, Microsoft Access MVP
 
Steve Schapel said:
Alice,

I would imagine that when a policy gets renewed, the expiration date
is updated? Or else, on what basis do you know whether it has already
been renewed or not? In other words, what is the meaning of "files
that don't already show a policy for next year"? Maybe if you listed
the relevant tables and fields, it may help to understand your data
requirements?
Click to expand...

Sorry I wasn't very exact.

There is one record per policy. When a policy is renewed, a new record is
created for the next year. I think the fields that would be helpful are
[dteEffective] [dteExpires] and [strPolicyNo].

If the policy is renewed, I would have a situation like the following:

Record# 1 [strPolicyNo] = 1025 [dteEffective] = 10/1/03 [dteExpires] =
10/1/04
Record# 2 [strPolicyNo] = 1025 [dteEffective] = 10/1/04 [dteExpires] =
10/1/05

At the moment, nothing is done to Record# 1 when Record# 2 is created.
Assuming I was interested in October, 2003, and since I'm trying to query
for records that have *not* been renewed, I would not want either Record# 1
or Record# 2 to show up in my query.

Maybe there is a way to select the record with the highest expiration date,
and then not list it at all if it is greater than the month and year I'm
interested in. ????

Hope this clarifies the situation somewhat. Thanks for any help you can
give.

Alice
 
Alice,

Thanks for the further clarification.

You can find the latest dates for each policy by using a Totals Query.
If you use the query design view to make a query based on your
policies table, and then select Totals from the View menu. Then,
leave Group By entered in the Totals row of the query grid for the
strPolicyNo field, and enter Max in the dteExpires column. You can
then enter your criteria as applicable to find policies expired at any
given date, or within a date range.

- Steve Schapel, Microsoft Access MVP
 
One possible method, though it could be slow, is the following UNTESTED SQL.

SELECT T1.strPolicyNO
FROM tblPolicies as T1
WHERE T1.DteEffective
Between #1/1/03# and #1/31/03#
AND T1.dteEffective =
(SELECT Max(t2.dteEffective)
FROM tblPolicies as T2
WHERE t2.strPolicyNo = t1.strPolicyNo)


my-wings said:
Steve Schapel said:
Alice,

I would imagine that when a policy gets renewed, the expiration date
is updated? Or else, on what basis do you know whether it has already
been renewed or not? In other words, what is the meaning of "files
that don't already show a policy for next year"? Maybe if you listed
the relevant tables and fields, it may help to understand your data
requirements?
Click to expand...

Sorry I wasn't very exact.

There is one record per policy. When a policy is renewed, a new record is
created for the next year. I think the fields that would be helpful are
[dteEffective] [dteExpires] and [strPolicyNo].

If the policy is renewed, I would have a situation like the following:

Record# 1 [strPolicyNo] = 1025 [dteEffective] = 10/1/03 [dteExpires] =
10/1/04
Record# 2 [strPolicyNo] = 1025 [dteEffective] = 10/1/04 [dteExpires] =
10/1/05

At the moment, nothing is done to Record# 1 when Record# 2 is created.
Assuming I was interested in October, 2003, and since I'm trying to query
for records that have *not* been renewed, I would not want either Record# 1
or Record# 2 to show up in my query.

Maybe there is a way to select the record with the highest expiration date,
and then not list it at all if it is greater than the month and year I'm
interested in. ????

Hope this clarifies the situation somewhat. Thanks for any help you can
give.

Alice
Click to expand...
 
Steve Schapel said:
Alice,

Thanks for the further clarification.

You can find the latest dates for each policy by using a Totals Query.
If you use the query design view to make a query based on your
policies table, and then select Totals from the View menu. Then,
leave Group By entered in the Totals row of the query grid for the
strPolicyNo field, and enter Max in the dteExpires column. You can
then enter your criteria as applicable to find policies expired at any
given date, or within a date range.

- Steve Schapel, Microsoft Access MVP
Click to expand...

Thanks for your response. I will try this, but the merits of not rousing
sleeping dogs are beginning to look more and more attractive as I
contemplate all the other things I would have to change to make this work!

Alice
 
Another method. This should be relatively quick, and will return all
of the information in your table for those records where the maximum
expiration date is within the current month (Oct 03).

This method uses a subquery with a GroupBy clause to identify the
maximum policy expiration date in the table for each policy number.
It then uses a Having clause to eliminate those that fall outside of
the current month. To get the other information for this record, you
have to join this subquery to your table by the policy number and the
expiration date.

SELECT T.*
FROM yourTable T.
INNER JOIN
(SELECT strPolicyNo, MAX(dteExpires)as MaxExpires
FROM yourTable
GROUP BY strPolicyNo
HAVING MAX(dteExpires) BETWEEN #10/1/03# AND #10/31/03#) as MR
ON T.strPolicyNo = MR.strPolicyNo
AND T.dteExpires = MR.MaxExpires


--
HTH

Dale Fye



Steve Schapel said:
Alice,

I would imagine that when a policy gets renewed, the expiration date
is updated? Or else, on what basis do you know whether it has already
been renewed or not? In other words, what is the meaning of "files
that don't already show a policy for next year"? Maybe if you listed
the relevant tables and fields, it may help to understand your data
requirements?
Click to expand...

Sorry I wasn't very exact.

There is one record per policy. When a policy is renewed, a new record
is
created for the next year. I think the fields that would be helpful
are
[dteEffective] [dteExpires] and [strPolicyNo].

If the policy is renewed, I would have a situation like the following:

Record# 1 [strPolicyNo] = 1025 [dteEffective] = 10/1/03 [dteExpires]
=
10/1/04
Record# 2 [strPolicyNo] = 1025 [dteEffective] = 10/1/04 [dteExpires]
=
10/1/05

At the moment, nothing is done to Record# 1 when Record# 2 is created.
Assuming I was interested in October, 2003, and since I'm trying to
query
for records that have *not* been renewed, I would not want either
Record# 1
or Record# 2 to show up in my query.

Maybe there is a way to select the record with the highest expiration
date,
and then not list it at all if it is greater than the month and year
I'm
interested in. ????

Hope this clarifies the situation somewhat. Thanks for any help you
can
give.

Alice
 
Back
Top