Not showing zero

  • Thread starter Thread starter instereo911 via AccessMonster.com
  • Start date Start date
I

instereo911 via AccessMonster.com

Good afternoon,

I am having trouble w/ a query. I set up a query w/ the following code:
SELECT [A-GATotalInventory].Date, [A-GATotalInventory].UnitAssociate, [A-
GATotalInventory].Manager, [A-GATotalInventory].Name, Count([A-
GATotalInventory].AgedDays) AS TotalInventory, Max([A-GATotalInventory].
AgedDays) AS OldestItem, Count([A-GATotalInventory].AgedDays) AS
CountOfAgedDays
FROM [A-GATotalInventory]
GROUP BY [A-GATotalInventory].Date, [A-GATotalInventory].UnitAssociate, [A-
GATotalInventory].Manager, [A-GATotalInventory].Name
HAVING (((Count([A-GATotalInventory].AgedDays))>30 Or (Count([A-
GATotalInventory].AgedDays)) Is Null));

My problem of the last statement. My goal is to group by Date, group by Unit
Associate, group by Manager, group by Name, Count the amount of ageddays and
then on the same query count the aged days that are over 30. My problem is
that some of the Unit Associate (names and managers) may not have any over 30
but may still have some of the countofaged AND THE QUERY won't show a zero if
there is none on it. Should I set up another query? What should I be doing. I
am not too expierenced w/ access so any helpful hints on what do would be
great!!!!


Thank you everyone
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If the value of the column AgedDays is a number representing the number
of "aged days" then you must use the SUM() function instead of the
COUNT() function.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR6wNE4echKqOuFEgEQJL7QCfW5SjU7A0nxQ9mHXED3quRtXdKfEAoLgF
Itr9XRFPCSetAdrVY1u8XW9i
=1H52
-----END PGP SIGNATURE-----
 
You might try

SELECT [A-GATotalInventory].Date
, [A-GATotalInventory].UnitAssociate
, [A-GATotalInventory].Manager
, [A-GATotalInventory].Name
, Abs(Sum([A-GATotalInventory].AgedDays <30)) AS LessThan30
, Abs(Sum([A-GATotalInventory].AgedDays >=30 AND AgedDays < 60)) AS Over30
, Abs(Sum([A-GATotalInventory].AgedDays >=60)) AS 60Plus
, Max([A-GATotalInventory].AgedDays) AS OldestItem
, Count([A-GATotalInventory].AgedDays) AS CountOfAgedDays
FROM [A-GATotalInventory]
GROUP BY [A-GATotalInventory].Date
, [A-GATotalInventory].UnitAssociate
, [A-GATotalInventory].Manager
, [A-GATotalInventory].Name


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top