Count by Date
in [Queries]
|
From: ladybug via AccessMonster.com on 28 Apr 2010 16:34 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/Forums.aspx/access-queries/201004/1
From: PieterLinden via AccessMonster.com on 28 Apr 2010 16:57 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
From: ladybug via AccessMonster.com on 28 Apr 2010 18:02 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
From: John W. Vinson on 28 Apr 2010 20:51 On Wed, 28 Apr 2010 22:02:31 GMT, "ladybug via AccessMonster.com" <u21071(a)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]
From: Duane Hookom on 28 Apr 2010 21:34 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" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message news:d0mht5t78drdfjl2hfdu54omisvq33b3mq(a)4ax.com... > On Wed, 28 Apr 2010 22:02:31 GMT, "ladybug via AccessMonster.com" > <u21071(a)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]
|
Pages: 1 Prev: split up one field into multiple fields Next: Query Criteria |