Filling 1 column with another column's last entry? 2

[joebox8]

Hi... I want to auto-fill in one column in my table with the last value entered in another column in my table. How would I go about doing this?
thanks in advance

 

[MajP]

You can continue flailing with your approach but here is how to do this properly in a database. I figure I demonstrate.

A single table for all K observations.

TblDataPoints

ID lotNumber X1	   X2	 X3     X4     X5     X6     X7	    X8
1  12183GHR  12.46 12.43 13.45  12.35  13.02  12.9   11.99  12.21
3  12184GHR  12.2  12.55 13.66  12.77  13     12.11  11.22  12.33
4  12183GHR  12    12.01 13.2   12.21  13.3   12.31  11.4   12.41

Normalize (data base term not statistical term) the data. Put the datapoints into a single field (column). it is a cut and paste job.

qryNormalData

SELECT 
  tblDataPoints.dataPointID,
  tblDataPoints.lotNumber, 
  tblDataPoints.X1 AS XsubI
FROM tblDataPoints
UNION ALL
SELECT 
  tblDataPoints.dataPointID,
  tblDataPoints.lotNumber, 
  tblDataPoints.X2 AS XsubI
FROM tblDataPoints
UNION ALL
SELECT
  tblDataPoints.dataPointID, 
  tblDataPoints.lotNumber, 
  tblDataPoints.X3 AS XsubI
FROM tblDataPoints
UNION ALL
SELECT
  tblDataPoints.dataPointID,
  tblDataPoints.lotNumber, 
  tblDataPoints.X4 AS XsubI
FROM tblDataPoints
UNION ALL
SELECT
  tblDataPoints.dataPointID,
  tblDataPoints.lotNumber, 
  tblDataPoints.X5 AS XsubI
FROM tblDataPoints
UNION ALL
SELECT
  tblDataPoints.dataPointID,
  tblDataPoints.lotNumber, 
  tblDataPoints.X6 AS XsubI
FROM tblDataPoints
UNION ALL
SELECT
  tblDataPoints.dataPointID,
  tblDataPoints.lotNumber, 
  tblDataPoints.X7 AS XsubI
FROM tblDataPoints
UNION ALL SELECT
  tblDataPoints.dataPointID,
  tblDataPoints.lotNumber, 
  tblDataPoints.X8 AS XsubI
FROM tblDataPoints
ORDER BY 1;

output

ID      lotNumber  XsubI
1	12183GHR  13.02
1	12183GHR  12.21
1	12183GHR  12.43
1	12183GHR  11.99
1	12183GHR  13.45
1	12183GHR  12.9
1	12183GHR  12.35
1	12183GHR  12.46
3	12184GHR  12.2
3	12184GHR  12.55
3	12184GHR  13.66
3	12184GHR  12.77
3	12184GHR  13
3	12184GHR  12.11
3	12184GHR  11.22
3	12184GHR  12.33
4	12183GHR  12.41
4	12183GHR  13.3
4	12183GHR  13.2
4	12183GHR  12.31
4	12183GHR  12.01
4	12183GHR  11.4
4	12183GHR  12
4	12183GHR  12.21

get the sample stats

SELECT 
 qryNormalData.dataPointID, 
 Avg(qryNormalData.XsubI) AS SampleMean, 
 StDev(qryNormalData.XsubI) AS SampleSTDEV, 
 Min(qryNormalData.XsubI) AS SampleMin, 
 Max(qryNormalData.XsubI) AS SampleMax, 
 Count(qryNormalData.XSubI) AS SampleN, 
 [SampleMax]-[SampleMin] AS SampleRange
FROM qryNormalData
GROUP BY qryNormalData.dataPointID;

results

ID SampleMean SampleSTDEV SampleMin SampleMax SampleN SampleRange
1  12.60      0.48        11.99	    13.45     8       1.46
3  12.48      0.71        11.22     13.66     8       2.44
4  12.36      0.63        11.4      13.3      8       1.90

Now the process stats

SELECT 
 Avg(qrySampleStats.SampleMean) AS XdoubleBar, 
 Sum(qrySampleStats.SampleSTDEV) AS SumSampleSTDEV, 
 Count(qrySampleStats.dataPointID) AS K_observations, 
 [SumSampleSTDEV]/[K_observations] AS Sbar, 
 [Sbar]/(0.965*(8^0.5)) AS UnbiasedEstimatorStd, 
 [XdoubleBar]-3*[UnbiasedEstimatorStd] AS LCL, 
 [XdoubleBar]+3*[UnbiasedEstimatorStd] AS UCL
FROM qrySampleStats;

results

XdoubleBar SumSampleSTDEV K_observations Sbar UnbiasedEstimatorStd LCL   UCL
12.48      1.83	          3              0.61 0.22                 11.81 13.15

I would think really hard about doing it this way. I am done. A simple table structure, no code, a simple interface, dynamic results, and can handle thousands of samples. Your design is overlay complicated and not very flexible. You are foring a spread sheet approach into a datbase.

Also I can add another field to my datapoint table. This would be a boolean field to determine if the K observation is used to estimate my process stats. Currently all k observations are included. Normally only a set (20 minimum) that are known to occur within control are used.

 

[MajP]

I figure to plot this I need

SELECT 
 qrySampleStats.dataPointID, 
 qrySampleStats.SampleMean, 
 qryProcessStats.LCL, 
 qryProcessStats.UCL
FROM 
 qrySampleStats, 
 qryProcessStats;

which gives me this

dataPointID SampleMean LCL    UCL
1           12.60      11.81  13.15
2           12.48      11.81  13.15
3           12.36      11.81  13.15

But I am not sure how to plot that using the chart.

I can get the Xbars plotted, but can you make two horizontal lines for the LCL and UCL? I tried adding a series, but never got the correct answer.

 

[MajP]

Figured it out "display series in columns".

 

[MajP]

Here is a proper database solution:

http://www.4shared.com/document/s0K2ebPh/MajPControlLimits.html