Reposted with a longer line length. Apologies if I did not cancel the 1st attempt before you got
it.
If necessary, please view in a text editor so the columnar text lines up. I used Terminal 9pt font
to compose this post and copied/pasted to my newsreader program.
I am writing in the hope that one of you Visual Basic experts may provide a solution, or at least
some insight, to the problem described below. I am not a programmer, but am fairly knowledgeable
about computers. If necessary, I could flowchart a process to do what needs to be done.
Unfortunately, I would not know how to go about coding it in a VB script. Maybe I'll get lucky and
find someone through this post that has had a similar experience importing third party data. . .
The scenario:
A company we contract to provide centralized medical billing for our "clients" allows review of
individual claims via their internet site. My goal is to import their data about our business to an
MS Access database, which I could then relate to internal data about our patients.
At my request, they have created a custom report that exports all related patient records to a
tab-delimited file, which I have subsequently imported to Access. My problem stems from the way
data is maintained in their system (I am told they use Lotus Notes) wherein multiple dates, medical
codes and related charges may be contained in a single data field. Data in their exported file has
this general format:
Key Claim# Patient Begin Date End Date Procedure Code Billed Charge
1 1 Doe, John 1/1/04 1/1/04 555.1 $198.00
2 2 Moss, Pete 1/1/04 1/2/04 1/1/04 1/2/04 101.1 203.6 $500.00 $375.00
For the sake of brevity, I have limited my example to 2 records. In practice, all records contain
at least one entry per field, but may contain multiple entries. The actual file contains several
fields in addition to those listed.
In order to make the data useful, I need to find a way to expand each record with multiple entries
to new records. Claim# 2 in the example above would then become:
Key Claim# Patient Begin Date End Date Procedure Code Billed Charge
1 1 Doe, John 1/1/04 1/1/04 555.1 $198.00
2 2a Moss, Pete 1/1/04 1/1/04 101.1 $500.00
3 2b Moss, Pete 1/2/04 1/2/04 203.6 $375.00
Please post a follow up message if you may be able to help. The email address listed in this
message is not valid.