Help with subqueries

Hello. Here is my issue.

Table A:
ID Name
1 Vance
2 Kasey
3 Alyssa

Table B:
ID IDA Image
1 1 Vance1.jpg
2 1 Vance2.jpg
3 2 Kasey.jpg
4 2 Kasey2.jpg

Tables connected by A.ID = B.IDA

I want to show a list of ALL names from table A, and their corresponding image, however, only the image with the max B.ID number.

For instance:
Name ID Image
Vance 2 Vance2.jpg
Kasey 4 Kasey2.jpg
Alyssa

I am assuming you use subqueries, but cannot figure it out...thanks for any help.

Just place the criteria under your tableB ID field using the DMax Function

DMax("[ID]","TableB")

Make sure TableA has your first criteria or sort, then TableB with your second criteria.

lwells

Sorry didn't read throughly...you wanted to display all names and then the associated picture with each name from TableB
Subqueries will only let you return one record at a time.

Right...so I don't know how to do it without subqueries.

Well, the only way I know how to do what you want is through vba code by creating a function. This function would create the sql for your query with the criteria that you want. The following example function can be run from a command button that would build this type of sql:

Function GetRecentPhoto()
On Error GoTo Err_Handler
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim strSQL As String, strSQL1 As String
Dim strCriteria As String

strSQL = "SELECT TableA.* FROM TableA;"

Set db = CurrentDb()
Set qdf = db.QueryDefs("QueryName")
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
Do Until rs.EOF
strSQL1 = "SELECT TableB.* From TableB " & _
"WHERE ID = " & DMax("[ID]", "TableB", "[IDA]=" & rs!ID)
Set rs1 = db.OpenRecordset(strSQL1, dbOpenSnapshot)
strCriteria = strCriteria & "TableB.ID =" & rs1!ID & " OR "
.MoveNext
Loop
End With

strCriteria = Left(strCriteria, Len(strCriteria) - 3)

qdf.SQL = "SELECT TableA.*, TableB.* " & _
"FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.IDA " & _
"WHERE " & strCriteria

Set db = Nothing
Set qdf = Nothing
Set rs = Nothing
Set rs1 = Nothing
Exit_Handler:
Exit Function
Err_Handler:
MsgBox Err.Description & " " & Err.Number
Resume Exit_Handler
End Function

This function however requires a matching record in TableB for each Record in TableA or you will receive an error message. The photo can be missing, but there will have to be an IDA number in TableB for each ID in TableA.

Hopefully this will give you some ideas.
lwells