My goal is to create a subform in Microsoft Access that has a chart which
plots scientific data / experimental data.
The numerical data is stored in a table, with each row corresponding to a
sample and each field corresponding to the sample's response at a certain
time (the times are stored in a separate table). If the data were transposed,
I would have to create a new field or a new table each for each sample added
to the database.
Because the numerical data is organized in rows rather than column series, I
cannot plot the data using Microsoft Chart. Chart (from my understanding)
requires the data to be stored in a single column series (across rows)
because it is limited to accessing 6 fields.
How can I plot my data? Are there any ways around the Chart limitation?
My original idea was to run some sort of query to return a dynaset of the
transposed data, then use this query as the Chart's source data. But there
is no transpose function, and I would need to specify the sample number for
the query from the parent form. The Microsoft knowledgebase has an article
which gives the code to transpose a table:
but the result is a new table. Is there anyway to make the result a
recordset or a dynaset and to pass that result to the query?
Anyother ideas of how to retrieve and transpose the data or plot the data?