Matching records

  • Thread starter Thread starter gary
  • Start date Start date
G

gary

(I have NOT used ANY version of Access before).

I have two text files:

FILE-1 has 2,500 rows, each containing a nine-digit number.
Examples:
391300004
453140065
672260027


FILE-2 has 2 million rows, each containing a nine-digit number
followed by one or two different nine-digit numbers.
Examples:
391300004391140021
391300004391140021391411001
391300004391140021391411002
453140065453741001
453140065453741001460191001
453140065453741002
453140065453741002460191002
453140065453741003
672260014672260046672822058
672260014672260046672822059
672260017672260041
672260017672260041673020006
672260017672260042
672260017672260042672260047
672260027672260046
672260027672260046672811001
672260027672260046672811002

I need to extract the row(s) in FILE-2 whose first nine-digit number
matches the nine-digit number in FILE-1.

Can someone provide step-by-step instructions on how I can do this
using Access 2007?
 
Questions.
Are numbers the first column in the file?

Do any have leading zeros that must be maintained? If leading zeros you'll
have to handle this with some text fields.

Is there other data in the rows you need imported? If so are the text files
deliminated with commas or spaces. If not you'll need to create a import
specification.

First you import the files into access tables. Then you will make a query
to collect the rows that match and create your new table. Read help file on
import

Left(File2Field1, 9) = File1Field1 although it won't look just like shown as
you will use your table and field names in the query grid. Read help file
on make table query

Post back as you get stuck.
 
Yes, the numbers are in the first column in both files.

No, there are no leading zeroes.

In FILE-2, except for the 2 or 3 nine-digit numbers, there is no other
data in the rows.

How do I "create an import specification"?

Is is correct to import each file in a "fixed width" format and, in
"Field Options", the "Data Type" is "Text" and the "Indexed" = "no"?

Do I check "Let Access add a primary key", the "Choose my own key" or
"No primary Key"?

How do I "make a query to collect the rows that match and create my
new table"?
 
If your going to do this once fixed width will probally work but field
formats may not be what you want. After you define the fixed field length
you can click on advanced and define the data types and then click save for
the specification. These are under file, get external data, import. Once
tables are created you will create query, add the two tables, decide what
fields you want in your new table from table 2 and set a criteria for the
comparison. Run it as a select query and see if it is what you want. If so
change query to to create table and you'll have what you asked for. Then it
is a matter of knowing what is the end product you want from it.

As it sounds like you have duplicates during the import I would let access
add the key. You can change this later if needed.
 
OK, I now have two tables.

Now, how do I:

1) create the query
2) add the two tables
3) decide what fields I want in my new table from table 2
4) set a criteria for the comparison
5) run it as a select query.
 
OK, I now have two databases:

file-1.accdb:
Table 'asmntnbr-only' (imported from FILE-1.txt)
has one field ("unpaid") containing the nine-digit numbers.

file-2.accdb:
Table "parcel-changes" (imported from FILE-2.txt)
has three fields:
"old" containing the first nine-digit number
"new-1" containing the second nine-digit number
"new-2" containing the third nine-digit number (if any)

Now, how do I:

1) create query
2) add the two tables
3) decide what fields I want in my new table from table 2
4) set a criteria for the comparison
5) run it as a select query
 
Back
Top