Left Join to Huge Table with Duplicates

[Kenny]

Table1
Account#
1
2
3

Table2
ID# Account# Field1 Field2 Field3 ETC
1 1
2 1
3 1
4 2
5 3
6 3
7 2
8 1

These seems simple enough but I cannot figure out how to do this quickley. I
tried an agregated sql but it took forever as table 2 has about 50000 records
with duplicates. All I want to do is create a query that will list all of
table1's account numbers and then join all of table2's information on the
account number matching AND ID# being the max or largest.

Anything I have tried takes about 30 mins - please help!

 

[MGFoster]

Table1
Account#
1
2
3

Table2
ID# Account# Field1 Field2 Field3 ETC
1 1
2 1
3 1
4 2
5 3
6 3
7 2
8 1

These seems simple enough but I cannot figure out how to do this quickley. I
tried an agregated sql but it took forever as table 2 has about 50000 records
with duplicates. All I want to do is create a query that will list all of
table1's account numbers and then join all of table2's information on the
account number matching AND ID# being the max or largest.

Anything I have tried takes about 30 mins - please help!

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

For speed increases make sure you have an index on the Account# and
possibly the ID#. Are you LEFT JOINing?

SELECT T1.Account#, MAX(T2.ID#) As LargestID
FROM table1 As T1 LEFT JOIN table2 As T2 ON T1.Account# = T2.Account#
WHERE < your criteria >
GROUP BY T1.Account#

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSe/yxYechKqOuFEgEQK9WwCfdbktJLNdWSS2cb7xAYrlGOhdg4AAoLI/
uGmx+8g8eskbALGY6J6p/YLb
=EBAZ
-----END PGP SIGNATURE-----

 

[John Spencer MVP]

Two query solution

First, you need a saved query (call it qMaxID):
SELECT Max([ID#]) as MaxID, [Account#]
FROM Table2
GROUP BY [Account#]

Second query uses the saved query.
SELECT Table2.*
FROM Table2 INNER JOIN qMaxID
ON Table2.[Id#] = qMaxID.MaxID
AND Table2.[Account#] = qMaxID.[Account#]
WHERE Table2.[Account#] in
(SELECT [Account#]
FROM Table1)

This could also be written as
SELECT Table2.*
FROM (Table2 INNER JOIN qMaxID
ON Table2.[Id#] = qMaxID.MaxID
AND Table2.[Account#] = qMaxID.[Account#])
INNER JOIN Table1
ON Table2.[Account#] = Table1.[Account#]

And if your field and table names consisted of only letters, numbers, and the
underscore character you could write one query to accomplish this.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County