Copy cell above in a table

[pdvsa]

Hello,

I need to update a table: tblImportToAccess.[TradeNo] and tblImportToAccess.[CoName]
with the value that is in the row above it.

example:

TradeNo	CoName
123	XYZ
123	XYZ
null	null
333	ABC
null	null

I need the nulls on [TradeNo] and [CoName] to populate with 123 and XYZ respectively for the first null but the second null I would need it to populate with 333 and ABC respectively.
My dataset is much larger and there are different TradeNo and CoName (much like dragging down the cell above in excel).
I am not sure if this would best be done by way of an update query but if so then I have a part of it below and looking for the remainder.
I am a novice user of Access. I can easily do this in excel with a macro but I want to to perform this in access after I import the excel data.

UPDATE tblImportToAccess SET
WHERE (((tblImportToAccess.[TradeNo]) Is Null) AND ((tblImportToAccess.[CoName]) Is Null));

Thank you.

 

[Micron]

If you do not have a field that imposes some sort of ascending or descending order, then AFAIK you cannot do this. There is nothing in your sample data that governs which record is to be considered as preceding or following any other record. Think of a spreadsheet - while the row numbers may not be part of the sheet data, they are organizing the data. You don't seem to have that. If you try to sort by TradeNo, all of your records with null in this field will be grouped together.
If this is a one time thing and you know how to do it in Excel, I'd suggest you take care of it there. Otherwise, you will have to add a sorting field, such as an autonumber.
EDIT - forgot to say that once you have the sort order field, you will need to use a subquery or write code to do this. For subquery approach see

Microsoft Access tips: Subquery basics

Using subqueries in Microsoft Access. What subqueries are, and examples of using subqueries.

allenbrowne.com

Microsoft Access tips: Surviving subqueries

Techniques to avoid 8 common errors, frustrations, limitations, and bugs when using subqueries in Microsoft Access.

allenbrowne.com

 

[grantsmith]

I am not sure why the mass update with the update statement does not work. You can always loop through the table as a record set and update the the missing values with update edits.

Set db = CurrentDb 
strSQL = "SELECT * FROM tblName WHERE TadeNo IS NULL"
Set rs = db .OpenRecordset(strSQL, dbOpenDynaset)

If rs.recordcount=0 Then Exit Sub

rs.Movefirst

i= 1 

Do

rs.Edit
rs![TadeNo] = 123
rs.Update 

rs.MoveNext   
i= i+ 1       
Loop until rs.eof

rs.Close   
db.Close    

Set rs = Nothing
Set db = Nothing

 

[teylyn]

Load the data into Power Query and use the Fill Down command.