Insert data from last record if next record is null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that is imported from an proprietary outside source. The
table I import has several rows of data for the same transaction, but only
the first row has a unique ID (called TransID). I am trying to write a macro
or update query to fill in the Trans ID's that are blank with the same
TransID that in the first row of that transaction.

I want to look at the first record in the TransID field - copy the contents
(the very first row will always have data)- look a the next record, and if
TransID is null, paste in the data from the first TransID and repeat that
until I run into the next TransID that isn't null. Then copy that and go
through those steps gain.

Can this be done without VB code?
 
IS there anything else- any combination of fields- that makes the records
unique, or more precisely that would allow you to group the records without
the ID field along with the record with the ID field?
 
I use Excel as an intermediate file and then a crosstab query to put all the
data into one Access record.
With the data in Excel insert a new column A. Insert a new row at the top
if there is no column label row. The below assumes TransID is in column B.
In A2 type -- =If(B2="",A1,B2)
Use autofill for the colum. Copy and paste special - values. Column A now
has a TransID in each row and changes with each new transaction. Delete
column B.

You can also put a row number for each line of the same transaction by doing
the following --
Isert new column A.
In A2 type -- =If(B2=B1,A1+1,1)
Use autofill for the colum. Copy and paste special - values.

Import into Access.
 
Here is a sample of what I want to happen:

Starts out as two columns:

Trans ID | Details
1001 Bank Debit
Expense Credit1
Expense Credit2
Expense Credit3
1002 Bank Debit
Expense Credit1
Expense Credit2
1003 Bank Debit
Expense Credit1

You can see that with each new trans ID, I have a new transaction. I want
to update all of the rows under 1001 to 1001 (where it is blank) until, of
course, I get to the new transaction 1002.

Is there any way to write an update query that will look at the first record
and make the second record what the first one is if it is null and then go
all the way down the list following that rule?
 
Back
Top