If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Count by Date
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 |
#3
|
|||
|
|||
Count by Date
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 |
#4
|
|||
|
|||
Count by Date
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] |
#5
|
|||
|
|||
Count by Date
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] |
Thread Tools | |
Display Modes | |
|
|