How do i do this?

[Emma Hope]

I have a tab delimited text report sent to that looks something like this:

Trans SupplierA
A01 Data Data Data Data
A01 Data Data Data Data
A01 Data Data Data Data
A01 Data Data Data Data
Trans SupplierB
A01 Data Data Data Data
A01 Data Data Data Data
Trans SupplierC
A01 Data Data Data Data
A01 Data Data Data Data
A01 Data Data Data Data

and what i need is something like this in my final table

SupplierA A01 Data Data Data Data
SupplierA A01 Data Data Data Data
SupplierA A01 Data Data Data Data
SupplierA A01 Data Data Data Data
SupplierB A01 Data Data Data Data
SupplierB A01 Data Data Data Data
SupplierC A01 Data Data Data Data
SupplierC A01 Data Data Data Data
SupplierC A01 Data Data Data Data

i.e. the 'data' has a 'header' row and i want the name from the header row
to be against each 'data' row.

The number of rows of data for each supplier can vary significantly. The
'header' row always has Trans in the first field and the 'data' rows always
have A01 in the first field.

Can anyone advise how i might achieve this?

Thanks
Emma

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImportLineInput.mdb" which illustrates in general how to do
this. The specifics will have to be adapted a tab delimited file. You can
download it for free here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=340.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Emma Hope]

Roger,

Thanks for taking the time to respond but your solution seems to bear no
relation to my problem.

Can anyone else think of a solution to this problem please.

Thanks
Emma

 

[Roger Carlson]

It doesn't? How odd because I was able to modify it as follows:

'--------------------------
Function ImportTable()
Dim dbs As Database, rst As Recordset
Dim Directory As String
Dim MyString As String
Dim Supplier As String

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from MyTable"
DoCmd.SetWarnings True

Set dbs = CurrentDb
Directory = (Mid(dbs.Name, 1, Len(dbs.Name) - Len(Dir(dbs.Name))))

Open Directory & "\TextReport.txt" For Input As #1

' Create a dynaset-type Recordset object based on table.
Set rst = dbs.OpenRecordset("MyTable")

Do While Not EOF(1)
Line Input #1, MyString

If Left(MyString, 5) = "Trans" Then
Supplier = Mid(MyString, InStr(MyString, Chr(9)) + 1)
Else
'Add a new Record
rst.AddNew
rst!Field1 = Supplier

rst!Field2 = Left(MyString, InStr(MyString, Chr(9)) - 1)
MyString = Mid(MyString, InStr(MyString, Chr(9)) + 1)
rst!DataField3 = Left(MyString, InStr(MyString, Chr(9)) - 1)
MyString = Mid(MyString, InStr(MyString, Chr(9)) + 1)
rst!DataField4 = Left(MyString, InStr(MyString, Chr(9)) - 1)
MyString = Mid(MyString, InStr(MyString, Chr(9)) + 1)
rst!DataField5 = Left(MyString, InStr(MyString, Chr(9)) - 1)
MyString = Mid(MyString, InStr(MyString, Chr(9)) + 1)
rst!DataField6 = MyString
rst.Update
End If
Loop
' Close text file.

MsgBox "Done!"

Close #1
rst.Close
Set dbs = Nothing

End Function
'--------------------------

Which seems to import the text file nicely.