Flag first occurrence of the duplicate flagged as 1 and second occurrence flagged wit

candy1

Registered User.
Local time
Yesterday, 16:19
Joined
Nov 8, 2016
Messages
11
I am familiar with finding and identifying duplicate column in excel sheet using the formula below

=IF(COUNTIF(A:A,A1)>1,0,1)
with the first occurrence of the duplicate flagged as 1 and second occurrence flagged with 0

I am working in access database and would like to create similar field in access using expression builder function.

I am trying not to work in both excel and access.

Thank you immensely in advance for your assistance
 
There is no order in a table until you give it order. That means no record is first, none second, no next, no prior and no 238th. Therefore to find "duplicates" you need at least 2 fields--one field holds the values that are the same and the other field you use to apply order to the records:

Product, OrderDate, DeliveryDate
AA77, 1/2/2016, 2/9/2016
BB89, 1/3/2016, 1/7/2016
AA77, 1/8/2016, 1/9/2016
AA77, 1/9/2016, 2/1/2016

In the above sample data there are 3 values with AA77 for Product. The "first" value could be determined 2 diferent ways--By OrderDate or by DeliveryDate. Therefore, asking 'What's the first duplicate for Product?' doesn't give enough information to decide that.

So, to help with your data, we need to know more about it. What the value fields and what are your ordering fields? Perhaps some sample data to demonstrate would help.
 
CuostimerID, Product, purchase date, combinedstring
AA77, bag, 1/2/2016, AA77bag1/2/2016
BB89, bag, 1/7/2016, BB89bag1/7/2016
AA77, bag, 1/2/2016, AA77bag1/2/2016
AA77, bag , 1/9/2016, AA77bag1/9/2016
AA77, apple, 1/2/2016, AA77apple1/2/2016
AA77, bag, 1/9/2016 , AA77bag1/9/2016
BB89, cheese, 1/7/2016 , BB89cheese1/7/2016



I have different customers with either buying the same product on the same day or same product on different days. I created a field which has the customerID , product and transaction date joined.
Thank you for your help
 
Last edited:
I agree with plog's comments.
Also, being familiar with Excel may not be helpful when using Access. Often, people have to un-learn Excel to understand database concepts.

I see you have done something with his info. So which record do you think should be 1, why?
 
AA77, bag, 1/2/2016, AA77bag1/2/2016, 1, original first encounter
AA77, bag, 1/2/2016, AA77bag1/2/2016, 0, duplicate encounter
BB89, bag, 1/7/2016, BB89bag1/7/2016, 1, original first encounter
AA77, bag , 1/9/2016, AA77bag1/9/2016, 1, original first encounter
AA77 , apple , 1/2/2016, AA77apple1/2/2016, 1, original first encounter
AA77, bag, 1/9/2016 , AA77bag1/9/2016, 0 duplicate encounter
BB89, cheese, 1/7/2016 , BB89cheese1/7/2016, 1, original first encounter
Thank you
 
Usually in database, you do NOT want duplicates. So you design your tables to identify and process/handle duplicates according to your business rules.

Relational tables have a PrimaryKey which uniquely identifies each and every record in that table.
In some cases, where multiple fields are required to make a unique identifier, you can have a composite unique index.

So bottom line --normally you avoid, or process/remove any duplicates as close to the source as possible.

Good luck.
 
Thank you for your impute and assistance.

The data at hand is the information that I currently have to work with and trying to analyze without deleting records
 
Jdraw thank you for the information and reference Truly appreciated
 
There is no order in a table until you give it order. That means no record is first, none second, no next, no prior and no 238th. Therefore to find "duplicates" you need at least 2 fields--one field holds the values that are the same and the other field you use to apply order to the records:

Product, OrderDate, DeliveryDate
AA77, 1/2/2016, 2/9/2016
BB89, 1/3/2016, 1/7/2016
AA77, 1/8/2016, 1/9/2016
AA77, 1/9/2016, 2/1/2016

In the above sample data there are 3 values with AA77 for Product. The "first" value could be determined 2 diferent ways--By OrderDate or by DeliveryDate. Therefore, asking 'What's the first duplicate for Product?' doesn't give enough information to decide that.

So, to help with your data, we need to know more about it. What the value fields and what are your ordering fields? Perhaps some sample data to demonstrate would help.

plog,
Thank you for your time. Below is sample information. Could you be so kind as to let me know if the information is enough to assist?

CuostimerID, Product, purchase date, combinedstring, unique, results
AA77, bag, 1/2/2016, AA77bag1/2/2016, 1, original first encounter
AA77, bag, 1/2/2016, AA77bag1/2/2016, 0, duplicate encounter
BB89, bag, 1/7/2016, BB89bag1/7/2016, 1, original first encounter
AA77, bag , 1/9/2016, AA77bag1/9/2016, 1, original first encounter
AA77 , apple , 1/2/2016, AA77apple1/2/2016, 1, original first encounter
AA77, bag, 1/9/2016 , AA77bag1/9/2016, 0 duplicate encounter
BB89, cheese, 1/7/2016 , BB89cheese1/7/2016, 1, original first encounter
 
you said

The data at hand is the information that I currently have to work with
which looks remarkably like Plogs example (he must have supernatural powers!).

with the data you have, you cannot determine a first from this data

AA77, bag, 1/2/2016
AA77, bag, 1/2/2016

either could be first - you need an additional field -perhaps an arbitrary primary key or a time stamp to be able to differentiate them.

So if this really is your real data, I don't think a solution can be provided.

Good luck with your project
 
you said

which looks remarkably like Plogs example (he must have supernatural powers!).

with the data you have, you cannot determine a first from this data

AA77, bag, 1/2/2016
AA77, bag, 1/2/2016

either could be first - you need an additional field -perhaps an arbitrary primary key or a time stamp to be able to differentiate them.

So if this really is your real data, I don't think a solution can be provided.

Good luck with your project
Thank you for your time.
There is a date with time field such as 01/02/2016 13:01.
Thank you
 
OK, so getting clearer

try something like this

Code:
 SELECT O.*, iif(PurchaseDate=FirstTime,"original first encounter", "duplicate encounter") AS Encounter
FROM myTable O 
     INNER JOIN (SELECT CuostimerID, Product, Datevalue(PurchaseDate) AS TDate, min(PurchaseDate) AS FirstTime FROM myTable GROUP BY CuostimerID, Product, Datevalue(PurchaseDate)) F
        ON O.CuostimerID=F.CuostimerID AND O.Product=F.Product AND Datevalue(O.PurchaseDate)=TDate
ORDER BY O.CuostimerID, O.Product, O.PurchaseDate
 
Thank you so much for your response.
Can I type the code in expression builder?
Thank you for helping my novice self.
 
no because it uses a subquery, but you can do that as a separate queries where you can use the query builder

you can put this in a query - call it query1

Code:
 SELECT CuostimerID, Product, Datevalue(PurchaseDate) AS TDate, min(PurchaseDate) AS FirstTime FROM myTable GROUP BY CuostimerID, Product, Datevalue(PurchaseDate)

then your main query becomes

Code:
 SELECT O.*, iif(PurchaseDate=FirstTime,"original first encounter", "duplicate encounter") AS Encounter
FROM myTable O 
     INNER JOIN Query1 F
        ON O.CuostimerID=F.CuostimerID AND O.Product=F.Product AND Datevalue(O.PurchaseDate)=TDate
ORDER BY O.CuostimerID, O.Product, O.PurchaseDate
 
Thank you so much for your kind assistance.
 

Users who are viewing this thread

Back
Top Bottom