Need to pull data from record X+1 and insert it into record X.

[cwhite23]

I have a database that has two lines per record (This was an import from an Excel File!). How can I tell Access to pull a field's data from the second line, and insert it into the same field on the first?

 

[randy700]

Is there a key field in your table?
How about providing some sample data?


Randy

 

[GKChesterton]

Query 1 will select all the Line A data.
Query 2 will select all the Line B data.
If there is no way to distinguish Lines A from Lines B, you can resort to an AutoNumber key field and use even/odd numbering for that.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[cwhite23]

Order Number Name State
John Doe NC
12345 Paid

What I need is to have some way to move the '12345' up to the Order Number field in the first line. The 'Paid' comment is irrelevant.

 

[GKChesterton]

[ol][li]Dump it back into Excel. [/li]
[li]Copy the entire data and paste it in duplicate to the right of the original data.[/li]
[li]Delete a few cells at the top of the new columns -- one row's worth. You have now gotten each record consolidated on one row. You also have a junk record for every other row.[/li]
[li]Find a way to delete the junk records. Usually I can just sort by some distinctive field (Order Number will do for you) so the junk records are contiguous. Sometimes I might start by adding a column with number series, to preserve the sort order; probably not a need in this instance.[/li]
[li]Re-import into Access. [/li] [/ol]

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​