Counting Entries

[adams.bria]

So I have a table where the key is Stuassn_ID

This combines a DC_ID with a Stuterm_ID and a Occ_ID.
The same DC_ID and Occ_ID can be assigned to any number of Stuterm_ID. However, each Stuterm_ID only has one DC_ID and Occ_ID.

Basically there are eight unique Occ_ID's. I want to count how many of each Occ_ID have been assigned to each DC_ID.

Let me know if anything needs clarification.

Thanks,

Brian

[Robeen]

Will this:

 
Select DC_ID, Count([Occ_ID])
From [TableName]
Group By DC_ID;

Work?

[adams.bria]

Close but no cigar. If I am reading this right, it is counting how many occ_id's have been assigned to a DC_ID. I need to know how many of each unique occ_id's have been assigned to a DC_ID. In other words, how many 1's, how many 2's, how many 3's... for each DC_ID. Also this is a historical table so I added the part about aid year = aid year. But this is where we are at. Anyone know what to change to get to where I would like to be?

SELECT stu_assn.DC_ID, Count(stu_assn.[Occ_ID]) AS CountOfOcc_ID
FROM (stu_assn INNER JOIN Dept_Cont ON stu_assn.DC_ID = Dept_Cont.DC_ID) INNER JOIN Local_Vars ON Dept_Cont.Aid_Year = Local_Vars.Aid_Year
WHERE (([dept_cont].[aid_year]=[local_vars].[aid_year]))
GROUP BY stu_assn.DC_ID;

[Robeen]

How about:

 
Select DC_ID, Occ_ID, Count(Occ_ID) AS NumberOfUniqueIDs
From [TableName]
Group By DC_ID, Occ_ID;

?

[Robeen]

To use your SQL:

 
SELECT stu_assn.DC_ID, stu_assn.[Occ_ID], Count(stu_assn.[Occ_ID]) AS CountOfOcc_ID
FROM (stu_assn INNER JOIN Dept_Cont ON stu_assn.DC_ID = Dept_Cont.DC_ID) INNER JOIN Local_Vars ON Dept_Cont.Aid_Year = Local_Vars.Aid_Year
WHERE (([dept_cont].[aid_year]=[local_vars].[aid_year]))
GROUP BY stu_assn.DC_ID, stu_assn.[Occ_ID];

[adams.bria]

Right on the money! Thanks, this forum is always amazing!

~Brian

[Robeen]

I've received a lot of help here myself so I'm happy to be able to help when I can.