Convert Hierarchial Report to Access Table

  • Thread starter Thread starter banker123
  • Start date Start date
B

banker123

I need to append the header record to the detail records in a
hierarchical report to create a database table. I was hoping their was


a command line tool that may assist.
Thanks!


Input:
Header 1
Detail 1
Detail 2
Detail 3


Header 2
Detail 1
Detail 2


Output:
Header 1 Detail 1
Header 1 Detail 2
Header 1 Detail 3


Header 2 Detail 1
Header 2 Detail 2
 
I can propose two ways to do it, and the choice depends on the actual
form of the input file (which I understand is a text file?) and your VBA
skills.

The first option is to link the external file twice, making two
different link specifications, one tailored on the header lines and one
on the detail lines. This will result in two linked tables, each of
which will have useful rows (header and detail respectively), and
rubbish (detail in the header link and vice-versa). The next step is to
make a query on each, to filter out the rubbish rows (surely there will
be some field to filter on). Then you can either use those directly in a
query to get the desired result, or make your queries make0table or
append, to put the data in Access tables to work with.

The second option comprises manual making of two tables for the header
and detail records, and then writing a VBA procedure which will open the
two tables as recordsets, read the text file line by line, decide what
each line is (header or detail) based on some pattern in the file, and
split the line accordingly and store it in the pertinent table by means
of a recordset operation.

HTH,
Nikos
 
I am interested in the first option, when combining the two tables,
that is the header and detail table how do you associate the header
record with its proper detail records without a primary key?
 
Well, this option assumes there is some common reference in both the
header and the detail lines, which you can use to associate. If not I'm
afraid you're out of luck... you are only left with the second option,
whereby you additionally need to create the reference (PK field in the
header table -> FK field in the detail table).

Nikos
 
Back
Top