If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Use query to copy data from previous row
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. |
#2
|
|||
|
|||
Use query to copy data from previous row
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" wrote: 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. |
#3
|
|||
|
|||
Use query to copy data from previous row
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" wrote: 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" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|