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!

 

[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