Access 2000 Count Issue

[icenemesis]

Using Access 2000 on WinXP

Alright so here's how what I have is set up, well simplified version.

I have a Program Table, a Procedure Table and a Status Table.

Program - Table
ProgramID
ProgramName

Procedure - Table
ProcedureID
ProcedureName
ProgramID
StatusID

Status - Table
StatusID
StatusName

Each program has a status, and each procedure has a status there making it a one to many relationship from the status to the program table and procedure table. Each program can have many procedures making this a one to many also.

I need to group the procedures according to what program they are a part of. And count how many procedures are part of each status but the catch is. I need to show all the statuses(sp?) even when none of the procedures have that status.

The query should come out something like this:

Program1
Status1 5
Status2 0 << instance where there isnt actually a procedure in that grouping with this status, right now these aren't showing up at all
Status3 6

Right now its coming out as this:

Program1
Status1 5
Status3 6

IF that makes sense I have this

SELECT Procedure.ProgramID, Count(Procedure.Status) AS CountOfStatus, Procedure.Status
FROM Status INNER JOIN [Procedure] ON Status.StatusID = Procedure.Status
GROUP BY Procedure.ProgramID, Procedure.Status
HAVING (((Procedure.Status) Between 1 And 5))
ORDER BY Procedure.Status;

[Rabbit]

If I understand correctly, you need a second query.

SELECT ProgramID, Status, Nz(CountOfStatus, 0) As NewCount
FROM FirstQuery RIGHT JOIN Status ON FirstQuery.Status = Status.StatusID;