| re: Count combination of fields in one table occurring in another table
S0ck3t wrote:
[color=blue]
>Please could I have some help on matching records between tables. I
>want to return a check (true/false) stating whether the field
>combination in table 1 occurs in table 2. Obviously it's easy with just
>one field, but two is more problematic.
>
>I have the following
>
>Table1
>--------
>field1
>field2
>
>Table1 Example values
> field1 field2
>Rec1: A 1
>Rec2: B 1
>Rec3: A 2
>
>Table2
>-------
>field1
>field2
>
>Table2 Example Values
> field1 field2
>Rec4: B 2
>Rec5: A 1
>
>
>E.g.
>Table1
> field1 field2 OccursInTable2
>Rec1: A 1 True
>Rec2: B 1 False
>Rec3: A 2 False
>
>Attempts I've made include an Exists (or an IN) subquery - very very
>slow. Combining the fields into their own primary Key (e.g. a field
>KEY: "field1.B+field2.1") and then matching on that - seems cumbersome.
>
>It's a dynamic query (not a once off). If you can answer this then is
>there a more generic way to extend to more than two fields?[/color]
Not sure what you mean by "dynamic query", but the fields to
be compared must be specified in the query. So, if you want
to specify the fields at the time you run the query, the
answer is no.
I'm pretty sure the fastest way to get the results in your
example is to use this kind of query:
SELECT table1.field1,
table1.field2,
Nz(table1.field1 = table2.field1, False) As InTable2
FROM table1 LEFT JOIN table2
ON table1.field1 = table2.field1
AND table1.field2 = table2.field2
If the speed of that is bothersome, you can try indexing
both field1 and field2 in both tables.
To do that for more fields, just extend the query in this
manner:
SELECT table1.field1,
table1.field2,
table1.field3,
Nz(table1.field1 = table2.field1, False) As InTable2
FROM table1 LEFT JOIN table2
ON table1.field1 = table2.field1
AND table1.field2 = table2.field2
AND table1.field3 = table2.field3
--
Marsh
| 
