Propogate data between records

[Guest]

I have been given a file that comes from a Web function. It has two records
for each person and looks something like this.

First name Last Name Old Address
New Address

What I am trying to do is have one record that looks like this.

First Name Last Name Old Address New Address

I believe I could get this done if I can figure out how to copy the first
and last name from the first record to the second record but I cannot get it
to copy down. Of course there are thousands of these 'sets' of records to
work with.

Any ideas would be appreciated.

Thanks for your help.

 

[louisjohnphillips]

I have been given a file that comes from a Web function. It has two records
for each person and looks something like this.

First name Last Name Old Address
New Address

What I am trying to do is have one record that looks like this.

First Name Last Name Old Address New Address

I believe I could get this done if I can figure out how to copy the first
and last name from the first record to the second record but I cannot get it
to copy down. Of course there are thousands of these 'sets' of records to
work with.

Any ideas would be appreciated.

Thanks for your help.

Could you use VBA to create a tab-delimited text file which later
could be loaded through the Get External Data menu option?

Sub main()

Dim sFirstLine As String
Dim sSecondLine As String

Open "WebSourceFile" For Input As #1
Open "OutputTextFile" For Output As #2

Line Input #1, sFirstLine
Line Input #1, sSecondLine

While Not EOF(1)

Print #2, sFirstLine & Chr(9) & sSecondLine

Line Input #1, sFirstLine
Line Input #1, sSecondLine

Wend

Close all

End Sub

 

[Larry Linson]

Assuming that the sequence of the records determines to whom the "new
address" belongs, this would not be a big problem to to in VBA code... you
would only have a problem if the addresses are freeform and may exceed 255
bytes. In that case, you would either have to define those Fields in the
Table as Memo or you would have to attempt to parse the addresses into
component parts before storing in the Record in the Table. For purposes of
the example, I am going to assume that the old address and the new address
are both less than 256 bytes or that you've defined them as Memo, and that
the fields in the file record begin in the same byte in each record (e.g.,
it is a fixed-field text file)

First, create the Table (I call it "tblOldAndNew" in the example) with the
Fields you want (I called them FirstName, LastName, OldAddress, and
NewAddress) in the record. In a standard module, or the module associated
with a form from which you are going to kick off the operation:

Dim intFile as Integer 'File number used by BASIC to read
Dim strRecord as String 'Buffer into which to read the Record
Dim db as DAO.Database
Dim rs as DAO.Recordset
CONST FNPos as Integer = <you insert the position of the FirstName>
CONST FNLen as Integer = <you insert the max length of the FirstName>
CONST LNPos as Integer = <you insert the position of the LastName>
CONST LNLen as Integer = <you insert the max length of the LastName>
CONST AddrPos as Integer = <you insert the position of the Address>
CONST AddrLen as Integer = <you insert the max length of the Address>
Set db = CurrentDB 'Set database to current
database
Set rs = db.OpenRecordset ("tblOldAndNew") 'Open the table you defined
intFile = FreeFile 'Get file number from
System
Open "<youinsertpathandfile>" For Input As intFile
Line Input #intFile, strRecord 'Read entire first line from
File (firstname,
'lastname,
oldaddress)
Do Until EOF(intFile) 'Process entire file
rs.AddNew
rs("FirstName") = Mid(strRecord,FNPos,FNLen)
rs("LastName") = Mid(strRecord,LNPos,LNLen)
rs("OldAddress") = Mid(strRecord,AddrPos,AddrLen)
Line Input #intFile, strRecord 'Read second line from File
(new address)
rs("NewAddress") = Mid(strRecord,AddrPos,AddrLen)
rs.Update 'Add the new Record to
the Table
Line Input #intFile, strRecord 'Read entire first line of next
Record (next
'person) from
File
Loop 'End of Do loop
Close intFile
rs.Close 'Close Recordset opened on Table
Set rs = Nothing 'Release memory used by recordset
object
Set db = Nothing 'Release memory used by database object

This, as you may guess, is "aircode", completely untested... and there may
be some detail slipups, but I have written a lot of code to read files and
populate tables, so the approach is correct. Now, don't forget... I said if
either address exceeds 255 bytes, that Field in the Table will have to be
defined as Memo, or you'll have to define the component Fields (such as
Street Address, etc.).

Good luck with your application.

Larry Linson
Microsoft Access MVP

 

[Larry Daugherty]

It's impossible to understand what's going on with the limited
information you've given.

That the data comes 'from a web function' may be interesting trivia.
More to the point is what is the over all business objective you're
trying to achieve? What tool(s) are you currently using to view it in
order to characterize it as you do? Can you get the data into some
standardized format such as CSV?

From a far, far distance, it seems that you might be looking at the
result of normalized data wherein Person(s) are in one table and
Address(es) are in another, related, table. That is probably the
organization you will eventually decide that you want.

Post back with specific information that will help us to help you.

HTH

 

[Guest]

WOW. Larry, that is a lot of code that I'm certain would work but,
unfortunately, I only know a small bit of VBA code. I was kind of hoping for
an Access solution but it does not appear to be one. Thank you for the
incredible program.

Larry D.

I was given this task.

Bring in this data that is entered in a freeform text on the web. I can
import this data in several options but excel is probably the best because of
these reasons.
1. There are international addresses mixed in with normal US addresses.
2. A comma delimited file does not always put the data in the same position
because of the difference in addresses.
3. Since this data is entered by the public and it is freeform, the only
way I can keep an address together is have it in a single cell. This works
fine but the new address line does not have the name in it. The name is only
on the 1st line of the 'set' of records.

As I tried to explain earlier, The data comes in pairs of records. These
records have a designation of 'old' and 'new' in the first field, then the
first and last name in two different cells. This is followed by the address.
In the 'old' record, you have 'old', first name, last name, old address. In
the 'new' record, you have 'new', blank, blank, and new address. I need to
get it into one record containing first name, last name, old address, and new
address.

Thank you for your assistance.

 

[Guest]

Louis,

I might be able to do it this way but would rather stay away from a
delimited file of any kind. The addresses are entered by the public on a web
function and it is just an open field with no formatting at all. I am
charged with taking whatever garbage they have entered and spit it out on a
file that is sent to a mail merge program. The only format I can find in the
download that will keep the entire address together is Excel. When I bring
it in as an excel file, it divides the line into old and new lines and the
name does not propagate. Ideas?

 

[Guest]

Larry,

You are correct in your first assumption. The problem is, don't laugh, I
have never had a class in Access or VBA code so, while I am sure your code
would work, I have no idea where to place it or how to execute it.

The second assumption that everything begins on the same byte is somewhat
correct. The address does start on the same byte in each record but within
the address, there is no such limitation.

What I was hopeful for was maybe a series of queries/table builds to somehow
join the data together.

Am I too far out in left field?