RE: Matching Type Query
- From: SteveS <sanfu at techie dot com>
- Date: Mon, 4 Jul 2005 15:39:02 -0700
"Jackson via AccessMonster.com" wrote:
> 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.
>
> --
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
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
.
- References:
- Matching Type Query
- From: Jackson via AccessMonster.com
- Matching Type Query
- Prev by Date: UPDATE xx SET xx = (SELECT subquery) -- not updateable
- Next by Date: Re: Updating table based on results of query
- Previous by thread: Matching Type Query
- Next by thread: RE: How do I query multiple rows into a single string in Access?
- Index(es):
Relevant Pages
|
