Choose the field with more data.....?

I'll try and make this as simple as possible...I'm using a more simplified scenario because most people understand the basics of criminal charges but won't understand my real data! I'm also listing this all out because, even though the question I actually have only has to do with the last teeny bit, there might have been a way to avoid it altogether by doing something different earlier on!

I have 2 tables, ProtocolA and ProtocolB, as my main tables. Each contains info about each case handled by the police -- the person's name, etc. -- and the protocol used to handle the case (crappy example, but hey). There are NO details about the charges themselves (except in a "summary" field). There are 3 other relevant tables: ChargeOptions (a 2 column table with every charge mandated in the Criminal Code, each with an id), and PACharged and PBCharged (each is a list of charges charged in each case, i.e., there is 1-to-many relationship between ProtocolA and PACharged, where ProtocolA.ID = PACharged.ParentID and PACharged.Charge = ChargeOptions.ID ...and a bunch of other info too ... and PBCharged works the same way.)

From now on, the tables are called:
A (ProtocolA)
B (ProtocolB)
AC (PACharged)
BC (PBCharged)
CO (Charge Options)

In the end, I want a list of how many cases have 1 charge, how many have 2 charges, etc.

I have this query set up:

Quote:

---

SELECT AC.ParentID, Count(AC.ID) AS TotalCs
FROM AC
GROUP BY AC.ParentID.

---

This gives me each ParentID (i.e., the case ID#), and how many charges there were in that case.

I have the same query set up substituting BC for AC. Call these 2 queries QA and QB.

I also have 2 other queries that build on this (one for A, one for B):

Quote:

---

SELECT QA.TotalCs, Count(QA.TotalCs) AS [Number of Cases]
FROM QA
GROUP BY QA.TotalCs.

---

Call these queries QQA and QQB.

So now I have what I want: the first column says how many charges are associated with a case (they happen to be 1 .. 6, if that helps you visualize it), and the second says how many cases there are where there are that many charges associated with it.

HOWEVER, I'd like this to be in 1 table, where the first column has the number of associated charges (it's really a group...), and the second has the number of cases with that many charges USING PROTOCOL A, and the third with protocol B.

So, AT THE MOMENT, I have this instead of QQA and QQB:

Quote:

---

SELECT QA.TotalCs, Count(QA.TotalCs) AS [Number of ProtocolA Cases], Count(QB.TotalCs) AS [Number of ProtocolB Cases]
FROM QA LEFT JOIN QB ON QA.TotalCs = QB.TotalCs
GROUP BY QA.TotalCs.

---

And there's the catch: that bolded statement is using the groups from QA. But what if there's a case in QB that has 7 charges, and the most charges there are in a ProtocolA case is 6? It'll just drop that! But I can't just switch it to say QB.TotalCs, because the same scenario in reverse could occur.

Is there ANY way to fix this so that the query: (a) joins on that grouping, (b) displays that grouping once and only once, and (c) doesn't drop rows?

I have a similar problem with another query that's counting how many cases occurred per year by protocol. The first 2 queries, YQA and YQB are like:

Quote:

---

SELECT Year([DateCharged]) AS Year, Count(AC.ParentID) AS PACases
FROM CO INNER JOIN (A INNER JOIN AC ON A.ID=AC.ParentID) ON CO.ID=A.Particular
GROUP BY Year([DateCharged]).

---

But now I want to display it in the 3-column format described above, and have the same problem, only this time it's grouping by years so it's a lot more intuitive:

Quote:

---

SELECT YQA.Year, YQA.PACases, YQB.PBCases
FROM YQA LEFT JOIN YQB ON YQA.Year = YQB.Year.

---

I know this is a REALLY long explanation but I've been thinking it over for hours and getting more and more frustrated!