Finding "older" duplicates

[Trey]

I am trying to make a database for tracking calls, the caller gets one free
call (per 12 months), then is charged a flat fee for every call after the
first call in a 12 month time span. The data is imported into the database
weekly (about 120 calls a week) from a text file. I am able to query the
12-month part. But I am having trouble making a statement/macro/query to
check for duplicate entries with the same address in the database, with a
date and time older then the current record that is being processed to see
if an older duplicate exists. Then, if a duplicate exists, then the
"billable" field will be checked "yes", if a duplicate is not found then the
"warning" field is checked "yes".



Since someone from the same address may call twice in one day, they would
get the first call free, then they would be charged for next call. But I do
not know how to make the statement tell the difference between the first
entry from the following entries.

I am currently using Access 2000 for this project.



Any help would be very much appreciated!

 

[Jeff Boyce]

Trey

Are you saying that you want to count how many existing records have the
same address and the same year (?calendar year)? What happens if someone
calls on December 31st, then again on January 1st (the next year)? Or are
you saying that the first (free) call "starts the 12 month clock"?

Another approach might be to skip marking any as "billable"?! If you just
keep adding in records, you can run a query that counts how many you have
per address (Totals query, GroupBy Address). If the count's greater than
one, multiple the count-1 * billing amount.

Or have I missed something?

 

[Trey]

Trey

Are you saying that you want to count how many existing records have
the same address and the same year (?calendar year)? What happens if
someone calls on December 31st, then again on January 1st (the next
year)? Or are you saying that the first (free) call "starts the 12
month clock"?

Another approach might be to skip marking any as "billable"?! If you
just keep adding in records, you can run a query that counts how many
you have per address (Totals query, GroupBy Address). If the count's
greater than one, multiple the count-1 * billing amount.

Or have I missed something?

I am using a datediff query to count the number of months from the day the
call is put in up to 'today', then just have it return the records that are
12 months or less. (one free call per 12 months, clock starts when they
call)
I have a query set up the way you mention it. but I have a feeling the
callers would also like a history on the bill as to when exactly the calls
were placed instead of just a bill that says "you called three times, give
us money now!" The only way I can think of doing this is by running a query
to find all the records that are billable, then group by address, so there
can be a list on the bill (like a call history)
It would be easy to set it up where it will mark the records as billable, if
duplicates exist. But that first entry needs to be marked as "free" then all
the others as billable. If someone calls for the first time then calls again
an hour later. then when I import the text file, it will see there is a
duplicate, and mark them both as billable, instead of marking the fist as
free, and the second and others after that as billable.
Did that clarify anything? I am just not entirely sure how to ask the
question.

 

[Jeff Boyce]

Trey

See replies in-line below...

I am using a datediff query to count the number of months from the day the
call is put in up to 'today', then just have it return the records that are
12 months or less. (one free call per 12 months, clock starts when they
call)

So, how do you decide when to restart the clock?

I have a query set up the way you mention it. but I have a feeling the
callers would also like a history on the bill as to when exactly the calls
were placed instead of just a bill that says "you called three times, give
us money now!"

Back to my suggestion ... if you use a Totals query to count how many, you
get the count. If you make it a simple select query, you get all the call
records. If you sort by date, you get the first one, plus all the rest.

The only way I can think of doing this is by running a query
to find all the records that are billable, then group by address, so there
can be a list on the bill (like a call history)

I didn't read far enough -- yes. (Assuming the address is entered
identically each time, or you are using an Address table to select it.)

It would be easy to set it up where it will mark the records as billable, if
duplicates exist. But that first entry needs to be marked as "free" then all
the others as billable.

Why? As in "why do you need to mark them at all?" If you are providing a
list of calls to the user, sorted by date, one approach might be to add a
statement in your report that says something like "You have called a total
of N times in the last 12 months. The first call was free, so the total
charge is N-1 * per-call-billable-amount."

If someone calls for the first time then calls again
an hour later. then when I import the text file, it will see there is a
duplicate, and mark them both as billable, instead of marking the fist as
free, and the second and others after that as billable.
Did that clarify anything? I am just not entirely sure how to ask the
question.

Or are you saying the only way you can/will do what you are trying is by
marking some as billable and some as free? What will this do to your tables
and routines if, at some point in the future, you decide that the first two
calls are free? Or that none are?

If you use the report approach, you change one thing, one place.

 

[Trey]

Trey

See replies in-line below...

So, how do you decide when to restart the clock?

When the datediff is 13 or more months, it no longer shows up in the query,
all queries are then pulled form that query, instead of the table with all
the records from years past.

Back to my suggestion ... if you use a Totals query to count how
many, you get the count. If you make it a simple select query, you
get all the call records. If you sort by date, you get the first
one, plus all the rest.

hmmm, I will have to try this approach, see if I can make it work that way.

I didn't read far enough -- yes. (Assuming the address is entered
identically each time, or you are using an Address table to select
it.)

Yes, there is still the GiGo issue with the data entry. That is more of a
user training issue though, since the addresses need to be accurate for
other systems we have.

Why? As in "why do you need to mark them at all?" If you are
providing a list of calls to the user, sorted by date, one approach
might be to add a statement in your report that says something like
"You have called a total of N times in the last 12 months. The first
call was free, so the total charge is N-1 * per-call-billable-amount."

I have this query written, I just need to make it all work together now.

Or are you saying the only way you can/will do what you are trying is
by marking some as billable and some as free? What will this do to
your tables and routines if, at some point in the future, you decide
that the first two calls are free? Or that none are?

Not saying it will 'never' change, but the chances of it changing are slim.
at most, the fee will increase. I guess if I wanted to be really cool, I
could put the fee amount, number of free calls, and number of months all
into a table, and just call to them with variables.