Update field value with same value in same field on previous row

[Beth]

Hello,

I have a table with 3 columns where the data can look like this:

Column A Column B Column C
blah blah 123
abc
test test
blah blah 456
test2test2 def
ghi

I need to update column C with the value in Column B when Column A = "blah
blah". If it does not equal "blah blah", then I need to populate column C
with the value in Column B the last time Column A = "blah blah". So, in the
above example, I'd need it to look like this:
Column A Column B Column C
blah blah 123 123
abc 123
test test 123
blah blah 456 456
test2test2 def 456
ghi 456


In Excel, I can do it with this formula: =if(column A="blah blah", +column
B, +column C one above)
The "+column C one row above" is the same field (column) in the row above.

I have 500k records which Excel won't accomodate. I've talked to my SQL
gurus who can't figure out how to do it. Any suggestions?

THANK YOU!

 

[Gary Walter]

In Excel, the grid establishes ordinality,
i.e., one row is "above" another row.

In Access, you need a unique field (column)
to set this order.

If you had an autonumber primary key (say "ID")

ID Column A Column B Column C
1 blah blah 123
2 abc
3 test test
4 blah blah 456
5 test2test2 def
6 ghi

then what you want will be a piece of cake.

Can you create that, or point out the unique field
in your table that sets this ordinality, i.e., when
you set up a query to sort by this field you get
the rows ordered in the correct sense that one
row is "above" another?

FYI.... if "ID" column did exist and your table was
named "yurtable"

qryblahblah

SELECT
ID,
ColumnB
FROM
yurtable
WHERE
ColumnA = "blahblah";

update query (one method):

UPDATE yurtable As t
SET t.ColumnC =
SELECT
q.ColumnB
FROM
qryblahblah As q
WHERE
q.ID = DMax("ID","qryblahblah","[ID]<=" & t.ID)

when you are looking at a row in yurtable,
there will be a set of ID's equal to or smaller
than the ID in that row,
choose the row with the largest ID from that set
(and only where ColumnA = "blahblah")
and you will find the ColumnB value
"from the row above"
to assign to ColumnC.

If you followed the above, then you may realize
that one did not *have* to have "qryblahblah."

UPDATE yurtable As t
SET t.ColumnC =
SELECT
q.ColumnB
FROM
yurtable As q
WHERE
q.ID = DMax("ID","yurtable","[ID]<=" & t.ID & " AND [ColumnA]='blahblah'")

the slow domain function DMax was necessary
because update queries do not allow aggregation/totals,
unless it is hidden in the WHERE clause....

{untested}

UPDATE
yurtable As Below
INNER JOIN
yurtable As Above
ON
Below.ID >= Above.ID
SET
Below.ColumnC = Above.ColumnB
WHERE
Above.ColumnA = "blahblah"
AND
Above.ID =
(SELECT
Max(t.ID)
FROM
yurtable As t
WHERE
t.ID <= Below.ID)

 

[Gary Walter]

on further thought...

UPDATE
yurtable As Below
INNER JOIN
yurtable As Above
ON
Below.ID >= Above.ID
SET
Below.ColumnC = Above.ColumnB
WHERE
Above.ID =
(SELECT
Max(t.ID)
FROM
yurtable As t
WHERE
t.ID <= Below.ID
AND
t.ColumnA = "blahblah")

In Excel, the grid establishes ordinality,
i.e., one row is "above" another row.

In Access, you need a unique field (column)
to set this order.

If you had an autonumber primary key (say "ID")

ID Column A Column B Column C
1 blah blah 123
2 abc
3 test test
4 blah blah 456
5 test2test2 def
6 ghi

then what you want will be a piece of cake.

Can you create that, or point out the unique field
in your table that sets this ordinality, i.e., when
you set up a query to sort by this field you get
the rows ordered in the correct sense that one
row is "above" another?

FYI.... if "ID" column did exist and your table was
named "yurtable"

qryblahblah

SELECT
ID,
ColumnB
FROM
yurtable
WHERE
ColumnA = "blahblah";

update query (one method):

UPDATE yurtable As t
SET t.ColumnC =
SELECT
q.ColumnB
FROM
qryblahblah As q
WHERE
q.ID = DMax("ID","qryblahblah","[ID]<=" & t.ID)

when you are looking at a row in yurtable,
there will be a set of ID's equal to or smaller
than the ID in that row,
choose the row with the largest ID from that set
(and only where ColumnA = "blahblah")
and you will find the ColumnB value
"from the row above"
to assign to ColumnC.

If you followed the above, then you may realize
that one did not *have* to have "qryblahblah."

UPDATE yurtable As t
SET t.ColumnC =
SELECT
q.ColumnB
FROM
yurtable As q
WHERE
q.ID = DMax("ID","yurtable","[ID]<=" & t.ID & " AND [ColumnA]='blahblah'")

the slow domain function DMax was necessary
because update queries do not allow aggregation/totals,
unless it is hidden in the WHERE clause....

{untested}

UPDATE
yurtable As Below
INNER JOIN
yurtable As Above
ON
Below.ID >= Above.ID
SET
Below.ColumnC = Above.ColumnB
WHERE
Above.ColumnA = "blahblah"
AND
Above.ID =
(SELECT
Max(t.ID)
FROM
yurtable As t
WHERE
t.ID <= Below.ID)

I have a table with 3 columns where the data can look like this:

Column A Column B Column C
blah blah 123
abc
test test
blah blah 456
test2test2 def
ghi

I need to update column C with the value in Column B when Column A = "blah
blah". If it does not equal "blah blah", then I need to populate column C
with the value in Column B the last time Column A = "blah blah". So, in the
above example, I'd need it to look like this:
Column A Column B Column C
blah blah 123 123
abc 123
test test 123
blah blah 456 456
test2test2 def 456
ghi 456


In Excel, I can do it with this formula: =if(column A="blah blah", +column
B, +column C one above)
The "+column C one row above" is the same field (column) in the row above.

I have 500k records which Excel won't accomodate. I've talked to my SQL
gurus who can't figure out how to do it. Any suggestions?

THANK YOU!

 

[Beth]

Thank you Gary. I tried this code and am getting syntax error on the update
queries. I'm going to look at it more and will get it to work! I really
appreciate your help!

 

[Beth]

Hello Again Gary!

Well, once again - user error! Thank you very much! I used the "on further
thought" query and it worked! THANK YOU!

Beth

 

[Roddy]

--
isthatall