473,372 Members | 1,039 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Getting previous values of a field

Hi,

In oracle I have a LAG function using which I could get the previous
value of a field.
Do we have anything similar to that in SQL Server or access?
Thanks

Devi

Mar 15 '06 #1
4 4124
devi (de******@gmail.com) writes:
In oracle I have a LAG function using which I could get the previous
value of a field.
Do we have anything similar to that in SQL Server or access?


I have no idea what LAG does in Oracle, but the only place you can
retrieve this information easily is when you are in an UPDATE or
DELETE trigger, in which case you find the information in the
virtual table "deleted". One the transaction is committed, the only
way to retrieve the information is through a log-reader tool.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 15 '06 #2

Sorry. I guess I wasnt clear in explaning my doubt...

I am creating a simple bug tracker application (in Access db) and i
created a hisotry table to
log the bug history.

The history table contains details like ProblemRecordNo (PRN),
RecordStatus, Assignee, Reporter (and also some more columns). The PK
for this table is ID. It contains multiple entries for a Record.

I have inserted a lot of records int History table and now I introduced
two new fields into History table. They are PreviousStatus and
PreviousAssignee. I wanted to update the previous values of the Status
and Assignee for each record.

Say my History table contains values like

ID PRN Status Assignee
1 10 Report UserA
2 10 InProcess UserA
3 10 Esclated UserB

Now after introducing the Previous fields, the History table should
look like

ID PRN Status Assignee PreviousStatus PreviousAssignee
1 10 Report UserA
2 10 InProcess UserA Report UserA
3 10 Esclated UserB InProcess UserA
In the first record the PreviousStatus and PreviousAssignee are empty
bse there is no previous values for those two items. the next two
records contain the previous values of the status and assignee.

I used the following sql statement to update the table, but I get a
script time out error

UPDATE (
SELECT H.PRN, H.ID, H.PREVIOUSSTATUS AS OLDSTATUS, N.STATUS AS
NEWSTATUS,
H.PREVIOUSASSIGNEE AS OLDASSIGNEE, N.ASSIGNEE AS NEWASSIGNEE FROM
HISTORY H,
(SELECT N.ID, N.PRN, N.STATUS, N.ASSIGNEE FROM HISTORY N ) N
WHERE H.PRN = N.PRN AND N.ID < H.ID AND N.ID IN
(SELECT MAX(M.ID) FROM HISTORY M WHERE M.ID < H.ID AND H.PRN = M.PRN) )
SET OLDSTATUS = NEWSTATUS,
OLDASSIGNEE = NEWASSIGNEE

Is there anyother way i could update the table with a optimized query
that doesnt take time to update large set of rows.

:(

Mar 16 '06 #3
devi (de******@gmail.com) writes:
I am creating a simple bug tracker application (in Access db) and i
created a hisotry table to
log the bug history.


In such case you should ask in comp.databases.ms-access. While both
SQL Server and Access are produced by the same Redmon company, there
are vast differences between the SQL dialects.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 16 '06 #4
Can you access LAG in PL/SQL? I thought is was a datamining function. It is
also available in SQL Server Analysis services.

--
Anith
Mar 16 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Elvis V. | last post by:
Good morning, I have a table with three fields, Buildings, Floors and Rooms. This is what I would like to do; in my form when I select Building1 in my drop down box for Buildings, when I go to the...
1
by: allyn44 | last post by:
Hello, I have a table that has null fields that need to be filled in with the value of the previous record (example below) id date 1 2/2/02 2 3 4/4/02 4
6
by: abbylee26 | last post by:
Im comparing values in a field while doing a loop if sAct<>myData(5,i) then At the end of the loop I make the value of sAct equal the current myData This will work when comparing other fields...
1
by: Rajasuds | last post by:
I have a question in MS Access I have two tables MASTER with Fields 1, Field 2, Field 3 TRANS with Field1, Field 2, Field,10 I need to set up a look up condition where, I need !Field 2 to...
5
by: jdzemke | last post by:
I am getting 'options' is null or not an object using when using a dynamic dataset with a dropdown list in an html form. I am filling-in a text field and filtering the values in the drop-down. ...
1
by: roveagh1 | last post by:
Hi I've been using the 2 year old link below to repeat values from previous record field into current corresponding field. It's worked fine for text but the last piece of advice was to use the same...
6
by: John Devlon | last post by:
Hi, I've got a small problem using 2 asp pages..... The first page has a button to go to the second page. On the second page, when the page is loaded, a check is excecuted to see if a...
7
by: jodleren | last post by:
Hi all! I have a problem - I have a list of checkboxes, with certain data... the point is, that some of them are set, and the important thing for mere is to get those values, which have changed...
1
by: raghuvendra | last post by:
Hi I have a jsp page with 4 columns: namely Category name , Category order, Input field and a submit button. All these are aligned in a row. And Each Category Name has its corresponding Category...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.