CODEWITHSUNDEEP
sqlms-access
BeardedSith
BeardedSith

Reputation: 129

Two tables - SELECT TOP from one of the tables for each row in second table

I have a report which uses two tables.

tblContacts
tblCalls
I want to pull the TOP 1 from tblCalls for each row in tblContacts. I'm not entirely sure how to go about doing this. I tried to use JOINS but the only way that logically makes sense to me is where I get an error fired back telling me it's an ambiguous OUTER JOIN. So I tried to make two different queries to accomplish this, and in the end, it still only pulled TOP 1 for BOTH tables, so I'm not entirely sure where I'm going wrong.

I've tried a number of different SQL statements, and even though it doesn't work, this is the "closest" I've come thus far (The TOP 1 isn't working at all):

SELECT tblCalls.ID, tblCalls.[Call Time], tblCalls.NextContact, tblCalls.Subject, tblCalls.Notes, [First Name] & " " & [Last Name] AS FullName, tblContacts.[Mobile Phone], tblContacts.[Home Phone], tblContacts.[E-mail Address]
FROM tblCalls INNER JOIN tblContacts ON tblCalls.Contact = tblContacts.ID
GROUP BY tblCalls.ID, tblCalls.[Call Time], tblCalls.NextContact, tblCalls.Subject, tblCalls.Notes, [First Name] & " " & [Last Name], tblContacts.[Mobile Phone], tblContacts.[Home Phone], tblContacts.[E-mail Address];

This shows all the Call records in tblCalls. When I adjust the query to show TOP 1, it obviously only shows one record in total.

Additionally, just in case this becomes a problem down the road, I adding an "Archive" column to tblContacts and those records designated as "True" won't be listed on this report at all. This part should be easy, but just in case it's something that might break something later on, I figured I'd add it to the question.

Thanks!

Upvotes: 0

Views: 78

Answers (1)

forpas
forpas

Reputation: 164089

One way to get the last call for each contact is with NOT EXISTS:

SELECT c.* FROM tblCalls c
WHERE NOT EXISTS(
  SELECT 1 FROM tblCalls cc
  WHERE cc.Contact = c.Contact AND cc.[Call Time] > c.[Call Time]
)

So you can join it to the table:

SELECT
  c.ID, c.[Call Time], c.NextContact, c.Subject, c.Notes, 
  t.[First Name] & " " & t.[Last Name] AS FullName, 
  t.[Mobile Phone], t.[Home Phone], t.[E-mail Address] 
FROM tblContacts t LEFT JOIN (
  SELECT c.* FROM tblCalls c
  WHERE NOT EXISTS(
    SELECT 1 FROM tblCalls cc
    WHERE cc.Contact = c.Contact AND cc.[Call Time] > c.[Call Time]
  )
) c ON c.Contact = t.ID

Upvotes: 1

Related Questions