Need Header info to fill down to Next Header and repeat over till done.

[PJ Crittenden]

Thank you in advance for any help you can give. I've never written code...just macros and this is not possible in a macro. Speak "newbie" please!

Example: Under Header is the header number. I need it to populate all the blanks until it reaches the next header number and then start filling all the blanks with that number, and so on and so on till the end.

Header	ID	Field1	Field2	Field3	Field4	Field5	Field6
12345678912125	30361	12345678912125412SU					T0
blank________	30362	19543125878001	XYZ		4.79	.00Z1751404935837221490011	C1
blank________	30363	14786542354451	XYZ		2.77	.00Z1751404935858650290011	C1
blank________	30364	16498734245601	APC		7.82	.00Z1751405532638610350011	C1
blank________	30365	16161315478521	DNC		7.53	.00Z1751406238499190360011	C1
11657894621321	35752	11657894621321723SU					T0
blank________	40477	16987325456541	DNC	09	25.62	.00 131601457905144001	C1
blank________	40478	13654654232121	XYZ	09	174.4	.00 131611460909094001	C1
blank________	40479	13789465412181	APC	09	408.3	.00 131681461071131001	C1

[June7]

What you show is not appropriate structure for records in table. Is this data in a query that is joining related parent and child tables? Are you trying to build a report with that query and group records on part of the value from Field1 in parent table record?

If you want to provide db for analysis, follow instructions at bottom of my post.

[PJ Crittenden]

Here's a sample DB. Thank you!

[rpeare]

I'm guessing he's imported a spreadsheet and he's trying to build a relationship between the 'header' information and the 'detail' information which I don't know you can do without vb code. (I can't look at it right now sorry, computer is tied up crunching away on something)

[June7]

What I suspected - bad data structure. What is this data and where did it come from? Why don't the fields have meaningful names? Was this a spreadsheet import?

Records 50610 and 50619 are not represented in the sample in the original post - why not?

Appears the table needs another field populated with Field1 value from the T0 records. Then delete the T0 records. Don't know what you want to do with the T9 records.

This can be done programmatically using VBA to open a recordset object and cycle through the records and populate the new field. How many records are there?

[PJ Crittenden]

I didn't create this bad structure...that's the way the data is sent to us. I'm trying to fix it. The source of this data will never send it any other way.

The data is and will be from multiple .txt files. They will have meaningful field names but the .txt files do not come with them...I can modify later on. Right now, all I'm concerned with is the Header.

The records you're referring to are created by autonumbering. I deleted all records and reloaded with modified data....hence new record numbers.

I hope you can help.

Thank you,

Pam

[PJ Crittenden]

There will be hundreds of thousands of records, but they will be uploaded elsewhere, so I can delete the table once the data has been uploaded. Then I will have to start the process all over again with new files coming in daily.

[PJ Crittenden]

That's exactly what is needed. I can always run a delete query to get rid of the headers and EOF rows.

[June7]

I edited my previous post perhaps after you originally read it.

The actual record ID numbers were not critical to my question. The point is T9 records in the table were not represented in the posted sample. Why - should they be deleted in the cleanup process?

This will be a periodic process?

The following code worked in the sample db.

Option Compare Database
Option Explicit
________________

Sub PopulateHeader()
Dim rs As DAO.Recordset, strHeader As String
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Raw_Data WHERE Header Is Null ORDER BY ID;")
While Not rs.EOF
    If rs!Field6 = "T0" And strHeader <> Left(rs!Field1, 14) Then
        strHeader = Left(rs!Field1, 14)
    Else
        rs.Edit
        rs!Header = strHeader
        rs.Update
    End If
    rs.MoveNext
Wend
End Sub

EDIT: I see you did some posting while I was composing.

[PJ Crittenden]

Yes, I will need to delete both the T0 and T9 rows in the end.

Yes, this will be a periodic process....up to 30 or so .txt files from various clients on a weekly basis.

Here's where I show my ignorance with code. I don't know where to put it. Do I start a module? Do I add it to the SQL view of a query? I'm sorry, all I've ever done is use the macro wizard in the past.

Thank you.

[June7]

I created a general module and ran the code with the VBA editor Run button.

The code can go where you want it (except in SQL view of query), maybe in a button Click event. In the event property select [Event Procedure] then click the ellipsis (...) to open the VBA editor at the procedure. Type or copy/paste code. Modify as needed.

This code does make assumption that the record ID will properly order the records.

Code to delete records:

CurrentDb.Execute "DELETE FROM Raw_Data WHERE Field6='T0' OR Field6='T9'"

[PJ Crittenden]

OMG, it worked! You are a genius! Send me a bill!

[June7]

Glad it worked. I might have edited again after you read post.

[PJ Crittenden]

Awesome! I'll add it!