How do i create a report

[peterfseaman]

Firstly i am a novice when it comes to Access - I have a very simple query that contains - date, Person, Function, and duration (short time)
I want to create a report that will give me a summary of the data with the following columns
Person, Function, count, average time taken


could somebody please give me a sample report to set me on the right lines

[pbaldy]

The simplest is probably to create a totals query ("E" icon on the ribbon with the query in design view). It can be based on your query, or perhaps your query can be converted. Change the Group By to Count and Average on those 2 fields and see what it returns. Then base a report on that query.

[davegri]

First, duration cannot be data type TIME. Time is an instant in time, like 2:00 O'clock, not a number representing an accumulation of seconds or minutes or hours, etc.
An accumulation of time is just a number, like 100, representing 100 seconds or 100 hours, etc. It is the difference between two times.

Fix that in your query then highlight the query name in the navigation pane. In the ribbon click create, then click report.
Right click on the report menu and click design view. Click sorting and grouping to get your totals and averages.

[June7]

Agree with Dave. Cannot enter elapsed time value as a time. Data input as 2:15 cannot be calculated in a Sum or Avg function. If that is meant to be 2 hrs 15 minutes then it must be input as a quantity (not time) into a number field as 135 (minutes) or 2.25 (hours). Or do a calculation to convert 2:15 to 2.25.

Converting a decimal to hh:mm:ss structure (and the reverse) is a fairly common topic.

Then if you want to display the calculated result as hr:mm, that will require more calculation to format the number to a string.


So is your Duration field an input or a calculation? I don't see two date/time type fields to calculate with so I presume this is an input.

[peterfseaman]

June7 thanks for the reply - the duration is a calculated field (format date/time) which is end time (formated as short time) minus Start time (also formated short time). These three fields are in the same table

example
Start Time Endtime duration
08:00 08.45 00:45:00

When i create a crosstab query rows are persons name columns Name total actions then a column for a count of each transaction type. What i then want to get is an average time taken for each person for each transaction type. Maybe i dont need a crosstab query ???? HELP

[June7]

I would not bother with formatting Duration.

You don't NEED a crosstab but it can be a nice way to present calcs.

However, if you want Count as well as Avg, this would be 2 CROSSTAB queries. Then you can join those 2 in another query. Or might be possible with a single statement, review http://allenbrowne.com/ser-67.html#MultipleValues

Otherwise, build a normal aggregate (GROUP BY) query with Count and Avg, like:

SELECT [Name], [TransType], Count(*) AS CntTxn, Avg(DateDiff("n", [StartTime], [EndTime])) AS AvgDur FROM table GROUP BY [Name], [TransType];

Build a report for nice presentation of data. Do Sorting & Grouping and aggregate calcs in report. This will allow display of raw data as well as summary info.