help with date groups

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need help with dates. I have two dates IssueEntry and IssueResolved, I need to find a way to group the records and get a count by issues resolved within 7 days, 7-14 days, etc. and then find out the percentage of the each group from the entire range(say first quarter) and put it in a report...preferably as a chart. I cant figure out ow to query so I can get the data I need to add to the chart...If anyone has any Ideas?
 
you could work with the datediff & count() functions
see access help

like

select count(*) as amount from
....
group by datediff(d,day1,day2)

then you only need the total amount of records in a second
query

the result can be another query to feed your chart

like

select table1.amount/table2.totalamount
from table 1 , table 2

-----Original Message-----
I need help with dates. I have two dates IssueEntry and
Click to expand...
IssueResolved, I need to find a way to group the records
and get a count by issues resolved within 7 days, 7-14
days, etc. and then find out the percentage of the each
group from the entire range(say first quarter) and put it
in a report...preferably as a chart. I cant figure out ow
to query so I can get the data I need to add to the
chart...If anyone has any Ideas?
 
Zman88a,

You can make a query based on your table, and enter a calculated field
such as...
ResolutionWeek: ([IssueResolved]-[IssueEntry])\7+1
Then, make it a Totals Query (select Totals from the View menu of the
query design window), leave Group By in the Totals row of this
calculated field, and include also in the query design grid the primary
key field from the table, and put Count if the Totals row of the grid.
This will give you the number of "issues" resolved for each period of
multiples of 7 days.
To get these figures as a percentage, onw way to do this would be via a
domain aggregate function. In other words, make another query based on
this first query, and make a calculated field like this...
ProportionResolved: [CountOfID]/DCount("*","NameOfYourTable")
This query can then be the basis of your graph.
 
Back
Top