Re: ClarificationRE: VBA code to assemble data spread across sever

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

John, being relatively weak in VBA, no experience in Perl, and not having
success with Word (and needing to get this process as automated as possible
by tuesday morning) I posted a question in VBA Programming that would copy
values down a column until it hit the next column that was not null. There by
I would have a key that would link the data that was spread across the 4
rows. Whenever I attempted to provide an example, this news group would shift
the location "cardio" to a fifth row under the Patient Identifier. which is
not the case (and why I tried to post a couple of clarifications after the
fact)
So...while it was related to our original posting on Import/Export group, I
was asking for help for a VBA solution that I think I would be able to manage
given my level of experience. (ie...a button that would run code to copy the
Patient # and then loop until the end of the table)
If you look at the example I ask for VBA code that would go from the first
example to what I have in the second example... (From this...To This). The
secod example, where the Patient number is repeated across the rows is not
meant to indicate what the file looks like now..just where I want to go.
So...for example purposes for the easier VBA solution I thougt I could handle
, you can omit the Field 5 and the word Cardiology all together and take that
to mean what the current file looks like.
So, My VBA question example is going from this.....

Field1 Field2 Field3 Field4
M000999999 MORA,EDUARDO N M/30
07/05/05
1440
M000123456 GARCIA,HOPE J F/80
07/05/05
1000

To this........
Field1 Field2 Field3 Field4
M000999999 MORA,EDUARDO N M/30
M000999999 07/05/05
M000999999 1440
M000123456 GARCIA,HOPE J F/80
M000123456 07/05/05
M000123456 1000

Sorry for the confusion. Still would appreciate any suggestions.
Antonio




"John Nurick" wrote:

> Antonio, almost every message you have posted, both here and in
> ..externaldata, shows a different structure of the sample data. It now
> seems that the [Field 1] value (which you previously described as [ID])
> is repeated at the beginning of every line of a patient's record;
> previously you said it was only present on the first line of each
> patient's record.
>
> See my message today in .externaldata for the wildcard search patterns
> needed to do what I thought you wanted in Word. Meanwhile, I'm going to
> stop chasing a moving target.
>
> On Sun, 3 Jul 2005 02:48:01 -0700, "Antonio"
> <Antonio@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
> >In the clarification, the word Cardiology is actually under field 5...not in
> >field 1...
> >Sorry for the confusion.
> >Antonio
> >
> >"Antonio" wrote:
> >
> >> Sorry, the news group shifted the fields in my example...this is how it
> >> should look:
> >> Field1 Field2 Field3 Field4 Field5
> >> M000999999 MORA,EDUARDO N M/30
> >> 07/05/05
> >> 1440
> >>
> >> CARDIOLOGY
> >> M000123456 GARCIA,HOPE J F/80
> >> 07/05/05
> >> 1000
> >>
> >> CARDIOLOGY
> >> To This:
> >> Field1 Field2 Field3 Field4 Field5
> >> M000999999 MORA,EDUARDO N M/30
> >> M000999999 07/05/05
> >> M000999999 1440
> >> M000999999
> >> CARDIOLOGY
> >> M000123456 GARCIA,HOPE J F/80
> >> M000123456 07/05/05
> >> M000123456 1000
> >> M000123456
> >> CARDIOLOGY
> >>
> >>
> >> "Antonio" wrote:
> >>
> >> > I posted a question on 7/1/05 In the Access Import/Export Data group. One of
> >> > the three suggestions from a very kind person named John was to "write VBA
> >> > code to read the text file line by line, assemble records, and append them to
> >> > a table." But I am looking for example of such code (as I am new to the VBA
> >> > arena) Here is the problem, I have a linked table to a text file. When you
> >> > open the link the data displays like this:
> >> > Field1 Field2 Field3
> >> > Field4 Field5
> >> > M000443990 MORA,EDUARDO N M/30
> >> > 07/05/05
> >> >
> >> > 1440
> >> >
> >> > CARDIOLOGY
> >> > M000162333 GARCIA,HOPE J F/80
> >> > 07/05/05
> >> >
> >> > 1000
> >> >
> >> > CARDIOLOGY
> >> > The desired end result would be to bring all the data into one record by
> >> > Field1 and Field Two, like this.
> >> > Field1 Field2 Field3
> >> > Field4 Field5
> >> > M000999999 MORA,EDUARDO N M/30 07/05/05 1440 CARDIOLOGY
> >> > M000123456 GARCIA,HOPE J F/80 07/05/05 1000 CARDIOLOGY
> >> >
> >> > Can anyone give examples of such code? OR...since I am not terribly familiar
> >> > with VBA, can anyone suggest code that would copy Field1 into all Null fields
> >> > below it until it reaches a "Not Is Null" field, and then copy the data that
> >> > is in that field to all Null Field1 fields below it, and then again, until it
> >> > reaches the last record in the table? (I saw something in the knowledge base
> >> > about something to do with Looping Structures? perhaps?)... Example:
> >> > From this:
> >> > Field1 Field2 Field3
> >> > Field4 Field5
> >> > M000999999 MORA,EDUARDO N M/30
> >> > 07/05/05
> >> >
> >> > 1440
> >> >
> >> > CARDIOLOGY
> >> > M000123456 GARCIA,HOPE J F/80
> >> > 07/05/05
> >> >
> >> > 1000
> >> >
> >> > CARDIOLOGY
> >> > To This:
> >> > Field1 Field2 Field3
> >> > Field4 Field5
> >> > M000999999 MORA,EDUARDO N M/30
> >> > M000999999 07/05/05
> >> > M000999999 1440
> >> > M000999999
> >> > CARDIOLOGY
> >> > M000123456 GARCIA,HOPE J F/80
> >> > M000123456 07/05/05
> >> > M000123456 1000
> >> > M000123456
> >> > CARDIOLOGY
> >> > What I am thinking (hoping) is that with the same information across the
> >> > corresponding rows, I can build a query that will bring all the info together
> >> > where Field1 is the same. (field 1 is never repeated..its unique to the
> >> > person.)
> >> >
> >> > Any help would be Awsome!. Thank you.
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
.

Quantcast