Filling 1 column with another column's last entry?
Filling 1 column with another column's last entry?
(OP)
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
thanks in advance
RE: Filling 1 column with another column's last entry?
RE: Filling 1 column with another column's last entry?
Or is there an easier way to do control limits? Hope you understand what i described!
RE: Filling 1 column with another column's last entry?
if using
CL = Mean
LCL = CL - 3*STD
UCL = CL + 3*STD
then assume table1
dblValue
5.5
2
7
8.8
2.2
0.7
3.3
10
The query
CODE
Table1.dblValue,
(SELECT Avg(dblValue)from table1) AS CL,
(SELECT Avg(dblValue)- 3 * stdev(dblValue) from table1) AS LCL,
(SELECT Avg(dblValue)+ 3 * stdev(dblValue) from table1) AS UCL
FROM
Table1;
CODE
5.5 4.94 -5.32 15.20
2 4.94 -5.32 15.20
7 4.94 -5.32 15.20
8.8 4.94 -5.32 15.20
2.2 4.94 -5.32 15.20
0.7 4.94 -5.32 15.20
3.3 4.94 -5.32 15.20
10 4.94 -5.32 15.20
RE: Filling 1 column with another column's last entry?
RE: Filling 1 column with another column's last entry?
Here is my code:
SELECT Table1.AVG, (SELECT Avg(AVG)from Table1) AS XCL, (SELECT (XBARCL)-(RBARCL)*0.373 from Table1) AS XLCL, (SELECT (XBARCL)+(RBARCL)*0.373 from Table1) AS XUCL
FROM Table1;
RE: Filling 1 column with another column's last entry?
Select avg([AVG])
In my example I had basically just the data points. I calculate dynamically the mean and standard dev from the datapoints using sql functions. I am a little confused from what you show. It appears you have an average, Xbar, and RBAR already calculated. But then it appears you are trying to take an average of the average. Do you want X Double Bar
So you can do what I did and dynamically calculate the Avg, Xbar, and Rbar just from the datapoints or if you have a seperate table with these values already calculated manually you can join them to the datapoints in a query.
If I have a table or query with a single record that has the avg, xbar, and rbar called table2, and my data points in table1 then I can join it all together with a cartesion product
Select DISTINCT
dblValue,
Average,
XBARCL - RBARCL * 0.373 as XLCL,
XBARCL + RBARCL * 0.373 as XUCL,
From
table1,
table2
So I am confused of what is in your table 1.
How and where are you calculating Xbar?
How and where are you calculating Rbar?
Do you need XDoubleBar?
I am also confused because you have a field called avg and one called XBARCL. What is the difference.
RE: Filling 1 column with another column's last entry?
Table1 is ;
Lot# X1 X2 X3 X4 X5 X6 X7 X8 Ave Stdev Avg-3 Avg+3 Range CL(x) UCL(x) LCL(x) CL(R) UCL(R) LCL(R)
the CL(x) is an average of a column of averages [Ave]; DAvg("AVG","Table1")
UCL(x)=([XBARCL])+([RBARCL])*0.373
LCL(x)=([XBARCL])-([RBARCL])*0.373
The CL(R) is an average of all the column of Range's;
DAvg("RANGE","Table1")
UCL(R)=([RBARCL])*1.864
LCL(R)=([RBARCL])*0.136
Sorry for this, thanks again!
RE: Filling 1 column with another column's last entry?
RE: Filling 1 column with another column's last entry?
To do this in Access and use the power of SQL you need to modify your table structure. You can import your existing data into this format with some actions queries. This also allows for a nice user interface and will handle variable lot sizes
tblData
LotNumber (lot number)
dblValue (your value)
So assume lot A, and B the data would go into a table
A 2
A 2.5
A 3.4
A 4.1
A 1
A 6
A 1.2
A 7
B 4
...
B 3.5
Using Sql and a group by query (grouping on lot) you can get
min lot
max lot
avg lot
stdev lot
You can also get the avg over all lots in a third query.
You can then join your tables linking on lot and be able to get all the calculations.
To verify
CL is the grand mean over all lots
Xbar is the lot mean
Rbar is Max lot - min lot
Range is what?
RE: Filling 1 column with another column's last entry?
Is there a way that i could use the latest value from the CL text box on my form to populate a column. I just want the latest control limits spread out over all the fields.
I hope you understand what i mean by all this!
RE: Filling 1 column with another column's last entry?
UPDATE LAC_BP_13010 SET LAC_BP_13010.XCL = (THE LAST ENTRY FROM THE XBARCL COLUMN)
RE: Filling 1 column with another column's last entry?
RE: Filling 1 column with another column's last entry?
tblValues
autoID
lot#
dblValue
Everything else would be calculated on the fly through queries and calculated controls.
However to do what you want there are several ways, depends on how you want to work this.
You seem to be coming from a spread sheet background and using concepts and terms that really do not have meaning in databases.
There is really no such thing as a column and row in database or a last record. There are fields that look like columns, and records that look like rows. There is no last record unless there is a sortable field in the table like a date time stamp or auto id. Without a sort order you are never guranteed to get out the "last record".
If your table structure is this
Lot# X1 X2 X3 X4 X5 X6 X7 X8 Ave Stdev Avg-3 Avg+3 Range CL(x) UCL(x) LCL(x) CL(R) UCL(R) LCL(R)
1) If you add an autonumber field then you can pull the last record by pulling some value from the max autonumber or date field. So if XBar is in this table (I do not see it) or another table. This can be done in a subquery
Select fldOne, fldTwo,...(Select Top 1 Xbar from someTable order by autoID DESC) as LastXBAR...
2) If that value is not in a table but on a form you can call it in a query as well
Select fldOne, fldTwo,...Forms!SubFormName!SomeControlName as LastXBAR...
3) If you want to save that last xbar in its own one column one record table you can do a cartesian join. This will add that record in tblLastXbar to every record in your main table
Select fldOne, fldTwo,...lastXbar from tblOne, tblLastXbar
4) If you want to persist the last xbar in you main table you can run an update query from your form
UPDATE someTable SET lastXbar = Forms!subFormName!SomeControlName
or if you pull that value from a table
UPDATE someTable SET lastXbar = (Select Top 1 Xbar from someTable order by autoID DESC)
RE: Filling 1 column with another column's last entry?
My graph has lot number on the X axis and average on the Y axis. It is a line graph and I want to show the LCL, CL and UCL as straight lines. I don't know how to do this except to get a column with the most up to date control limits populating an entire field giving a straight line for the graph which is then an accurate control limit.
I've attached a picture of the chart we want.
Thanks again for your help!
RE: Filling 1 column with another column's last entry?
A term "most up to date control limits" means nothing to me in database terms. Are these pulled from a form where you manually calculate them, are these calculated from the other records, are they stored in another table.
You say that a single record looks like this with these fields.
Lot#
X1
X2
X3
X4
X5
X6
X7
X8
Ave
Stdev
Avg-3
Avg+3
Range
CL(x)
UCL(x)
LCL(x)
CL(R)
UCL(R)
LCL(R)
In my mind everything below the datapoints X8 is a calculated value. I would calculate all of those dynamically as I said. But it sounds as if you have stored values in those fields already. Is that correct? Do all those fields have values already.
When you say the last control limits where would I pull those from? Is that the CL(R), UCL(R), LCL (R) from the last record entered?
I am also a little confused. I thought a single record was the measures for a specific lot. Each lot has 8 values. The graph has more than 8 points so I assume that is not graphing a single lot, but multiple.
Can you post your table (try 4shared.com)? There is probably a very easy solution but we are talking two lingos: you are talking spread sheet paradigm and I am thinking in a database paradigm. I just can not figure out your structure, and therefore how to bring the information together.
RE: Filling 1 column with another column's last entry?
Thanks again for your continuing help!
RE: Filling 1 column with another column's last entry?
There are many reasons for not saving calculated values. You can not ensure that your values are in fact "real time" based on additions, deletions, and edits of records. Further you are requiring a person triggered event procedure to do something that should just happen immediately. Like a spread sheet, you would want to see your values update as you change one of your x values and ensure they are 'real time'. You would not want to have to press a button to put hard-wired values into cells.
With that said, I will try to give you a solution for your current design. So you have a record with all the calculations already hardwired into fields. If I understand after you add a record (the "last Record") you want to do this
Does this mean you want to change the field CL(X) for every record equal to XBARCL (20.58)? Or do you want to dynamically add a column called XCL equal to 20.58 for all records? Or something else?
If you want a new column for all records equal to XBARCL of the last entered record you can build a query like
CODE
tblControlLimits.ID,
tblControlLimits.lotNumber,
tblControlLimits.X1,
.....
Other fields
(Select top 1 XBARCL from tblControlLimits order by ID DESC) AS LastCL
FROM tblControlLimits;
That method is dynamic and does not persist.
If you want to insert 20.58 into all records in the CL(X) column then
you have to do that in two steps.
Build a simple query to get the last entered XBARCL. Call it
qryLastCL
CODE
tblControlLimits.XBARCL
FROM
tblControlLimits
ORDER BY
tblControlLimits.ID DESC;
CODE
tblControlLimits,
qryLastCL
SET
tblControlLimits.[CL(X)] = [qryLastCL].[xbarcl];
Is that what you want?
RE: Filling 1 column with another column's last entry?
RE: Filling 1 column with another column's last entry?
I am pretty confident all of this can be done in a single table that holds nothing but data points and lot numbers and everything else is calculated dynamically.
If you are hard fast in sticking with your table structure then running queries on multiple tables is done in code.
If the tables have consistent naming convention you could loop them.
assume the tables are tblCL1, tblCL2, .. tblCLN
dim tblName as string
dim strSql as string
dim I as integer
for I = 1 to N ' Where N is your last number
tblName = "tblCL" & I
strSql = "UPDATE "& tblName & ", qryLastCL SET " & tblName & ".[CL(X)] = [qryLastCL].[xbarcl]"
currentDB.execute strSql
next I
If the tables do not have consistent names then have to come up with another method to loop them.
RE: Filling 1 column with another column's last entry?
A single table for all K observations.
TblDataPoints
CODE
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
CODE
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
CODE
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
CODE
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
CODE
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
CODE
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;
CODE
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.
RE: Filling 1 column with another column's last entry?
CODE
SELECT
qrySampleStats.dataPointID,
qrySampleStats.SampleMean,
qryProcessStats.LCL,
qryProcessStats.UCL
FROM
qrySampleStats,
qryProcessStats;
which gives me this
CODE
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.
RE: Filling 1 column with another column's last entry?
RE: Filling 1 column with another column's last entry?
htt