Need to transpose query results like excel

Topic

New Reply

I have a summary query that has five fields each with 1 result
field1 field2 field3 field4 field5
100 200 250 400 150

it need these results to be transposed so as to give two fileds

NameOfField Results

Field1 100
Field2 200
Field3 250
Filed4 400
Field5 150

I need this in order to make a pie chart

Reply | Quote

Replies

Sorry, I cannot think of a way to do this in a query. Unless others can suggest a better way, you’re looking at a simple bit of code.

1. Identify a recordset to hold the result (e.g. a table, remember to empty it before starting)
2. Open a recordset to show the source data.
3. Go to the first (only?) record of your source data, and the first field.
4. Read the value.
5. Append the field name and the value to the temporary table (remember to update if using a recordset, but I’d use an Append SQL statement)
6. Move to the second field, and repeat steps 4 and 5. Continue until you run out of fields.

Close your recordset(s), and open the pie chart which is based on the temporary table.

Hope this helps,

Jules

Reply | Quote

Unless I’m missing something obscure, you can create a pie chart from data formated like your summary query results. What version of Office are you using, and are you creating the pie chart in Access or Excel?

Reply | Quote

i am using access 2003, I assumed the pie chart adresses only one field

Reply | Quote