Distinguishing Name

[Guest]

Hi! I have a problem I've been trying to resolve for some time but don't know the best way to go about it

I have a text file that comes back from a municipality. It contains license plates, names, addresses, etc. I am able to automatically load the data and to split it into fields (it's fixed width, so not that hard). The file comes back with a type (A) for names, (B) for addresses, (C) for City, State, Zip on separate lines. Each type only includes the license plate to link them together

My problem is that the record types may contain 2 or more rows, for different vehicle owners or different addresses. Like this
A123ABC John Smith
A123ABC Delores Smit
B123ABC 1313 Mocking Bird Lan
B123ABC PO BOX 455
C123ABC Concord CA 9172

I'd like to be able to combine the data to look like this
License FName LName Primary Owner? Address1 Address2 City State Zi
123ABC John Smith YES PO BOX 4554 1313 Mocking... Concord CA 9172
123ABC Delores Smith NO PO BOX 4554 1313 Mocking... Concord CA 9172

Can anyone help

Thank you
Cowdog Ga

 

[TC]

Gak! Here's what I'd do.

Have a seperate table for each record type:
tblA
PlateNumber text(whatever)
OwnerName text(255)
tblB, tblC similar.

Put a unique index on the PlateNumber field of each table.

Now sort the data text file so the As come first, then the Bs, then the Cs.
Split the file into three parts, so you can load the A records into tblA,
the Bs into tblB, and the Cs into tblC. Note you do not need to load the
record type values ("A", "B" and "C"), since the record type is implied by
the table that the record is in. You could probably write a Word macro to
automate sorting & splitting the data text file.

Now go in to the query designer & create a new query. Add the 3 tables to
the query. Join the three PlateNumber fields. Double-click each join line,
and select "outer join" (or whatever it says - I don't have Access here to
check). Select the option that says to show each record even if it does not
have a matching record in the other tables.

Now, running that query will give you an accumulated view of the data
present for each Plate Number. You could save the results of the query into
a new table, then perform any cleanups required; eg. splitting the
OwenerName field into seperate forname & surname fields to facilitate
sorting etc.

HTH,
TC

Hi! I have a problem I've been trying to resolve for some time but don't

know the best way to go about it.

I have a text file that comes back from a municipality. It contains

license plates, names, addresses, etc. I am able to automatically load the
data and to split it into fields (it's fixed width, so not that hard). The
file comes back with a type (A) for names, (B) for addresses, (C) for City,
State, Zip on separate lines. Each type only includes the license plate to
link them together.

My problem is that the record types may contain 2 or more rows, for

different vehicle owners or different addresses. Like this:

A123ABC John Smith
A123ABC Delores Smith
B123ABC 1313 Mocking Bird Lane
B123ABC PO BOX 4554
C123ABC Concord CA 91723

I'd like to be able to combine the data to look like this:
License FName LName Primary Owner? Address1 Address2 City State Zip
123ABC John Smith YES PO BOX 4554

1313 Mocking... Concord CA 91723

123ABC Delores Smith NO PO BOX 4554 1313

Mocking... Concord CA 91723