Query for lookup problem

[Sunj]

Hi,
I have follwing two tables (table1& table2) in ACCESS
Table1
A B C
10 20 1.4
20 30 1.5
30 40 1.7
40 50 1.8
50 60 1.9
60 70 2.1

Table2
D E F
20 10 2.4
40 20 3.4
60 30 4.4
40 50 5.1
50 60 6.1
70 30 8.1
I would like a table below which matches column A&B of
Table1 with D&E from table 2 giving me a matching C and F
column in O and P field.
M N O P
40 50 1.8 5.1
50 60 1.9 6.1
Any help in solving this lookup problem will be highly
appreciated. Thanks
Sunj.

 

[MGFoster]

Hi,
I have follwing two tables (table1& table2) in ACCESS
Table1
A B C
10 20 1.4
20 30 1.5
30 40 1.7
40 50 1.8
50 60 1.9
60 70 2.1

Table2
D E F
20 10 2.4
40 20 3.4
60 30 4.4
40 50 5.1
50 60 6.1
70 30 8.1
I would like a table below which matches column A&B of
Table1 with D&E from table 2 giving me a matching C and F
column in O and P field.
M N O P
40 50 1.8 5.1
50 60 1.9 6.1
Any help in solving this lookup problem will be highly
appreciated. Thanks
Sunj.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps, something like this:

SELECT A, B, MAX(C), MAX(F)
FROM table1 As T1 INNER JOIN table2 As T2
ON T1.A = T2.D AND T1.B = T2.E
GROUP BY A, B

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgfi1oechKqOuFEgEQKF8QCg0KQJxgv2rOvyL4DEFMS0guJRlP0AoNcu
U5gLWqLCOkdFxP3Ndendkl2X
=elkb
-----END PGP SIGNATURE-----