add value to one record based on another record

[pkohli8]

Hi,
I have a table that has following fields
field A, Field B, Field C(Time)

for example

Field A Field B Field C
102 Apple 3:00 AM
102 Orange 3:15 AM
102 Banana 4:00 Am
300 Grapes 1:00 AM
300 Berry 4:00 PM
401 Apple 1:00 AM
401 Grapes 3:00 AM

Now I need to Add another column say Field BB that displays previous value of Field B corresponds to same Field A

for example (Final result)

Field A Field BB Field B Field C
102 Apple 3:00 AM
102 Apple Orange 3:15 AM
102 Orange Banana 4:00 Am
300 Grapes 1:00 AM
300 Grapes Berry 4:00 PM
401 Apple 1:00 AM
401 Apple Grapes 3:00 AM

Please let me know if anybody knows how this can be done.

Thanks

 

[vbkris]

hi, i am unable to understand your example.

can you explain a bit more. i think this is possible using triggers...

Known is handfull, Unknown is worldfull

 

[pkohli8]

Hi,
Thanks for the reply,

I have a table that has 3 fields

Field A Field B Field C
101 Apple 3/2/06 3:00 AM
101 Orange 3/2/06 4:00 AM
101 Banana 3/2/06 9:00 AM
102 Orange 3/2/06 4:00 AM
102 Grapes 3/2/06 5:00 AM
102 Orange 3/2/06 10:00 AM
103 Orange 3/2/06 4:00 AM
103 Grapes 3/2/06 5:00 AM

now the need to create another field BB that has previous value of Field B if Value of Field A = previous value(Field A) at previous time.

e.g

Field A Field BB Field B Field C
101 Apple 3/2/06 3:00 AM
101 Apple Orange 3/2/06 4:00 AM
101 Orange Banana 3/2/06 9:00 AM
102 Orange 3/2/06 4:00 AM
102 Orange Grapes 3/2/06 5:00 AM
102 Grapes Orange 3/2/06 10:00
103 Orange 3/2/06 4:00 AM
103 Orange Grapes 3/2/06 5:00 AM


I hope this time its clear...

Thanks In advance

 

[vbkris]

hi,

the first task will be to pick the last data, this query will help in that:

select top 1 FieldB from Table where FieldA=101 order by FieldC desc

now the same must be inserted in a trigger.

every time a new entry is added the above query will return the last value of FieldB if any. I am assuming that the value with the latest FieldC is the last entry...

Known is handfull, Unknown is worldfull

 

[pkohli8]

Hi,
I already have this data for several last months and i need to generate on demand report, to show the number of times one record gets updated (between two dates) and what was the previous value for that record before its got updated.
so i was thinking of creating a table where i can upload all the data between two dates(for which report needs to be created) and use some query to add another column that can pull previous value of Field B for same previous Value of field A.

thanks