Plotting spectral data in Access

Copper Contributor

My table has the following structure:


FieldName        Value

nm380              1.23

nm390               2.67

nm400               3.87

nm410                3.45


nm710               1.45

nm720               2.45

nm730               0.67


Each record contains spectral data extending from 380 nanometer (nm380) to 730 nanometer (nm730). How can I plot this data in Access? I have experimented with many different approaches to no avail. I suppose I have to "massage" the data, somehow, so that they become in a form of X,Y data? So that, the "nm" column becomes X? The Value column is already in a form directly usable as Y. 


I was thinking, perhaps, to extract the "number" 380, 390, 400 and so on out of the field name? So that it could become a "number" and show up on the X axis? But I have found a function to do this in the Query Editor?

4 Replies



I meant to write "But I have NOT found a function to do this in the Query Editor?

@RogerBreton What exactly you mean "plotting"

If actual plotting is required...then

Either use a chart to host the data

or a Report/Form and the Line Control to make some kind of pseudo chart

I found ONE way to do it, and it does not work with the "new" Chart control. I had to use the "old" chart control for this approach to work. Essentially, the "trick" revolves around using "ALIAS" for each of the original field names. So, instead of "nm380", I used the following, in a query, and use THAT as the RowSource :


SELECT Mesures.ID, Mesures.nm380 AS 380, Mesures.nm390 AS 390, Mesures.nm400 AS 400, Mesures.nm410 AS 410, Mesures.nm420 AS 420, Mesures.nm430 AS 430, Mesures.nm440 AS 440, Mesures.nm450 AS 450, Mesures.nm460 AS 460, Mesures.nm470 AS 470, Mesures.nm480 AS 480, Mesures.nm490 AS 490, Mesures.nm500 AS 500, Mesures.nm510 AS 510, Mesures.nm520 AS 520, Mesures.nm530 AS 530, Mesures.nm540 AS 540, Mesures.nm550 AS 550, Mesures.nm560 AS 560, Mesures.nm570 AS 570, Mesures.nm580 AS 580, Mesures.nm590 AS 590, Mesures.nm600 AS 600, Mesures.nm610 AS 610, Mesures.nm620 AS 620, Mesures.nm630 AS 630, Mesures.nm640 AS 640, Mesures.nm650 AS 650, Mesures.nm660 AS 660, Mesures.nm670 AS 670, Mesures.nm680 AS 680, Mesures.nm690 AS 690, Mesures.nm700 AS 700, Mesures.nm710 AS 710, Mesures.nm720 AS 720, Mesures.nm730 AS 730
FROM Mesures;


This way, the horizontal X axis was displayed "correctly", in a "meaningful" way. It worked beautifully. 


I tried the same technique with the "new" chart control and it did not work? If there is a, "better" technique, anyone, please! I am opened to suggestions.

@tsgiannis Yes, of course, I used a "Chart" control.