database - MS-Access - unique records -

i have dataset lot of students, each own student number. each student has done multiple quizzes. have done quiz more once. can find out how many quizzes in total each student has completed i'd find out how many different unique quizzes each student has done. example, student may have completed 6 quizzes, have completed 3 unique quizzes (maybe did each unique quiz twice). how can in access?

it have tables names , column names answers can fined tuned exact setup easier.

for sake of example, assume have 1 table, called table1 , has columns student , quiz, quiz being name (unique identifier) of quiz taken.

an sql count of unique quizzes student took, this:

select a.student, count(a.quiz) quizcount (select distinct student, quiz table1) group a.student 

the (select distinct student, quiz table1) in case called sub-query, means query inside query. purpose of sub-query resultant table can count has unique quizzes student took accomplished keyword distinct.

the as a , as quizcount called aliasing. tables , columns can aliased , purposes such not having repeatedly type out long table name, increase readability of query, name resultant column of aggregate function such count, avg, sum, etc. since if no alias provided resultant column name of aggregate function exr1000.

lastly, column in result set not result of aggregate function, such a.student, needs put in group by statement.

edit: version using our provided table/column names:

select b.studentid, count(b.quizname) totalquizcount, c.uniquequizcount [combine v2] b inner join (select [a].studentid, count([a].quizname) uniquequizcount (select distinct studentid, quizname [combine v2] )  group a.studentid) c on b.studentid = c.studentid group b.studentid, uniquequizcount