Query where every record in one Table is listed

[Bill F]

I have two tables Apartments and Occupancy and I am trying to create a query
that will pull the current Tenant from the Occupancy Table based on an
inquiry date (I know how to do that part) but I also want the query to list
the Apartment from the Apartments Table even if it does not have a "current"
tenant. I can only get the query to return the "occupied" apartments. Can
someone help me?

Thanks.

 

[Allen Browne]

Create the query that just returns the currenct occupancy for each
apartment. Save as (say) qryOccupancyCurrent.

Now create another query using Apartments and qryOccupancyCurrent as input
'tables', joined on ApartmentID. Right-click the line joining the 2 tables
in the upper pane of query design. Access pops up a dialog with 3 options.
Choose:
All records from Apartments, and any matches from qryOccupancyCurrent.
Technically that's known as an outer join. More into:
http://allenbrowne.com/casu-02.html

If you want to do it all in one query, this might help:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html