Different counts on same query

[instereo911 via AccessMonster.com]

Good afternoon,

I need help on finding out how to run three different counts on one query. I
have a table set up to show Date
Within this query I have the following fields:

Date | Method | AgedDays ... taken from TotalInventoryTable

my goal is to say the following in one query.

Date Method CountofAge CountofAge>30
CountofAge>60
Date Calls ## ##
##
Date Corr ## ##
##

etc. etc.

All on one query... Is this possible.

Thank you everyone.

 

[KARL DEWEY]

Try this --
SELECT TotalInventoryTable.Method,
Sum(IIf(DateDiff("d",[Date],Date())<=30,1,0)) AS [30 days or less],
Sum(IIf(DateDiff("d",[Date],Date()) Between 31 And 59,1,0)) AS [More than 30
and less than 60 days], Sum(IIf(DateDiff("d",[Date],Date())>59,1,0)) AS [60
days or more]
FROM TotalInventoryTable
GROUP BY TotalInventoryTable.Method;

 

[instereo911 via AccessMonster.com]

Karl,

Sorry i didn't clarify,

what my table looks like is Date | Method | AgedDays(which is a number)

I want the query to say the following

Sort by date, sort by method and one column to have a total count of aged
days, another column to have a total count of aged days that are greater than
30 days, and another column to have a total count of aged days that is
greater than 60 days...

Does that make sense? I don't want any count with the date field - only the
aged field...

Thanks again Karl

Try this --
SELECT TotalInventoryTable.Method,
Sum(IIf(DateDiff("d",[Date],Date())<=30,1,0)) AS [30 days or less],
Sum(IIf(DateDiff("d",[Date],Date()) Between 31 And 59,1,0)) AS [More than 30
and less than 60 days], Sum(IIf(DateDiff("d",[Date],Date())>59,1,0)) AS [60
days or more]
FROM TotalInventoryTable
GROUP BY TotalInventoryTable.Method;

Good afternoon,

[quoted text clipped - 18 lines]

Thank you everyone.

 

[John Spencer]

Do you have multiple records per date and method? If not, I would think
that you are going to get a count of 1 or zero for each category of aged
days for each row of Date plus method. Adapting Karl Dewey's query to the
following

SELECT TotalInventoryTable.[Date]
, TotalInventoryTable.Method
, Sum(IIf(AgedDays<=30,1,0)) AS [30 days or less]
, Sum(IIf(AgedDays Between 31 And 59,1,0)) AS [More than 30 and less than 60
days]
, Sum(IIf(AgedDays >59,1,0)) AS [60 days or more]
, Count(AgedDays) as TotalCount
FROM TotalInventoryTable
GROUP BY TotalInventoryTable.Date, TotalInventoryTable.Method;


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

Karl,

Sorry i didn't clarify,

what my table looks like is Date | Method | AgedDays(which is a number)

I want the query to say the following

Sort by date, sort by method and one column to have a total count of aged
days, another column to have a total count of aged days that are greater
than
30 days, and another column to have a total count of aged days that is
greater than 60 days...

Does that make sense? I don't want any count with the date field - only
the
aged field...

Thanks again Karl

Try this --
SELECT TotalInventoryTable.Method,
Sum(IIf(DateDiff("d",[Date],Date())<=30,1,0)) AS [30 days or less],
Sum(IIf(DateDiff("d",[Date],Date()) Between 31 And 59,1,0)) AS [More than
30
and less than 60 days], Sum(IIf(DateDiff("d",[Date],Date())>59,1,0)) AS
[60
days or more]
FROM TotalInventoryTable
GROUP BY TotalInventoryTable.Method;

Good afternoon,

[quoted text clipped - 18 lines]

Thank you everyone.