Count by Date

I am trying to create a query that will capture how long a particular record
has been in a queue. I have another query called Status of Item. This query
has the following Fields: ItemId, Date Entered, and Queue.

Now I need my new query to group the Queues and count the # of ItemId's by
how many days from when it was entered. I need it to look something like
this:

Queue 10 10-20 21-30 31-40 40
Grand Total

Blue 4 6 0 0
2 12
Purple 8 2 1 1
4 16
Yellow 0 0 2 8
5 15

I just need the formulas in access that will count the entries from the date
it was entered (Date Entered) to todays date.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201004/1

ladybug wrote:
I am trying to create a query that will capture how long a particular record
has been in a queue. I have another query called Status of Item. This query
has the following Fields: ItemId, Date Entered, and Queue.

Now I need my new query to group the Queues and count the # of ItemId's by
how many days from when it was entered. I need it to look something like
this:

Queue 10 10-20 21-30 31-40 40
Grand Total

Blue 4 6 0 0
2 12
Purple 8 2 1 1
4 16
Yellow 0 0 2 8
5 15

I just need the formulas in access that will count the entries from the date
it was entered (Date Entered) to todays date.

Add a column to the Status of Item query that calculates the days in the
query using datediff("d",[date entered], Date())
then use a nested IIF to put those into groups.

IIF(DateDiff("d",[date entered], Date())40, "40",
IIF(DateDiff("d",[date entered], Date())30,"31-40",
IIF(DateDiff("d",[date entered], Date())20,"21-30",
IIF(DateDiff("d",[date entered], Date())=10,"10-20","10"))))

then you can pivot on the group

--
Message posted via http://www.accessmonster.com

I ended up going another route. I have the first column working:
Less than 10: (IIf([Days in Queue]10,""))
This gives a count for each queue that has been under 10 days

I cannot get the criteria to work for the # ranges after that. I want it to
return the count for anything that has been in queue for 10 to 20 days. I
know what I have below does not work. I need something for the =10-20 part.
Right now I get all zeros returned.
10-20: (IIf([Days in Queue]=10-20,""))

Thank you for your help!

PieterLinden wrote:
I am trying to create a query that will capture how long a particular record
has been in a queue. I have another query called Status of Item. This query
[quoted text clipped - 16 lines]
I just need the formulas in access that will count the entries from the date
it was entered (Date Entered) to todays date.

Add a column to the Status of Item query that calculates the days in the
query using datediff("d",[date entered], Date())
then use a nested IIF to put those into groups.

IIF(DateDiff("d",[date entered], Date())40, "40",
IIF(DateDiff("d",[date entered], Date())30,"31-40",
IIF(DateDiff("d",[date entered], Date())20,"21-30",
IIF(DateDiff("d",[date entered], Date())=10,"10-20","10"))))

then you can pivot on the group

--
Message posted via http://www.accessmonster.com

On Wed, 28 Apr 2010 22:02:31 GMT, "ladybug via AccessMonster.com" u21071@uwe
wrote:

I ended up going another route. I have the first column working:
Less than 10: (IIf([Days in Queue]10,""))
This gives a count for each queue that has been under 10 days

I cannot get the criteria to work for the # ranges after that.

Correct, because the route you chose to take is a dead end.

Try following Pieter's suggestion, which should work fine.
--

John W. Vinson [MVP]

I would assume the ranges will change and build a solution that doesn't make
someone go back and change the design of a query. The ranges belong in a
small "bucket" table where ranges of numbers go into specific buckets. When
the ranges change, you change your data and not an expression with four
IIf()s.


--
Duane Hookom
MS Access MVP


"John W. Vinson" wrote in message
...
On Wed, 28 Apr 2010 22:02:31 GMT, "ladybug via AccessMonster.com"
u21071@uwe
wrote:

I ended up going another route. I have the first column working:
Less than 10: (IIf([Days in Queue]10,""))
This gives a count for each queue that has been under 10 days

I cannot get the criteria to work for the # ranges after that.

Correct, because the route you chose to take is a dead end.

Try following Pieter's suggestion, which should work fine.
--

John W. Vinson [MVP]