Urgent! Help! Show 0 values in query or report

[Guest]

I have a query which shows 2 fields. It counts the how many of each status
has occured in the database. eg. Withdrawn 2, Issued permit 53, Sent back 5
etc. What I need is to have all of the statuses showing in a report (even
zero values) There are approximately 10 different statuses and I need them
to appear the same on all reports.

 

[Van T. Dinh]

Please post the relevant details of the Tables involved ...

If you have a look-up Table "tblStatus" and a Table "tblMain" that has a
ForeignKey Field "frg_StatusID" to link to "tblStatus", you can use the Left
Outer Join from "tblStatus" to "tblMain", something like:

***Untested***
SELECT S.StatusText, Count(M.*)
FROM tblStatus As S LEFT JOIN
tblMain As M ON S.StatusID = M.frg_StatusID
GROUP BY M.frg_StatusID

 

[Duane Hookom]

Hi Van. Long time, no see ;-)
JoHickey has the same thread going in the reports NG. Our replies are
basically the same.

 

[Van T. Dinh]

Has it been 2 days yet? My brain still trying to adjust to the local time.

 

[Guest]

Here is what I have:

SELECT Count([Current Database].F1) AS CountOfF1, [Current Database].Status
AS Status
FROM [EAAB Status] LEFT JOIN [Current Database] ON [EAAB Status].StatusEAAB
= [Current Database].Status
WHERE ((([Current Database].[Date Application Received]) Between #4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));

Returns:
CountOfF1 Status
28 Additional information required (EAAB)
9 Withdrawn

Problem is: Only shows up with 2 of the 12 possible statuses. I need all
12 to show up.

Example:
28 Additional information required (EAAB)
9 Withdrawn
0 Assigned
0 In Progress
0 Sent to Region
Etc.

 

[Duane Hookom]

See reply in other thread......

--
Duane Hookom
MS Access MVP
--

Here is what I have:

SELECT Count([Current Database].F1) AS CountOfF1, [Current
Database].Status
AS Status
FROM [EAAB Status] LEFT JOIN [Current Database] ON [EAAB
Status].StatusEAAB
= [Current Database].Status
WHERE ((([Current Database].[Date Application Received]) Between
#4/1/2005#
And Date()))
GROUP BY [Current Database].Status, [Current Database].Stage
HAVING ((([Current Database].Status)<>"Cancelled") AND (([Current
Database].Stage)="EAAB"));

Returns:
CountOfF1 Status
28 Additional information required (EAAB)
9 Withdrawn

Problem is: Only shows up with 2 of the 12 possible statuses. I need all
12 to show up.

Example:
28 Additional information required (EAAB)
9 Withdrawn
0 Assigned
0 In Progress
0 Sent to Region
Etc.

Has it been 2 days yet? My brain still trying to adjust to the local
time.