A series of data in column names? how to reformat

[proben930]

I have a table where someone types in a number of calls received for each hour. The column headings are like "hour0809, hour0910, etc.." I now want to graph these results, but one of the axes of data (the hour) is stored in the column headings. How can i make a query that will have column A as the hour and column B as the average calls? Thanks in advance!

 

[dching]

It appears that the data you have contains amounts for each hour of the day. This makes it very hard to transpose the data into 2 separate columns without having to write multiple queries to extract the data from each control.

What I would do is to create 1 make-table query for the first time slot (i.e. hour0809) and separate append queries for each hour thereafter.

On the make-table query, you would have 2 fields:
Exp1:8-9
hour0809

On the append-table query, you would have 2 fields:
Exp1:9-10
hour0910

Repeat for each subsequent hour needed.

Create a VB to run each of the queries. In the end, you will have a table that contains all of the average calls in one column and the related time of call in a separate column (Exp1).

You can also expand the queries to include when the call was logged as a selection parameter.

hth.

 

[EMP]

A Union query may also do the trick.

Select "0809" as [The Hour], Round((Select Avg(Nz([Hour0809])) from [tableName]),2) as Average from [tableName]
UNION Select "0910", Round((Select Avg(Nz([Hour0910])) from [tableName]),2) from [tableName]
UNION Select "1011", .... etc


If the data do not contain any null values, you can remove the Nz() function.

If the Round() function does not exist in your version of Access, you can, if you want, search the forums here for rounding numbers to two decimal places.