MS Access: Display A Subreport Even When There Are No Records

Seems like a lot of people are having a problem because Access automatically hides a subreport if it contains no records. Ref: PC Review, Experts Exchange, ASPFree.

After digging through the various report and widget properties, there appears to be no property that will automatically display the subreport if there are no records.

The way I finally got a subreport to display was to create a query which returns all the records, and also returns “blank” rows for nonexistent records.

In this example, we have three tables: Orgs, People, and Positions. Positions is a table that has OrgID and PeopleID columns, and joins the other two tables. Positions has a column “HasRecord” which is a boolean that indicates that the person has a record. Positions contains not only the related records of interest, but other records as well; HasRecord indicates that this is a record we’re looking for.

This query will get you the list of all the orgs, with additional columns where there is a matching record in Positions where HasRecord is true.

SELECT * FROM
( SELECT OrgID FROM Orgs ) a
LEFT JOIN
( SELECT OrgID, PeopleID FROM Positions WHERE HasRecord=TRUE ) b
ON a.OrgID=b.OrgID

That gets you your result set, and you can then add some columns with a regular join:

SELECT Name, Address, Title FROM
( SELECT * FROM
   ( SELECT OrgID FROM Orgs ) a
   LEFT JOIN
   ( SELECT OrgID, PeopleID FROM Positions WHERE HasRecord=TRUE ) b
   ON a.OrgID=b.OrgID
) c
INNER JOIN
People p
ON c.PeopleID=p.PeopleID

What didn’t work

One commenter suggested using a UNION query to add blank rows to the result. I tried doing this:

SELECT OrgID, Null FROM Orgs
UNION
SELECT OrgID, PeopleID FROM Positions WHERE HasRecord=TRUE 

This didn’t work because you’d end up with a blank line before records that exist

Another possibility was to do a LEFT JOIN between Orgs and Positions, and match not only on HasRecord=TRUE but even when it’s NULL.

SELECT * FROM Orgs LEFT JOIN Positions ON Orgs.OrgID=Positions.OrgID 
WHERE HasRecord=True OR HasRecord IS NULL

That doesn’t work because HasRecord, if it exists, is either True or False. So False is not included. This means if a related record exists in Positions, but is not HasRecord=True, then, an OrgID for that organization won’t show up in the results. If we include “OR HasRecord=False” to the statement, we end up selecting everything, including records we don’t want. It just doesn’t work.