Matching Type Query

  • Thread starter Thread starter Jackson via AccessMonster.com
  • Start date Start date
J

Jackson via AccessMonster.com

I've got two sets of data, financial information imported into access from
two different sources. I basically want to check one version against the
other. Problem is, there is no real way to uniquely identify them (that I
know of). Table 1 has tons more information than table 2 which is more of a
daily sheet of information.

Fields include, two types of ccys (text), buy and sell amounts, trade dates,
value dates and exchange rates but nothing that you could use as a unique
identifier. Originally I was trying to put together a text string that used
some of the info I've put down above ie USD/EUR 30/06/05 01/07/05 1.215

And have that for each record as the unique identifier but it seems an
inefficient and inaccurate way of doing it. Does anyone have any ideas for
things like this, when you can't really get a primary key for the info but
all records in one table do match records in the larger table?

Cheers.
 
Jackson via AccessMonster.com said:
I've got two sets of data, financial information imported into access from
two different sources. I basically want to check one version against the
other. Problem is, there is no real way to uniquely identify them (that I
know of). Table 1 has tons more information than table 2 which is more of a
daily sheet of information.

Fields include, two types of ccys (text), buy and sell amounts, trade dates,
value dates and exchange rates but nothing that you could use as a unique
identifier. Originally I was trying to put together a text string that used
some of the info I've put down above ie USD/EUR 30/06/05 01/07/05 1.215

And have that for each record as the unique identifier but it seems an
inefficient and inaccurate way of doing it. Does anyone have any ideas for
things like this, when you can't really get a primary key for the info but
all records in one table do match records in the larger table?

Cheers.

--
Click to expand...

What are you wanting to do? Delete matching records from one table? Link the
two tables?

How many fields would it take to determine that two records "matched"?

Does Table2 (the small one) have unique records? So that you could use
Table2 as the One side and Table 1 as the Many side?

What you might do is run a "find duplicate" query the small table (Table2)
using all fields in the record so you end up with unique records.

Add a new field of type Number - Long Integer , name it something like
"lngRec_ID". Use code to step thru the records and number each record. Do a
Compact & Repair.

Change the field type for the new field ("lngRec_ID" or whatever you named
it) to an Autonumber. Do a Compact & Repair.

Add a new field to Table1 of type Number - Long Integer. This will be the FK
that will hold the PK from Table2.

Again using code, compare every record in Table1 to each record in Table2
using however many fields necessary to determine if the records match or are
equal. If they are, save the Table2 "lngRec_ID" to the new field (the FK) in
Table1.

So if there are 100 records in Table2, you would go thru Table2 one time and
Table1 100 times.

When that is done. you could run a query to find any records in Table1 where
the new field Is Null.

HTH
 
Back
Top