Duplicate Counts

[BikeToWork]

My database has loan data where the loans are grouped by Month/Year and there are duplicates for most loan numbers based on the LoanNumber field. The table has multiple Month/Years and I need to somehow group the data as follows:

"Unique Loan Numbers: SUM(X, Y, Z)

Loans with 1 record: X (where X equals all unique loan numbers with one row of data)
Loans with 2 records: Y (where Y equals all unique loan numbers with two rows of data)
Loans with 3 records: Z (etc.)"

How does one group by Month and get a count of dupes for each distinct loan number? I'm not sure where to start. Any help is much appreciated.

[zmbd]

Something simple like this should work I would think?

SELECT [loan], Count([dateissued]) AS CountOfdateissued
FROM Table4
GROUP BY [loan];

[edit added the following data table]

[PK]   [loan]       [dateissued]
1        a1         09/01/2014
2        b2         09/02/2014
3        b2         09/03/2014
4        c3         09/04/2014
5        c3         09/05/2014
6        c3         09/06/2014
7        d51        10/01/2014
8        e62        10/02/2014
9        e62        10/02/2014
10       f73        10/03/2014
11       f73        10/03/2014
12       f73        10/03/2014
13       g89        10/11/2014
14       g89        10/11/2014
(...)  (...)       (...)
20       g89        10/11/2014
21       g89        10/15/2014

Running the aforementioned query should return:
(just for ease, loan 1 has 1 entry, 2 = 2, 51 = 1 etc)

[Loan]   [Countofdateissued]
a1                      1
b2                      2
c3                      3
d51                     1
e62                     2
f73                     3
g89                     9

[BikeToWork]

Thanks for the code Mr. Z. That's a good start and it gives me the number of dupes for each loan number, but they want to have the number of loans where there is no dupes, 1 dupe, 2 dupes, 3 dupes, etc, etc, ad nauseum. Could I just make another query based on the dupes query grouped by month and count of dupes?

[zmbd]

I'll go back and add the recordset... (^_^)

Now take a look at what the query returns.
It gives you the number of associated records in the table...

so to answer your subsequent question:
loans where there are no dupes, === Loan A1, D51
1 dupe, === b2, e62 (or two associated records each)
2 dupes, =?= c3, f73 (has three records - 1' + 2'')
3 dupes, ==== didn't make one for that
etc === g89 === which has 9 records... 1' + 8''

Notice, the query doesn't take into account the dates as identifiers as we're grouping on loan only, so for B2 the two different dates return 2 records count just the same as the two dates for E62 which are the same date... return 2 record counts.


Mind you, I don't know your dataset