Duplicate Records w/ Date Criteria

[Guest]

I have my query showing duplicate records using the Electronic # and customer
# fields. I want it to just show the duplicate records if the customer has
bought the same electronic item within 60 days. I have a Date Entered field
using the Date() code and was trying to use field as my criteria.

Example:
Electronic # Electronic Customer # Customer Name Date
Entered
1 TV 1 Brown, Helen
10/15/05
1 TV 1 Brown, Helen
11/1/05

My query would show the above records has the Customer ordered the same
electronic within 60 days of eachother. Can anyone help me figure out the
best formula to use. Thanks for any help w/ this issue.

 

[Tom Ellison]

Dear Sonya:

This is a fairly typical case where "Correlated Subquery" is the needed
device.

A subquery is a SELECT clause embedded within the SQL that functions to
return something to the "main query" in which it is contained.

What you need is a query that will return the date of the most recent
previous purchase and limit that to be within 60 days of the current
purchase.

First, would it be acceptable to show the results something like this?

Customer# / CustomerName / Electronic# / Electronic / DatePurchased /
PreviousDate

A query to do this would be:

SELECT Customer#, CustomerName, Electronic#, Electronic,
[Date] AS DatePurchased,
(SELECT MAX([Date]) FROM YourTable T1
WHERE T1.Customer# = T.CustomerNumber
AND T1.Electronic# = T.Electronic#) AS PrevPurch
FROM YourTable T

This would provide unfiltered results. You could then base another query on
this one, filtering out those where there was NO previous purchase
(PrevPurch is null) and those where the interval is more than 60 days.

Now, what do you want to see if a single customer purchased and item 3 times
at 35 day intervals? The above would show two rows, the first with
purchases A and B, the other with B and C.

I can think of a way of producing what you asked for as well, with a row for
each purchase. If you want that, do you want to see the most recent
purchase whether there is a previous purchase witnin 60 days, or omit the
most recent pruchase as well?

For this method:

SELECT Customer#, CustomerName, Electronic#, Electronic, [Date]
FROM YourTable T
WHERE (SELECT MIN(ABS(DateDiff(T1[Date], T.[Date])))
FROM YourTable T1
WHERE T1.Customer# = T.Customer#
AND T1.Electronic# = T.Electronic#) <= 60

If any of your date/time values in [Date] contain time components, it gets a
bit more complex.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

 

[Guest]

Hi Tom. I am sorry, but I am kind of confused as I am not really
knowledgeable on sql and etc. I am not sure if this makes a difference, but
I have 2 tables (one w/ the electronic # and electronic description and the
other w/ the customer information). I don't have a purchased date field.
What I have is an issued date, but I want to know if they purchased this item
before I issue the new one. That is why I was using the entered date field
which is set to Date(). My query will include the duplicates records using
electronic # and customer # fields and then filter the records by using the
entered date field compared to the last issued date. And if the issued date
is less that 60 days of the entered date it would appear in my query. I hope
this make sense. Thanks. Sonya

Dear Sonya:

This is a fairly typical case where "Correlated Subquery" is the needed
device.

A subquery is a SELECT clause embedded within the SQL that functions to
return something to the "main query" in which it is contained.

What you need is a query that will return the date of the most recent
previous purchase and limit that to be within 60 days of the current
purchase.

First, would it be acceptable to show the results something like this?

Customer# / CustomerName / Electronic# / Electronic / DatePurchased /
PreviousDate

A query to do this would be:

SELECT Customer#, CustomerName, Electronic#, Electronic,
[Date] AS DatePurchased,
(SELECT MAX([Date]) FROM YourTable T1
WHERE T1.Customer# = T.CustomerNumber
AND T1.Electronic# = T.Electronic#) AS PrevPurch
FROM YourTable T

This would provide unfiltered results. You could then base another query on
this one, filtering out those where there was NO previous purchase
(PrevPurch is null) and those where the interval is more than 60 days.

Now, what do you want to see if a single customer purchased and item 3 times
at 35 day intervals? The above would show two rows, the first with
purchases A and B, the other with B and C.

I can think of a way of producing what you asked for as well, with a row for
each purchase. If you want that, do you want to see the most recent
purchase whether there is a previous purchase witnin 60 days, or omit the
most recent pruchase as well?

For this method:

SELECT Customer#, CustomerName, Electronic#, Electronic, [Date]
FROM YourTable T
WHERE (SELECT MIN(ABS(DateDiff(T1[Date], T.[Date])))
FROM YourTable T1
WHERE T1.Customer# = T.Customer#
AND T1.Electronic# = T.Electronic#) <= 60

If any of your date/time values in [Date] contain time components, it gets a
bit more complex.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

I have my query showing duplicate records using the Electronic # and
customer
# fields. I want it to just show the duplicate records if the customer
has
bought the same electronic item within 60 days. I have a Date Entered
field
using the Date() code and was trying to use field as my criteria.

Example:
Electronic # Electronic Customer # Customer Name
Date
Entered
1 TV 1 Brown,
Helen
10/15/05
1 TV 1 Brown,
Helen
11/1/05

My query would show the above records has the Customer ordered the same
electronic within 60 days of eachother. Can anyone help me figure out the
best formula to use. Thanks for any help w/ this issue.

 

[Tom Ellison]

Dear Sonya:

I realize you do not have a PurchasedDate column in your table, nor a
PrevDate column. However, the query I proposed would retrieve both these
dates from two separate rows of the table and return both of them in one
row. I named them as I did to distinguish one from the other.

If you would correct any of the column names I used and change to your
actual table name, then run this query, perhaps you would understand what
I'm suggesting. One change you'd need to make is to insert spaces in your
column names where necessary and put square brackets [ ] around them. When
you have queries to write, it's really handy not to have the spaces in the
column names, just letters and digits.

Tom Ellison

Hi Tom. I am sorry, but I am kind of confused as I am not really
knowledgeable on sql and etc. I am not sure if this makes a difference,
but
I have 2 tables (one w/ the electronic # and electronic description and
the
other w/ the customer information). I don't have a purchased date field.
What I have is an issued date, but I want to know if they purchased this
item
before I issue the new one. That is why I was using the entered date
field
which is set to Date(). My query will include the duplicates records
using
electronic # and customer # fields and then filter the records by using
the
entered date field compared to the last issued date. And if the issued
date
is less that 60 days of the entered date it would appear in my query. I
hope
this make sense. Thanks. Sonya

Dear Sonya:

This is a fairly typical case where "Correlated Subquery" is the needed
device.

A subquery is a SELECT clause embedded within the SQL that functions to
return something to the "main query" in which it is contained.

What you need is a query that will return the date of the most recent
previous purchase and limit that to be within 60 days of the current
purchase.

First, would it be acceptable to show the results something like this?

Customer# / CustomerName / Electronic# / Electronic / DatePurchased /
PreviousDate

A query to do this would be:

SELECT Customer#, CustomerName, Electronic#, Electronic,
[Date] AS DatePurchased,
(SELECT MAX([Date]) FROM YourTable T1
WHERE T1.Customer# = T.CustomerNumber
AND T1.Electronic# = T.Electronic#) AS PrevPurch
FROM YourTable T

This would provide unfiltered results. You could then base another query
on
this one, filtering out those where there was NO previous purchase
(PrevPurch is null) and those where the interval is more than 60 days.

Now, what do you want to see if a single customer purchased and item 3
times
at 35 day intervals? The above would show two rows, the first with
purchases A and B, the other with B and C.

I can think of a way of producing what you asked for as well, with a row
for
each purchase. If you want that, do you want to see the most recent
purchase whether there is a previous purchase witnin 60 days, or omit the
most recent pruchase as well?

For this method:

SELECT Customer#, CustomerName, Electronic#, Electronic, [Date]
FROM YourTable T
WHERE (SELECT MIN(ABS(DateDiff(T1[Date], T.[Date])))
FROM YourTable T1
WHERE T1.Customer# = T.Customer#
AND T1.Electronic# = T.Electronic#) <= 60

If any of your date/time values in [Date] contain time components, it
gets a
bit more complex.

Please let me know if this helped, and if I can be of any other
assistance.

Tom Ellison

I have my query showing duplicate records using the Electronic # and
customer
# fields. I want it to just show the duplicate records if the customer
has
bought the same electronic item within 60 days. I have a Date Entered
field
using the Date() code and was trying to use field as my criteria.

Example:
Electronic # Electronic Customer # Customer Name
Date
Entered
1 TV 1 Brown,
Helen
10/15/05
1 TV 1 Brown,
Helen
11/1/05

My query would show the above records has the Customer ordered the same
electronic within 60 days of eachother. Can anyone help me figure out
the
best formula to use. Thanks for any help w/ this issue.