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]

There is no purpose in doing this in a database, and violates standard datbase design. Why do you think you would need to do this? What is the purpose.

 

[joebox8]

I want to create a chart with control limits displayed. But access reports dont let you do this. So i want to get the latest control limits UCL, CL and LCL and populate a column for each one (each box in the column will be the same, based on the most up to date control limits) then i will create a report with a line graph, the graph will plot my information and the UCL, CL and LCL will show up as 3 straight horizontal lines (because all points are the same value).

Or is there an easier way to do control limits? Hope you understand what i described!

 

[MajP]

not sure of your formula for CL, LCL, UCL
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

SELECT 
 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;

Provides

dblValue  CL    LCL    UCL
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

 

[joebox8]

Thats great MajP Thanks!

 

[joebox8]

Didnt get to try that until now. I'm using a different formula for the control limits and replaced yours with it. The query doesnt seem to work tho. When i try run it it pops up this message box: At most one record can be returned by this subquery.

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;

 

[MajP]

On thing if you have a field name with spaces or a reserved word you need to put in []. You have a field named AVG which is a name of a sql function. You have to then do
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.

 

[joebox8]

This is the layout more or less...The formula are taken from existing excel sheets that I have to convert to access and make a UI.
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!

 

[joebox8]

Sorry Ave should be AVG

 

[MajP]

I am not sure if I quite have it. Not sure which is calculated per lot and what is calculated over all lots.

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?

 

[joebox8]

I have my UCL LCL and CL calculated, they are displayed on my form. When my form calculates the control limits it saves them to each record so when i look up a certain record i can see what the control limits were when that record was entered to the table. What i want (for my graph) is to take the latest control limits and fill an entire column (in the same table or a different table or a query) so that i can use this column in the graph to show a straight line for that control limit.

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!

 

[joebox8]

Ok what I think I want basically is something like this query (replacing the contents of the brackets with the right code obviously):

UPDATE LAC_BP_13010 SET LAC_BP_13010.XCL = (THE LAST ENTRY FROM THE XBARCL COLUMN)

 

[joebox8]

Could i identify the last entry with the ID column? The last entry's ID number would be the highest number in the column so the query could Update XCL column with XBARCL entry with the highest ID number. How would I do that?

 

[MajP]

There are several ways to do what you want, but as I said I would not do any of these. You seem to want to apply a spread sheet structure to a database. If it was me the only values stored in the a table are the lot # and the recorded value. It would be one simple table

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)

 

[joebox8]

I don't really follow. All i know is that i need 3 straight lines across the graph (UCL, CL and LCL) my knowledge of access isnt sufficient to follow what your saying.

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!

 

[MajP]

I understand what you are trying to do, but I am totally confused of what information you currently have and what still needs to be calculated. So I have no idea where you want to pull the information from or calculate the information You need to be very specific with your terminology and names.

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.

 

[joebox8]

Basically we have a form. All 8 X numbers are entered into 8 different bound text boxes on the form, each Lot has 8 x numbers. There is a calculate button and when the user clicks on it it calculates the Average(of the 8 x numbers, likewise for the other calculations), Standard Deviation, Range, Average + 3(Standard Dev), Average -3(Standard Dev), Pass/Fail (if AVG-3STDEV is <17.4), XBAR CL, XBAR UCL, XBAR LCL, RBAR CL, RBAR UCL, RBAR LCL. Each of these results is calculated using formulae in a macro and the result is displayed in a labeled text box for each result. So we have all of our calculations done, now we need to be able to display our information in an accurate graph with our control limits. Each text box is bound to a field and sends the data when a save record button is pressed. We have an ID field on our table which is an autonumber field. You have our table structure correct in your above post. I am attaching a pic of the form.

Thanks again for your continuing help!

 

[MajP]

I will try to provide you a solution for your current design, but your design makes no sense to me. In a database like a spreadsheet you normally do not run code to do a calculation and then save those values. You dynamically calculate your values for display. What you are doing in my mind would be equivalent to having a spread sheet where your fields do not have formulas in them. Instead you force the user to click a button and code runs sticking values in fields. That would not make much sense in a spread sheet, same for a database

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

Update XCL column with XBARCL entry with the highest ID number.

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

SELECT 
 tblControlLimits.ID, 
 tblControlLimits.lotNumber, 
 tblControlLimits.X1,
 .....
 Other fields
 (Select top 1 XBARCL from tblControlLimits order by ID DESC) AS LastCL
FROM tblControlLimits;

This adds a column called LastCL 20.58 to all records in a query.
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

SELECT TOP 1 
 tblControlLimits.XBARCL
FROM 
 tblControlLimits
ORDER BY 
 tblControlLimits.ID DESC;

Then call that query from another query

  UPDATE 
    tblControlLimits, 
    qryLastCL 
  SET 
    tblControlLimits.[CL(X)] = [qryLastCL].[xbarcl];

Is that what you want?

 

[joebox8]

Thanks, that works! Now my only problem is the fact that I need to do this with a lot of tables and I'm also getting inaccurate results on my charts because of duplicate lot no's.

 

[MajP]

Any chance you can post your database? I use 4shared.com. Now you are really putting band-aids on top of band-aids. So it is a snow ball effect where the work arounds are become increasingly more complicated. I think your tables, interface, and calculations can be done far simpler and I will try to demonstrate.

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.