find duplicates in table A before Joining with Table b

[mud2]

Given two tables, Table A and table B. Table B may contain duplicate values of those in table B. I want to COPUNBT the number of these duplicates before joining B with A, using DISCRETE, so that the final table contains no duplicates. But I must know how many duplicates there wer...for future editing.

I.e. Table A: a b c d e f
I.e. Table B: x a e b y
Final table: a b c d e f x y
and there are 2 values of a, two of b, and two of e.

At a later time I might have Table C: a l m b n o
Final table will be: a b c d e f l m n o,
withy a record now of: Three of a, three of b, and two of e.


(If I counted right)!

[OBP]

I would suggest a non VBA method that is the easiest and most "checkable" method would be to have 2 tables one with duplicates allowed in the field containing your duplicates and a second table that has "no duplicates" allowed.
Populate the first (duplicates allowed) table using queries to append your data from the tables A, B and C (when you have it). This will create a table with all the duplicated data in, which you can now easily check for counts prior to the next part.
Run a query with totals and group by the "duplicates" and have a "count" function which will count your duplications.
In the second new table (with no duplicates) have an extra field to hold the count value of your duplicates. Use an append query based on the "Totals query" to fill this table with data.

Unless of course you are looking for a VBA only answer.