If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Urgent! Help! Show 0 values in query or report
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. |
#2
|
|||
|
|||
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 -- HTH Van T. Dinh MVP (Access) "JoHickey" wrote in message ... 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. |
#3
|
|||
|
|||
Hi Van. Long time, no see ;-)
JoHickey has the same thread going in the reports NG. Our replies are basically the same. -- Duane Hookom MS Access MVP "Van T. Dinh" wrote in message ... 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 -- HTH Van T. Dinh MVP (Access) "JoHickey" wrote in message ... 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. |
#4
|
|||
|
|||
Has it been 2 days yet? My brain still trying to adjust to the local time.
-- Cheers Van T. Dinh MVP (Access) "Duane Hookom" wrote in message ... Hi Van. Long time, no see ;-) JoHickey has the same thread going in the reports NG. Our replies are basically the same. -- Duane Hookom MS Access MVP |
#5
|
|||
|
|||
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. "Van T. Dinh" wrote: Has it been 2 days yet? My brain still trying to adjust to the local time. -- Cheers Van T. Dinh MVP (Access) "Duane Hookom" wrote in message ... Hi Van. Long time, no see ;-) JoHickey has the same thread going in the reports NG. Our replies are basically the same. -- Duane Hookom MS Access MVP |
#6
|
|||
|
|||
See reply in other thread......
-- Duane Hookom MS Access MVP -- "JoHickey" wrote in message ... 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. "Van T. Dinh" wrote: Has it been 2 days yet? My brain still trying to adjust to the local time. -- Cheers Van T. Dinh MVP (Access) "Duane Hookom" wrote in message ... Hi Van. Long time, no see ;-) JoHickey has the same thread going in the reports NG. Our replies are basically the same. -- Duane Hookom MS Access MVP |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Error when running Query, but not Report | Blaze | Running & Setting Up Queries | 29 | September 9th, 2005 05:40 AM |
To Sharkbyte and all: Calculate a total values in group level | Ally | General Discussion | 6 | June 13th, 2005 08:16 PM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
Display Parameter from Form on Report | sara | Setting Up & Running Reports | 10 | July 19th, 2004 04:54 PM |