Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Pinecricker
Starting Member
8 Posts |
 Posted - 2008-03-15 : 23:24:13
|
| record.For example. I have a table with fields named "From" and "To" which track drill hole measurements. When I add a new record I would like it to autopopulate the "To" field with the "From" value from the previous record.Example Recordset Record 1, From=0, To=500, Lithcode=AB1Record 2, From=500, To=1000, Lithcode=CR4Record 3, From=1000<- trying to figure out how to auto populate previous record's from value hereTIA Todd |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-15 : 23:33:50
|
It depends on which version you have, but you can set a trigger to do this after insert depending on how many new records at a time get added, but it can also be added to the statement that inserts the new record possibly...How are new records added?Are the record ID's incremental?
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
jhocutt
Constraint Violating Yak Guru
385 Posts |
Posted - 2008-03-15 : 23:37:44
|
| declare @a table ( record int, [from] int, [to] int, LithCode varchar(10))insert into @a select 1,0,500,'AB1' union all select 2,500,1000,'CR4' select * from @ainsert into @aselect 3, [to], 1500, 'gh2'from @awhere record = (select max(record) from @a)select * from @a"God does not play dice" -- Albert Einstein"Not only does God play dice, but he sometimes throws them where they cannot be seen." -- Stephen Hawking |
|
|
|
Pinecricker
Starting Member
8 Posts |
Posted - 2008-03-15 : 23:39:25
|
The new records are being entered manually by a data entry person using an MS Access 2007 from end. The ID's are incremental, but not always in a specific pattern. i.e. record #1 might hold FROM and TO values of 0 to 100, record #2 might hold values from 100 to 1000, but the FROM value of the new record will always be equal to the TO value of the previous record.THquote:
Originally posted by dataguru1971
It depends on which version you have, but you can set a trigger to do this after insert depending on how many new records at a time get added, but it can also be added to the statement that inserts the new record possibly...How are new records added?Are the record ID's incremental?
Poor planning on your part does not constitute an emergency on my part.
|
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-16 : 00:25:51
|
Does the update need to happen on the SQL side, or can you modify the event handler of the Access GUI?It would make sense that adding the new record should happen as soon as the [TO] Value is updated. I would have to assume that since the FROM Value is to be auto-populated, that the [TO] Value is an unknown, and is therefore entered via the Access GUI?Once the TO value is updated to the current record, that same event(in access) OR a trigger (in SQL server) should automatically insert the new record using the entered [TO] Value ...How are the [TO] Values updated in the server? is the RecordID an autonumber or identity column?
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Pinecricker
Starting Member
8 Posts |
Posted - 2008-03-17 : 00:53:23
|
I guess I could do it on the Access side, but it is not that easy when the table is a linked table and not a local table. I was hoping there might be an easy way in SQL, similar to the way access can autopopulate a field using a query.To your other question, as soon as one record is completed, and a new one begun, the FROM field of the new record should be auto populated with the TO value from the previous record. All data are manually entered one record at a time.quote:
Originally posted by dataguru1971
Does the update need to happen on the SQL side, or can you modify the event handler of the Access GUI?It would make sense that adding the new record should happen as soon as the [TO] Value is updated. I would have to assume that since the FROM Value is to be auto-populated, that the [TO] Value is an unknown, and is therefore entered via the Access GUI?Once the TO value is updated to the current record, that same event(in access) OR a trigger (in SQL server) should automatically insert the new record using the entered [TO] Value ...How are the [TO] Values updated in the server? is the RecordID an autonumber or identity column?
Poor planning on your part does not constitute an emergency on my part.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-17 : 12:30:43
|
| If you're using SQL 2005 you can make use of Common Table Expressions along with the ROW_NUMBER() function to achieve this. |
|
|
|
|
|
|
|
|
Auto populate a field with a value from previous..