Appending Data from One Record to the Next

[Greg@Work]

I have a table that has records of accounts, actions taken, and operator ids. Unfortunately, the records are incomplete. The report was written poorly so that there is a row that contains data in all three fields for all subsequent rows there is only the action taken, no account or operator. So my table looks like that the records below. As you can imagine this means that I cannot write any useful queries on what happened on various accounts. Does anyone know a that I can append the account number to every row that pertains to that account number? Thanks in advance for your help.

Account	Action Taken	Operator ID
1234	Act1	1
	Act2
	Act3
	Act4
1235	Act1	2
	Act2
1236	Act1	3
	Act2
	Act3
…

[Dal Jeanis]

Are you sure that is the table, rather than a report based on the table? If that information isn't on the records, then you couldn't get a consistent report from the table - all those "records" without the account would end up together at the end.

Please look at the table itself in design view, and see what the fields in the table are. Then look at the table itself in data sheet view, and see what data is in the table.

[Greg@Work]

I created the table via outuput from a mainframe report. I have been asked to clean this up as best as possible because they have hit a dead end in the FOCUS code that creates the report. I agree its a terribly written report. But it is all I have to work with.

[Dal Jeanis]

Okay, then your best bet is to use a VBA routine to process the mainframe report and do the inserts.

If you can't do that, then you have to use VBA against a recordset or a complicated SQL subquery to update the table, and it's only possible if the table has an autonumber primary key that keeps the records in the same order that they were in the mainframe report.

Is that the case? If so, then what are the field names for

Table: MyActionTable
   PK       Autonumber 
   Account  Text
   Action   Text 
   OperID   Text

[Greg@Work]

I do have an autonumber field that does have the records in sequence. Its named "Key".

[Dal Jeanis]

Okay, try this. If the results of the second query look right, then you can do a MakeTable from it. First, obviously, try this in a junk copy of your database. Second, adjust the query, table and field names to match your actual field names.

Table: MyActionTable
   Key      Autonumber 
   Account  Text
   Action   Text 
   OperID   Text

Query1:
SELECT T1.Key, T1.Account, T1.Action, T1.OperID
FROM MyActionTable AS T1
WHERE T1.ACCOUNT & "" > "";

Query2:
SELECT T2.Key, Q1.Account, T2.Action, Q1.OperID
FROM MyActionTable AS T2, Query1 as Q1
WHERE Q1.KEY = 
  (SELECT MAX(Q1A.Key) 
   FROM Query1 AS Q1A
   WHERE Q1A.Key <= T2.Key);