Access: How to Unduplicated Count Number of Students in the Program
Example 1: In Table
below, count unduplicated number of students have loans in the program. Note:
NetLoan Column may have blank or 0 amount.
|
EnrolledLoan |
|||||
|
Program |
StudentName |
StuNum |
StartDate |
LDA |
NetLoan |
|
MBA |
CC |
1003 |
7/29/2019 |
10/11/2020 |
4333 |
|
MBA |
DD |
1004 |
7/29/2019 |
11/12/2020 |
|
|
MBA |
EE |
1005 |
3/11/2019 |
5/24/2020 |
0 |
|
MBA |
EE |
1005 |
10/8/2018 |
2/3/2019 |
5561 |
|
MBA |
FF |
1006 |
11/12/2018 |
3/13/2020 |
0 |
|
MBA |
GG |
1007 |
3/12/2018 |
5/19/2019 |
14844 |
|
MBA |
GG |
1007 |
2/4/2019 |
4/10/2020 |
16761 |
|
SRT |
AA |
1001 |
7/29/2019 |
5/24/2020 |
9401 |
|
SRT |
BB |
1002 |
3/11/2019 |
5/24/2020 |
12500 |
|
SRT |
BB |
1002 |
10/8/2018 |
2/3/2019 |
9401 |
Access or SQL Server SQL:
SELECT T.Program, COUNT(T.StuNum) AS NumHasLoan
FROM (SELECT DISTINCT Program, StuNum FROM EnrolledLoan WHERE NetLoan > 0)
AS T
GROUP BY T.Program
|
Result |
|
|
Program |
NumHasLoan |
|
MBA |
3 |
|
SRT |
2 |
Example 2: In Table below, count unduplicated number of students have loans in the program. Note: NetLoan column has no blank nor 0 amount (simple case).
|
ENLoan |
|||||
|
Program |
StudentName |
StuNum |
StartDate |
LDA |
NetLoan |
|
MBA |
CC |
1003 |
7/29/2019 |
10/11/2020 |
4333 |
|
MBA |
DD |
1004 |
7/29/2019 |
11/12/2020 |
1254 |
|
MBA |
EE |
1005 |
3/11/2019 |
5/24/2020 |
1545 |
|
MBA |
EE |
1005 |
10/8/2018 |
2/3/2019 |
5561 |
|
MBA |
FF |
1006 |
11/12/2018 |
3/13/2020 |
3215 |
|
MBA |
GG |
1007 |
3/12/2018 |
5/19/2019 |
14844 |
|
MBA |
GG |
1007 |
2/4/2019 |
4/10/2020 |
16761 |
|
SRT |
AA |
1001 |
7/29/2019 |
5/24/2020 |
9401 |
|
SRT |
BB |
1002 |
3/11/2019 |
5/24/2020 |
12500 |
|
SRT |
BB |
1002 |
10/8/2018 |
2/3/2019 |
9401 |
Access or SQL Server SQL:
SELECT T.Program, COUNT(T.StuNum) AS NumHasLoan
FROM (SELECT DISTINCT Program, StuNum FROM ENLoan) AS T
GROUP BY T.Program
|
Result |
|
|
Program |
NumHasLoan |
|
MBA |
5 |
|
SRT |
2 |
In SQL Server you may simple the code as below:
SELECT Program, COUNT(DISTINCT StuNum) AS NumHasLoan
FROM EnrolledLoan
GROUP BY Program