Count unique records in a table

[Guest]

I'm trying to write a query that will give users the total number of loans
debited during each month. I used a suggestion from a previous post that
suggests bringing the field name into a totals query twice, using Group By in
the first and Count in the second. This gets me a count of the number of
times each individual loan was debited, but not a count of unique loan
numbers debited. What I'm looking for is a way to count unique loan numbers
and group that count by month.

To illustrate, I have:
Date | Loan# | CountOfLoan#
June | 123 | 1
June | 456 | 7
June | 789 | 4

I need it to read:
Date | CountOfLoan#
June | 12

Any help would be appreciated.

 

[Michel Walsh]

Hi,

Build another TOTAL query that is based on the actual one. Drag the field
[date] in the grid, keep the proposed GROUP (remember to make that second
query a total query too), bring the Loan# field in the grid, change the
proposed GROUP to COUNT. That is it. You will get


June 3


since you have 3 different loan# (not 12). If you want the 12 (the sum,
not the count), then:
Remove the Loan# column from the grid of the first query, OR, in the
second query, bring CountOfLoan# in the grid, change the GROUP to SUM.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Just to add an example Michel explenation

Try this
SELECT TableName.[Date] , Sum(TableName.[CountOfLoan#]) AS SumOfCountOfLoan
FROM TableName
GROUP BY TableName.[Date]

Also if you have a field named date, you better change the field name
because it is a reserved name in Access, it will be fine as long that you
keep in square brackets

 

[Guest]

It worked beautifully! Thank you for understanding what I meant and not what
I typed. I wasn't looking for the sum (as my example unfortunately
indicated.)

Thanks again!

Hi,

Build another TOTAL query that is based on the actual one. Drag the field
[date] in the grid, keep the proposed GROUP (remember to make that second
query a total query too), bring the Loan# field in the grid, change the
proposed GROUP to COUNT. That is it. You will get


June 3


since you have 3 different loan# (not 12). If you want the 12 (the sum,
not the count), then:
Remove the Loan# column from the grid of the first query, OR, in the
second query, bring CountOfLoan# in the grid, change the GROUP to SUM.



Hoping it may help,
Vanderghast, Access MVP

I'm trying to write a query that will give users the total number of loans
debited during each month. I used a suggestion from a previous post that
suggests bringing the field name into a totals query twice, using Group By
in
the first and Count in the second. This gets me a count of the number of
times each individual loan was debited, but not a count of unique loan
numbers debited. What I'm looking for is a way to count unique loan
numbers
and group that count by month.

To illustrate, I have:
Date | Loan# | CountOfLoan#
June | 123 | 1
June | 456 | 7
June | 789 | 4

I need it to read:
Date | CountOfLoan#
June | 12

Any help would be appreciated.