Use query to copy data from previous row

  • Thread starter Thread starter penguin66
  • Start date Start date
P

penguin66

I'm trying to automate data import from a text file into access using queries
so my users can follow the progress of the data import and clean-up. I've
run into a snag in the clean-up portion of my queries. I have several
columns that need to be filled with data. My criteria for filling the field
is: If the value is null, copy the value of the record above. The records
are sequentially numbered, so I thought that a dlookup function would work,
but it only will fill in the value on the next row and nothing further. Is
there a way to get the columns to fill in without user intervention?

The statement I'm using in my query is:
iif ([newpiorgcode]="",DLookUp("[newpiorgcode]","textimport8","[id] =" &
[id]-1), [newpiorgcode])

I've tried this as an update query and also tried it as a make table query,
but no luck.
Hope I've been able to explain myself clearly.
 
BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
Try this --
UPDATE textimport8, textimport8 AS textimport8_1 SET
textimport8_1.newpiorgcode = [textimport8].[newpiorgcode]
WHERE (((textimport8_1.newpiorgcode) Is Null) AND
((textimport8_1.ID)=[textimport8].[ID]+1));
You data should be updated from this --
ID newpiorgcode X
1 A A1
2 A2
3 A3
4 B B1
5 B2
6 B3
7 B4
to this ---
ID newpiorgcode X
1 A A1
2 A A2
3 A A3
4 B B1
5 B B2
6 B B3
7 B B4
 
Thanks for the help Karl. The query works on a test database, but when I use
the actual data, the query doesn't work. I can get it to work if I copy the
data into a new table, but not if I use the table created by previous
queries. I'm completely baffled by this one.

KARL DEWEY said:
BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
Try this --
UPDATE textimport8, textimport8 AS textimport8_1 SET
textimport8_1.newpiorgcode = [textimport8].[newpiorgcode]
WHERE (((textimport8_1.newpiorgcode) Is Null) AND
((textimport8_1.ID)=[textimport8].[ID]+1));
You data should be updated from this --
ID newpiorgcode X
1 A A1
2 A2
3 A3
4 B B1
5 B2
6 B3
7 B4
to this ---
ID newpiorgcode X
1 A A1
2 A A2
3 A A3
4 B B1
5 B B2
6 B B3
7 B B4
--
KARL DEWEY
Build a little - Test a little


penguin66 said:
I'm trying to automate data import from a text file into access using queries
so my users can follow the progress of the data import and clean-up. I've
run into a snag in the clean-up portion of my queries. I have several
columns that need to be filled with data. My criteria for filling the field
is: If the value is null, copy the value of the record above. The records
are sequentially numbered, so I thought that a dlookup function would work,
but it only will fill in the value on the next row and nothing further. Is
there a way to get the columns to fill in without user intervention?

The statement I'm using in my query is:
iif ([newpiorgcode]="",DLookUp("[newpiorgcode]","textimport8","[id] =" &
[id]-1), [newpiorgcode])

I've tried this as an update query and also tried it as a make table query,
but no luck.
Hope I've been able to explain myself clearly.
Click to expand...
Click to expand...
 
Back
Top