Show how many times something has been assigned to someone, and who is next on list

[sephiroth2906]

When I started this project, it never occurred to me that THIS is where I would have a problem, but now that I am here, I realize I have no idea how to accomplish this.

I have a database that is tracking Judge assignments on cases. What I would like to have happen is have a query or report that shows two things, the number of cases assigned to a certain Judge, and which Judge is next up for case assignment, based upon the number of assignments and the date they were assigned.

I have one table with the Judge's names with their Bar numbers as key, and then another table that references that one for the Judges from a dropdown box and the case information, for example

Judge Case_Number Defendant Date_Assigned
Jones 11111 Billy Thompson 1/1/11


Smith 11112 Craig Ferguson 1/2/11
Tyler 11113 Matt Borsch 1/3/11
Jones 11114 Dennis Mitchell 1/4/11

I really appreciate any help I may receive. Please let me know if you need any more information.

[pbaldy]

Well, this should count them:

SELECT Judge, Count(*) AS HowMany
FROM TableName
GROUP BY Judge

[sephiroth2906]

Well, seeing as how my SQL is about as good as my astrophysics, that explains why that didn't come to me. Thanks!

By adding an additional date sort, it appears I have what I want from a query. This may come to me, but I may as well ask, can I get the first item in the query, the Judge's name, to appear on a report all by itself?

Thanks again!

[pbaldy]

You should be able to use

SELECT TOP 1 ...

which with the sorting should return the first item. Might return more than one if there's a tie though.

[sephiroth2906]

I decided to go with the list and added it to the form. I really appreciate the help! One of these days I need to get a good SQL book. Any suggestions in that arena?

[pbaldy]

Having gone to the "school of hard knocks", I never read a book. That probably explains the big gaps in my knowledge.