UPDATING A FIELD BASED ON NEXT VALUE IN ANOTHER FIELD

[Guest]

I HAVE FOUR FIELDS FIELD1,FIELD2,FIELD3,FIELD4 AS SHOWN BELOW
FIELD1 FIELD2 FIELD3 FIELD4
PRG 000000 003000 003000
PRO 002600 000030
PSA 002700 000030
PRO 002730 000030
PSA 002800 000100
PRO 002830 000030
PRO 002900 000030
PRC 003000 000030
PRG 003000 010000 003000
PRO 003900 000030
PRO 004000 000030
PRO 004030 000030
PRO 004100 000030
PRO 004130 000015

1.IF FIELD1 IS "PRG" FIELD3 IS FIELD2 OF NEXT ROW AND FIELD4 IS
FIELD3-FIELD2,IF FIELD1 IS NOT OF THAT TYPE THEN FIELD3 IS FIELD2+FIELD4
2.FOR THE NEXT RECORD AFTER PTYP IN FIELD1,FIELD2 IS FIELD3 OF PREVIOUS ROW
AND FIELD3 IS FIELD2+FIELD4
3.IF AGAIN THE FILED1 IS PTYP SAME STEP 1 & 2 AS TO BE REPEATED

THE RESULT SHUD BE AS SHOWN BELOW
FIELD1 FIELD2 FIELD3 FIELD4
PRG 000000 002600 002600
PRO 002600 002630 000030
PSA 002630 002700 000030
PRO 002700 002730 000030
PSA 002730 002830 000100
PRO 002830 002900 000030
PRO 002900 002930 000030
PRC 002930 003000 000030
PRG 003000 003900 000900
PRO 003900 003930 000030
PRO 003930 004000 000030
PRO 004000 004030 000030
PRO 004030 004100 000030
PRO 004100 004115 000015
PRO 004115 004145 000030
ANY HELP WOULD BE KINDLY APPRECIATED AS I AM NOT STRONG IN PROGRAMMING

 

[John W. Vinson]

ANY HELP WOULD BE KINDLY APPRECIATED AS I AM NOT STRONG IN PROGRAMMING

Please... don't type in all caps. IT'S HARD TO READ AND LOOKS LIKE
SHOUTING and it's considered impolite. just use all lower case if you
don't like the shift key.

Your problem is apparently a result of "committing spreadsheet".
Spreadsheets have rows in a defined order; tables *don't*. A Table is
an unordered "bag" of data - there is no definition (in any useful
way) of the "previous record" or "next record".

What do these fields mean? What is the source of the data? It appears
that you're trying to store calculated values; you shouldn't, it's
neither necessary nor is it good design. Perhaps you should consider
using Excel (which DOES let you do calculations in spreadsheets,
unlike what you can do in Access tables) for this application.

John W. Vinson [MVP]

 

[Guest]

Thank you so much for your reply.
I am so sorry for using Uppercase as i didnt mean to do that.Just now i got
the meaning of that.From now on i wont do that mistake.
I want to know how to loop through each record in Access table as one row
value in a field goes to next row of the same field or to the previous row
depending upon the value of Ptyp(a column in a table).
Ptyp,strt tm,end tm,dur are the fields in table.If Ptyp="PRG",then it will
have all strt tm,end tm and dur.if it is not of that type then it will have
only strt tm and dur from which you have to calculate end tm.The second part
i can do but the first part if it is of Ptyp="PRG" then existing end tm need
to be changed to next row end tm value if it is a first row in a table.If not
the strt tm of the column with ptyp as"PRG"should be previous row value of
end time and end tm of that record should be the strm tm of the next record.
I think it is confusing.
For more clear understanding of my problem ,you can have a look at the real
data i have posted in my first mail
Kindly help me out

Please... don't type in all caps. IT'S HARD TO READ AND LOOKS LIKE
SHOUTING and it's considered impolite. just use all lower case if you
don't like the shift key.

Your problem is apparently a result of "committing spreadsheet".
Spreadsheets have rows in a defined order; tables *don't*. A Table is
an unordered "bag" of data - there is no definition (in any useful
way) of the "previous record" or "next record".

What do these fields mean? What is the source of the data? It appears
that you're trying to store calculated values; you shouldn't, it's
neither necessary nor is it good design. Perhaps you should consider
using Excel (which DOES let you do calculations in spreadsheets,
unlike what you can do in Access tables) for this application.

John W. Vinson [MVP]

 

[John W. Vinson]

I want to know how to loop through each record in Access table as one row
value in a field goes to next row of the same field or to the previous row
depending upon the value of Ptyp(a column in a table).

If tables had row numbers, as spreadsheets do, this might make sense.

But they *don't*.

A Table may *look* like a spreadsheet, but it isn't. A Table is an
unordered "bag" of data. There is absolutely nothing within a table to
distinguish one row of data from another except for the actual
contents of that record.

What you are doing is a perfectly normal spreadsheet operation, and it
can be done very easily in Excel. It is NOT a normal relational
database operation and will be very difficult to do.

Ptyp,strt tm,end tm,dur are the fields in table.If Ptyp="PRG",then it will
have all strt tm,end tm and dur.if it is not of that type then it will have
only strt tm and dur from which you have to calculate end tm.The second part
i can do but the first part if it is of Ptyp="PRG" then existing end tm need
to be changed to next row end tm value if it is a first row in a table.If not
the strt tm of the column with ptyp as"PRG"should be previous row value of
end time and end tm of that record should be the strm tm of the next record.
I think it is confusing.

It is. And it's making some completely invalid assumptions about how
relational databases work! You cannot do calculations in a table,
period. You can do calculations in a Query based on data in a table;
but in order to do what you are describing, you must have a field in
your table which unambiguously defines the order of records. It
appears that you do not have such a field.

For more clear understanding of my problem ,you can have a look at the real
data i have posted in my first mail

The data you posted is - as I say - inappropriate for a relational
database. There is NO way that Access can distinguish two rows
containing the same three values, and determine what value should be
put into the NULL fourth field.

John W. Vinson [MVP]