Compare a list of values in ASCII file to a column in Access table

[Mark]

I'm working with ASCII data files provided by data vendors in a
standard format. These files contains lots of various pieces of
information for each reporting entity in the file.

Currently I have code to read these ASCII files and arrange the data
into a managable table structure. There is a key field unique to each
entity called PROPNUM.

The workflow is such that one ASCII file (file1.txt) is loaded into
this system. I have a data loading MS-Access database (loader.mdb)
that puts data from the ASCII file into another Access database
(target.mdb). Target.mdb contains a table named Master with the
PROPNUM key field.

I need to develop a routine that checks new ASCII datafiles (e.g.,
file2.txt) for PROPNUMs that are already in Target.Master.

I've started developing this routine in IsItInTarget.mdb. I've got
PROPNUMs from file2.txt loaded into a table in IsItInTarget.mdb. How
can I search for the presence of each PROPNUM in Target.mdb and report
them.

One simple idea is to use a MsgBox statement to report each occurence,
but someone may have a better idea.

Appreciate any suggestions.

Thanks, Mark

[Phil Stanton]

Why not see if the "new queries" "find duplicates wizard helps"

Phil
"Mark" <mr****@ogclearinghouse.com> wrote in message
news:9b**************************@posting.google.c om...

I'm working with ASCII data files provided by data vendors in a
standard format. These files contains lots of various pieces of
information for each reporting entity in the file.

Currently I have code to read these ASCII files and arrange the data
into a managable table structure. There is a key field unique to each
entity called PROPNUM.

The workflow is such that one ASCII file (file1.txt) is loaded into
this system. I have a data loading MS-Access database (loader.mdb)
that puts data from the ASCII file into another Access database
(target.mdb). Target.mdb contains a table named Master with the
PROPNUM key field.

I need to develop a routine that checks new ASCII datafiles (e.g.,
file2.txt) for PROPNUMs that are already in Target.Master.

I've started developing this routine in IsItInTarget.mdb. I've got
PROPNUMs from file2.txt loaded into a table in IsItInTarget.mdb. How
can I search for the presence of each PROPNUM in Target.mdb and report
them.

One simple idea is to use a MsgBox statement to report each occurence,
but someone may have a better idea.

Appreciate any suggestions.

Thanks, Mark

[Mark Roach]

Thanks Phil. It looks like the Find Duplicates query checks for more
than one occurance in a [table].[field]. In this case, there are two
tables involved. One table contains a list of PROPNUMs from the ASCII
file and the second table contains a list of PROPNUMs that have already
been loaded.

Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Phil Stanton]

OK Mark

Try linking Table Master in Target.Mdb into IsItIntarget.mdb. I am asseming
that the new PROPNUMs are in a table called something line NewMaster
Then create a simple query based creating a query based on Master and
NewMaster and join the 2 PROPNUM fields. All the results in this query will
be where the 2 PROPMUMs are the same

HTH

Phil

"Mark Roach" <mr****@ogclearinghouse.com> wrote in message
news:41*********************@news.newsgroups.ws...

Thanks Phil. It looks like the Find Duplicates query checks for more
than one occurance in a [table].[field]. In this case, there are two
tables involved. One table contains a list of PROPNUMs from the ASCII
file and the second table contains a list of PROPNUMs that have already
been loaded.

Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!