Obtain a value from a field in the previous record

[coucar3456]

All,

Is there a formula in Access that will obtain the previous value if the field is null? Below is the consolidated data file I'm working with.




CARTER1 Directory: /load_files/ROLL-UP/component1
Name: COMPONENT_DM.txt
-------------------------------------------
UIC
W333333
W555555
SIN23 Directory: /load_files/ROLL-UP/componet2
Name: COMPONENT_DM.txt
----------------------------------------------
UIC
W111111
W222222

 

[xenou]

Your question isn't clear - what is the sample data supposed to illustrate here? What are you looking for? Is this data in Access or not in Access already?

 

[coucar3456]

I tried to delete the post for what I'm trying to do its easier for me to do it in Excel then import the data file into my database.

What I was attempting to do in an access query is find a function that will replace all Null values with the previous record. See below

Field1
12345
null
null
null
6789
null
null

The fix would look like this:

Field1
12345
12345
12345
12345
6789
6789
6789

 

[xenou]

Is there any other data in these records? Answer might be no, because database tables are not oriented by rows and you can't (usually) rely on things like "before" and "after" if you mean physically one row before or after another.

 

[coucar3456]

I have an auto number as at the primary key thats about it. It only takes me a minute to have excel had the missing numbers.

 

[Joe4]

One of the hardest things new Access users often have is understanding how Access and database programs are very different than Excel (they are not an extension of Excel). In database tables, order really has no meaning, as xenou alluded. Sometimes once used a great metaphor and said to think a database table as a "sack full of marbles". They are all mixed up, and there really is no order. In a true database table, each record is really totally independent of all the other records, and has all the information it needs to be a complete record.

So, from a database standpoint, what you are dealing with is bad/incomplete data. There are a few ways you can clean it up, including:
- Using Excel to fix it, and then re-import the data
- Sort the data by whatever field (i.e. Autonumber) in a Query. Then write VBA code that creates a RecordSet and loops through the Query, row-by-row, where it can make reference to the previous record.

As you might imagine, the second method is a bit more complex (requires knowledge of Access VBA and RecordSets), and is fairly slow, as you are looping through each record individually.
So if you can correct the data quickly and easily in Excel, I would go that route.