Re: Help with chart


Thanks Bob,
See inline:

Some general responses to your info.
1) Do you mean starting with the current month and ending 11
months earlier? It's hard to predict the future.

:-) You're right, that was what I meant.

2) if there are 100 records in January the average will be a
single number, same as for 20 rows in February.

True, but the number of records involved per month would help to interpret
the figures. For instance, it could indicate that a month with a great less
number of requests, has a lower duration average. If the number of requests
for 2 particular months are the same but there is a big difference between
the average duration times of those months, that would be something to look
into.

Any idea on how to incorporate that into the chart?

Now some practtical information.
Create a query with year(DateIn)*100+month(datein)as a column.
and datediff("d",datein,nz(dateout,date()) as a second column.
the third column would contain any field which would have non-
blanks. Turn this into a Summary query, group by the first
field, average the second, and count the third.
You can also add a criteria to the first column with the
expression > (year(date()-1)*100 +month(date()

This will give you 12 rows, with the period, the average

Great! thanks,
john


"Bob Quintal" <rquintal@xxxxxxxxxxxxx> schreef in bericht
news:Xns9949CEBB8AC43BQuintal@xxxxxxxxxxxxxxxx
"John" <jo@xxxxxx> wrote in
news:f4ckau$2v5f$1@xxxxxxxxxxxxxxxxxxx:

I have a table with amongst others 2 fields: DateIn and
DateOut. The idea is to make a chart that shows the average
number of days it takes for a request to be answered. I'm
planning to make a linechart with on the x-axis the months
(starting with the current month and ending 11 months later)
and on the y-axis the average number of days (DateOut minus
DateIn). So the chart will show the average number of days per
month.

How would I go about this?
Which outputfields do I need in my table/query for my chart?
It's possible that there are 100 records for January and just
20 for February. Is there a way to make that visible in the
same chart?

Thanks in advance for any help.
john

Some general responses to your info.
1) Do you mean starting with the current month and ending 11
months earlier? It's hard to predict the future.
2) if there are 100 records in January the average will be a
single number, same as for 20 rows in February.

Now some practtical information.
Create a query with year(DateIn)*100+month(datein)as a column.
and datediff("d",datein,nz(dateout,date()) as a second column.
the third column would contain any field which would have non-
blanks. Turn this into a Summary query, group by the first
field, average the second, and count the third.
You can also add a criteria to the first column with the
expression > (year(date()-1)*100 +month(date()

This will give you 12 rows, with the period, the average
duration and the number of rows making that average.

Plot the period on the X axis, and either or both other columns
on the Y azis.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com



.

Relevant Pages

  • Re: Finding Max of a DB field and then return whole record grouping on one column
    ... I am trying to group on the first column and then find the Max of the second column, and where this is equal, to use the record which has the Max of the third column. ... can you ensure the order of the records is consistent? ...
    (microsoft.public.access.queries)
  • Re: please review my code
    ... thus spoke david: ... (setf (first column) ... (setf (second column) ... (setf (third column) ...
    (comp.lang.lisp)
  • Re: please review my code
    ... Stanisâaw Halik writes: ... (setf (first column) ... (setf (second column) ... (setf (third column) ...
    (comp.lang.lisp)
  • Re: please review my code
    ... pjb@xxxxxxxxxxxxxxxxx (Pascal J. Bourguignon) writes: ... (setf (first column) ... (setf (second column) ... (setf (third column) ...
    (comp.lang.lisp)
  • Re: How can I graph a series of launch dates in excel?
    ... and use the wizard to create a Line Chart. ... If you want your series to go vertically, put the dummy data in the ... first column and the dates in the second column, ...
    (microsoft.public.excel.charting)