Numbering / Autonumbering (1 Viewer)

[Stevrino]

I have a table I need to generate from a file I receive.
The file contains many records.

The first record will be a header record
The second record will be Detail record
The third will be information.
Then the next group of records will start, and so on.

I will populate 3 tables, Header, Detail, and Information.

The problem I have is if there is more data than 1 for each of the above.
Example.
You will have 1 header record.
3 Detail records
3 Information records

The Information records have a field stipulating Line 1, Line 2, Line 3 and they may show in any order.
The detail records do not have this field however they are in the proper order. Line 1 then line 2 then line 3.

How can I created a field in the table to stipulate which line it is so I can link it back to the information table.
I would then need to restart the numbering for the next record group.

Have I been clear enough with this explanation?

Stevrino

 

[The_Doc_Man]

The good news is, it is possible to do what you want. The bad news is, you cannot do it simply. You will need to do one of:

A - Externally break the input data into three separate streams, one for each table. I.e. split apart the records before you give them to Access. Use the Import wizard to accomplish the actual import steps, once for each table.

B - Write some VBA code. Write a macro that can do a RunCode. Inside the code: Open the source file, split the information into three separate output file streams, then import the files separately using the Import wizards. One import for each of the separated streams.

C - Write some VBA code that opens recordsets. Write the macro to do a RunCode. Inside the code, directly import the data as its comes in. Do this by opening and reading the source file one record at a time. As you identify each new record, classify it as to which table it references. Add a new record to the appropriate recordset, populate the record, update the recordset. Keep behind any data from the header record that you would need to link the associated detail or informational records.

Method C requires you to do some fancy VBA string parsing, in all likelihood, and not all persons can do that.

Unfortunately, unless some things have changed drastically with Access in the last few months, there is no direct way to import data to be spread unevenly among three tables from one data source. The Import wizard will just barf and go away.