Graphing values from more than one table

[QuantumLeap]

Is there a way I can use series from different tables in a graph? I have
measurement data and the date it was taken in one chart, and the upper and
lower limits for the data in two different tables. There is no date
associated with the limits. I would like to have three series, one each for
the upper limit, the lower limit, and the measured value.

If this cannot be done this way, is there any other way I can go about
putting limits on this graph without adding columns to the measurment table
for the upper and lower limits of each measurement?

Thank you

 

[Duane Hookom]

There must be some field that associates the two tables. You don't need to
associate the limits to the date if the limits don't change by date.

 

[QuantumLeap]

How can I make a field to associate the tables where there isn't one now?
Their only relationship now is the column heading. For example, there is a
left corner USL and LSL for the left corner measurement.

Thank you

 

[Duane Hookom]

I assume your limits table has USL and LSL as fields. Isn't there also a
field that links to a field storing "Left Corner"? For instance

tblMeasures
=============
MeasureDate date/time of measure
ItemID value that suggests "left corner"
MeasureValue numeric measurement

tblLimits
============
ItemID value that suggests "left corner"
USL Upper Limit
LSL Lower Limit

The ItemID would be the link.

 

[QuantumLeap]

My tables are currently set up in the format:

Date | Measurement A | Measurement B |
Measurement C
_____________________________________________________________________
date measurement at point A measurement at point B measurement at
point C

The only descriptions of where the measurement is taken (which would could
be used to connect the tables as you suggested) are currently column
headings. This works well when entering data, but it seems like I will have
to change the format of the tables if I am going to get the charts I want.

 

[Duane Hookom]

I would normalize the tables. You also haven't suggested you have a table
that lists USL and LSL for measurements.

 

[QuantumLeap]

My tables for USL and LSL also have the measurement name as headings, but
they do not have dates.

What do you mean I should normalize the tables?

Thank you

 

[Duane Hookom]

I would not have MeasurementA, MeasurentB,.... When I have created a
solution like this in the past I had tables like:

tblInspection
==============
insID autonumber primary key
insItemID item inspected
insDate date/time of inspection

tblInspectionDetails
=============
indID autonumber primary key
indinsID link to tblInspection.insID
indPoint what attribute was measured
indValue value of measurement

Rather than different fields for different points, you have different
records for different points.

 

[QuantumLeap]

So is there no way to make the charts like I want them with my tables set up
as they are now? I hesitate to alter them because I have 33 different
measurement points and need to make data entry as easy as possible for the
operators. They like the current format where they can just tab along and
type in the values rather than selecting the measurement type from a menu.

Thanks

 

[Duane Hookom]

Still no comment from you regarding a table of USL and LSL? If these are
values you expect to plot, they must be stored somewhere in your systme and
related to the actual measurements in some manner.

You can take your wide, un-normalized table and make it normalized by using
a union query.

 

[QuantumLeap]

I realized that I was unclear in my previous comment about the USL and LSL
tables. The only way they are related to the actual measurements is that
each field (column) is the name/location of a measurement in both the limit
and the measurement tables. The rows correspond to the date the measurement
was taken in the measurement table. The cells in the USL and LSL tables just
have the limits, with no specific meaning associated with the rows.

 

[Duane Hookom]

I would need to see some actual sample records with the significant fields.
Then a description of how you would expect this to graph.

 

[QuantumLeap]

How could I provide you with some sample records? I do not seem to be able
to paste an image here.

Thank you

 

[Duane Hookom]

Maybe take the time to type some samples in a format that can easily
identify tables, fields, values...

 

[QuantumLeap]

A portion of the measurement table:

Date Series Measurement A Measurent B Measurement C Measurement D
2/1/2006 2 0.034 -0.037 -0.635 0.01
2/1/2006 3 0.031 -0.011 -0.629 0.039
2/1/2006 4 0.024 -0.034 -0.662 0.028
2/1/2006 5 0.018 -0.017 -0.639 -0.024
2/1/2006 6 0.039 -0.01 -0.618 0.061
2/1/2006 7 0.022 -0.032 -0.643 -0.021
2/2/2006 2 0.061 -0.044 -0.634 0.053
2/2/2006 3 0.052 -0.016 -0.629 0.036
2/2/2006 4 0.008 -0.04 -0.649 0.035
2/2/2006 5 0.065 -0.011 -0.626 -0.019

A portion of the USL table:
Series Measurement A Measurement B Measurement C Measurement D
USL 0.06 0.09 0.08 0.08
USL 0.06 0.09 0.08 0.08
USL 0.06 0.09 0.08 0.08
USL 0.06 0.09 0.08 0.08
USL 0.06 0.09 0.08 0.08
USL 0.06 0.09 0.08 0.08
USL 0.06 0.09 0.08 0.08

A portion of the LSL table:
Series Measurement A Measurement B Measurement C Measurement D
LSL -0.06 -0.09 -0.08 -0.08
LSL -0.06 -0.09
-0.08 -0.08
LSL -0.06 -0.09
-0.08 -0.08
LSL -0.06 -0.09 -0.08 -0.08
LSL -0.06 -0.09 -0.08 -0.08
LSL -0.06 -0.09 -0.08 -0.08
LSL -0.06 -0.09 -0.08 -0.08
LSL -0.06 -0.09 -0.08 -0.08


The actual tables have more measurements.

For my graph, I would like the data to be plotted with the date to be the
x-coordinant, the measurement value to be the y-x-coordinant, with different
series at shown in the series column.

I want to plot my USL and LSL as series even though they do not have dates
associated with them because their value stays the same.

Thanks

 

[Duane Hookom]

Am I correct in assuming these records are for the same "part". Is there a
reason why all of the USL records are the same and all the LSL records are
the same?

Two options (the first normalizes, the second doesn't)

The first thing I would do is to normalize your measurement records with a
union queries:
===quniMeasurements=======
SELECT Date, Series, "A" as Measurement, [Measurement A] as MeasureVal
FROM tblMeasurements
UNION ALL
SELECT Date, Series, "B", [Measurement B]
FROM tblMeasurements
UNION ALL
SELECT Date, Series, "C", [Measurement C]
FROM tblMeasurements
UNION ALL
SELECT Date, Series, "D", [Measurement D]
FROM tblMeasurements;

===quniLimits==============
SELECT "A" AS Measurement, tblLSL.[Measurement A] AS NewLSL,
tblUSL.[Measurement A] AS NewUSL
FROM tblUSL, tblLSL
UNION
SELECT "B", tblLSL.[Measurement B], tblUSL.[Measurement B]
FROM tblUSL, tblLSL
UNION
SELECT "C", tblLSL.[Measurement C], tblUSL.[Measurement C]
FROM tblUSL, tblLSL
UNION
SELECT "D", tblLSL.[Measurement D], tblUSL.[Measurement D]
FROM tblUSL, tblLSL;

Then create your query that you should be able to graph:
===qselForGraph===========
SELECT quniMeasurements.Measurement,
quniMeasurements.Date, quniMeasurements.Series,
quniMeasurements.MeasureVal, quniLimits.NewLSL,
quniLimits.NewUSL
FROM quniLimits INNER JOIN quniMeasurements
ON quniLimits.Measurement = quniMeasurements.Measurement
ORDER BY quniMeasurements.Measurement,
quniMeasurements.Date, quniMeasurements.Series;

SECOND OPTION
Create a query with this SQL view
===qgrpToGraph==========
SELECT tblMeasurements.Date, tblMeasurements.Series,
tblMeasurements.[Measurement A], tblMeasurements.[Measurement B],
tblMeasurements.[Measurement C], tblMeasurements.[Measurement D],
tblLSL.[Measurement A] AS ALSL, tblLSL.[Measurement B] AS BLSL,
tblLSL.[Measurement C] AS CLSL, tblLSL.[Measurement D] AS DLSL,
tblUSL.[Measurement A] AS AUSL, tblUSL.[Measurement B] AS BUSL,
tblUSL.[Measurement C] AS CUSL, tblUSL.[Measurement D] AS DUSL
FROM tblMeasurements, tblUSL, tblLSL
GROUP BY tblMeasurements.Date, tblMeasurements.Series,
tblMeasurements.[Measurement A], tblMeasurements.[Measurement B],
tblMeasurements.[Measurement C], tblMeasurements.[Measurement D],
tblLSL.[Measurement A], tblLSL.[Measurement B], tblLSL.[Measurement C],
tblLSL.[Measurement D], tblUSL.[Measurement A], tblUSL.[Measurement B],
tblUSL.[Measurement C], tblUSL.[Measurement D];