Count by Date

[ladybug via AccessMonster.com]

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.

 

[PieterLinden via AccessMonster.com]

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

 

[ladybug via 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!

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

 

[John W. Vinson]

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.

 

[Duane Hookom]

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

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.