Fill blank in table with data from previous row

[80.vikas]

Hi,

Can anyone suggest me how can I fill blank cell from data in previous row in Access Table.
Update Flag in attached file with value in Previous row.

Thanks
Vikas

[June7]

Can't do it in table. Use form. Review this http://allenbrowne.com/ser-24.html

[80.vikas]

Please suggest something that can be handle with Query and not with use of form.

Can't do it in table. Use form. Review this http://allenbrowne.com/ser-24.html

[June7]

You can run an INSERT SELECT query. The query would have to find the most recent record in table and then insert new record with values from the selected record. Is this a multi-user database?

If you are working directly with tables, can just select the entire row and do copy/paste into new row.

[80.vikas]

Can you please sugget how to run Insert query in database in attached file .

No, this is single-user database. But still don't want to update manually, since data is quite huge and needs daily updation.

You can run an INSERT SELECT query. The query would have to find the most recent record in table and then insert new record with values from the selected record. Is this a multi-user database?

If you are working directly with tables, can just select the entire row and do copy/paste into new row.

[June7]

The SQL is like:

INSERT INTO Test (Flag) SELECT Flag FROM (SELECT Flag FROM Test WHERE ID=(SELECT Max(ID) As MaxID FROM Test)) As Q1;

Review http://msdn.microsoft.com/en-us/library/ms188263.aspx

[80.vikas]

Insert is not working. I need to update particular values.

Original date
ID Flag
1 Fixed
2
3 Variable
4

Data required
ID Flag
1 Fixed
2 Fixed
3 Variable
4 Variable

I want to do this on recurring basis, so I think I need to use Module option that can be linked with Table Query and dont want this with form.


Option that I can think of is creating new function using SQL, something like below:

Sub Sort()


Dim Rs As Recordset
Dim RsSql As String
'Create a snapshot of data from your table with the desired fields and data
RsSql = "Select ID, SORTDATE1 From Dates2 Where ID IS NOT NULL"
'Where Condition = '" & Criteria & "'"
'Open the recordset
Set Rs = CurrentDb.OpenRecordset(RsSql)
'Destroy the instance of the recordset from memory
Set Rs = Nothing
End Sub

But I dont know if :
1. Above query is correct
2. Also, how to call this in Table query.

Can you please create above sturcture in my Access file and send back to me.

The SQL is like:

INSERT INTO Test (Flag) SELECT Flag FROM (SELECT Flag FROM Test WHERE ID=(SELECT Max(ID) As MaxID FROM Test)) As Q1;

Review http://msdn.microsoft.com/en-us/library/ms188263.aspx

[June7]

Review this article http://allenbrowne.com/subquery-01.html especially the section 'Get the value in another record'

[80.vikas]

Can you please provide suggest me how can I use below in my access file?

Review this article http://allenbrowne.com/subquery-01.html especially the section 'Get the value in another record'

[June7]

Having a record get value from another record is not simple.

Never tried anything like this. Possibly:

1. Build query using Allen Browne's query syntax that gets the value from other record

2. Use that query in an UPDATE query on the original table. The query would include join on the table and the first query so that the value will be available for the UPDATE TO row of the query grid. Review Help on how to build UPDATE where the update value comes from another table/query.

Try:
query 1: PriorValue
SELECT Test.ID, Test.Flag, (SELECT Flag FROM Test As Dupe WHERE Dupe.ID = Test.ID-1) AS PriorValue FROM Test;

query 2: couldn't get a join to work so used DLookup
UPDATE Test SET Test.Flag = DLookUp("PriorValue","PriorValue","ID=" & [ID]) WHERE (((Test.Flag) Is Null));