Zero values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that returns the sum of reopened projects by month. Some months don't have reopened projects and are not listed in the query. I am importing this data into excel and would like to have all months accounted for so I don't have to manually add information. In researching, I am assuming the solution will be be to join a table with months to the query (It doesn't appear the Nz or DCount functions will work). I have not been able to join this successfully. Can walk me through this or offer another solution

code for reopened query -
SELECT QRY_WAV_MTH_FF_TELEPHONE.New_Reopen_Date, Sum(QRY_WAV_MTH_FF_TELEPHONE.[Count_Re-Open]) AS [SumOfCount_Re-Open
FROM QRY_WAV_MTH_FF_TELEPHON
GROUP BY QRY_WAV_MTH_FF_TELEPHONE.New_Reopen_Dat
HAVING (((QRY_WAV_MTH_FF_TELEPHONE.New_Reopen_Date)<>""))
 
Save this as a named query. Create new query and join it back to the
original table (the one that has All of the projects) with an OUTER join.
This will show all of the projects in the projects table and only filling in
those that have re-opened projects.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

McGee said:
I have a query that returns the sum of reopened projects by month. Some
Click to expand...
months don't have reopened projects and are not listed in the query. I am
importing this data into excel and would like to have all months accounted
for so I don't have to manually add information. In researching, I am
assuming the solution will be be to join a table with months to the query
(It doesn't appear the Nz or DCount functions will work). I have not been
able to join this successfully. Can walk me through this or offer another
solution?
code for reopened query -
SELECT QRY_WAV_MTH_FF_TELEPHONE.New_Reopen_Date,
Click to expand...
Sum(QRY_WAV_MTH_FF_TELEPHONE.[Count_Re-Open]) AS [SumOfCount_Re-Open]
 
This doesn't work because the original data feed has fields for Initial Date & Reopened date. There are some months with no reopened and so it leaves these months blank.
 
Back
Top