Access "countif"

Question

Is there a way I can assign a number value to duplicate occurrences in Access?  I need to be able to assign a unique value to a duplicate value.

Similar to the "=COUNTIF($A$2:A2,A2):" function in excel.

   Customer    Count of Occurrence

   Cust #1    ---   1

   Cust #2    ---   1

   Cust #3    ---   1

   Cust #1    ---   2

   Cust #1    ---   3

   Cust #2    ---   2

Answer 1

You might like to take a look at RowNumbering.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

One of the queries in this little demo returns an updatable recordset with sequential row numbers per customer.  It does this by calling the VBA DCount function, rather than using the more efficient, but non-updatable methods illustrated in two other queries using a join or a subquery.  The query is as follows:

SELECT DCOUNT("*","Transactions","CustomerID = " & CustomerID & "

AND TransactionDate  <=  #" & Format(TransactionDate,"yyyy-mm-dd") & "#

AND (TransactionID <= " & TransactionID & "

OR TransactionDate <> #" & Format(TransactionDate,"yyyy-mm-dd") & "#)")

AS RowNumber, CustomerID, TransactionDate, TransactionAmount

FROM Transactions

ORDER BY CustomerID, TransactionDate, TransactionID;

If a TransactionNumber column is added to the Transactions table the query can be converted as below to an UPDATE statement which inserts the computed transaction numbers per customer into the TransactionNumber column:

UPDATE Transactions

SET TransactionNumber =

DCOUNT("*","Transactions","CustomerID = " & CustomerID & "

AND TransactionDate  <=  #" & Format(TransactionDate,"yyyy-mm-dd") & "#

AND (TransactionID <= " & TransactionID & "

OR TransactionDate <> #" & Format(TransactionDate,"yyyy-mm-dd") & "#)");

As Tom pointed out earlier, it is necessary to identify a sort order per customer to do this.  In the above examples the sort order is by TransactionDate, and, because there could be two or more transactions per customer on the same date, the primary key TransactionID column is brought into play as the tie breaker.

Answer 2

Assume you have Table1 in Access, which has at least the field Cust. To that table, add an ID field that has the autonumber property.

“Read” file #1 into the table and ID will be unique for each row. Then “read” file #2 in the table and the IDs will continue to be unique for each row.

Answer 3

I am combining two files and when there are two of the same entries from one file matching against one from the other, the matches get doubled.  I was hoping that if I could make a unique value, entry 1, entry 2 then only entry 1 would match on both files and entry 2 would stand alone.

Answer 4

You would not be able to do that without also establishing a sort order, which would indicate why record 4 will be #2 rather than record 1.

And say you have this in place, what are you going to do with it? If we understand the end goal we may be able to suggest a way to break out of the Excel way of thinking.