Top Value to obtain Top Child for each Parent

[Ken Cobler]

I am having trouble getting what I need from a query.

For each of my Parent records (buildings), I want to know the largest tenant
(child).

I have a query for my child table which returns the top record based on
square feet. But when I try to use another query to match up each of my
buildings with the top tenant for each building, I do not get the correct
answer. (I am only seeing the top tenant of ALL my buildings - - - .)

I'm new to queries. Please advise. Thank you.

SQL view below

 

[John Spencer]

So is square feet stored in the tenant table?

Build a query to get the Max square footage for each building using the
tenants table. Save that as qBigGuy

SELECT BuildingID, Max(SqFoot) as BigFoot
FROM Tenants

Then use that in a second query to get the details on the tenant

SELECT T.*
FROM Tenants as T INNER JOIN qBigGuy
ON Tenants.BuildingID = qBigGuy.BuildingID
AND Tenants.SqFoot = qBigGuy.BigFoot

You can add the Building table into that query if you wish.