Fill in Records with Header Info Above

[Jason Morin]

I've imported data into a table where I need to fill in
records (in the example, Fields 1 & 2). I need to fill in
the header information from above (see example). How can
I do this with an update query? Thanks. Jason

Record Field1 Field2 Field 3 Field4
1 ABC Co. Order100 Green 3
2 Blue 4
3 Red 1
4 SmithBros Order102 Yellow 8
5 Blue 2
6 Orange 1
7 Green 1
8 XYZ Co. Order108 Green 2
9 Red 5

 

[Tom Ellison]

Dear Jason:

If the column you show as "Record" is a column in the table, then you
could do this. The rule could be:

For each row where Field1 is missing, use the value of Field1 from
the largest Record that is less than the Record of that row where
Field1 is not missing.

I can code this is it is useful.

If there is no column Record in the table, then there is no sequence
to the rows in your table that would allow you to do this. There is
no such thing as an order in a table unless you sort it.

However, there is a trick that could just work if you use it
immediately after importing. You see, I lied, just a bit. There is
such a thing as an unsorted order in a table, but it it fragile.

You could add a column like the "Record" column. Then, open a
recordset on the table in unsorted sequence and update the Record
column using VBA, incrementing each time. This will probably work if
you do it before something else happens to the table.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

One thing I should have said about the "physical order" in a table. A
query cannot generally reference that order. My recommendation was to
step outside queries to get the ordering accomplished.

It is probably important that the import be done to a freshly cleared
table.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts