On MS Access applications, usually I use subreport objects with SQL queries of their own, linked to the main report which includes the subreport. Classic example of such master-detail reports is invoices:

+ master (main report) section includes invoice date, invoice number and details of the customer account such as name and address lines.
+ detail (subreport) section includes the products and services charged, product code, desription, quantity, price, and line total etc.

Another example could be statement of share transactions of an employee within a date interval.

+ employee record (name, surname, NI number etc) on main section
+ share transaction list in a subreport underneath the employee record

The problem: When the subreport has no data, MS Access does not show it at all. I may want to see the column headings, at least, or a message with it.

The Solution: We can modify the SQL query (record source) of the subreport and make sure that it has data every time it is run.

Let’s say the SQL query of the main report is something like this:

SELECT e.emp_surname , e.emp_name, e.payroll_number
FROM tbl_employee e
WHERE e.payroll_number = GetCurrentEmployeeNumber()

And the original SQL query for our subreport which brings the share transactions of employees is:

SELECT t.trans_date, t.quantity, t.price, t.description, t.payroll_number
FROM tbl_transaction t
INNER JOIN tbl_employee e
  ON t.payroll_number = e. payroll_number
WHERE e.payroll_number = GetCurrentEmployeeNumber()
  AND t.trans_date BETWEEN GetStartDate() AND GetEndDate()

We can convert it into a UNION query which will always produce data:

SELECT t.trans_date, t.quantity, t.price, t.description, t.payroll_number
FROM tbl_transaction t
INNER JOIN tbl_employee e
  ON t.payroll_number = e. payroll_number
WHERE e.payroll_number = GetCurrentEmployeeNumber()
  AND t.trans_date BETWEEN GetStartDate() AND GetEndDate()

UNION

SELECT TOP 1
  NULL AS trans_date, 0 AS quantity, 0 AS price, "N/A" AS description, e.payroll_number
FROM tbl_employee e2
WHERE e2.payroll_number = GetCurrentEmployeeNumber()
  AND e2.payroll_number NOT IN (
    SELECT t2.payroll_number
    FROM tbl_transaction AS t2
    WHERE t.trans_date BETWEEN GetStartDate() AND GetEndDate()
    GROUP BY t2.payroll_number
    HAVING COUNT(*)>0
);

This way, when there is at least one share transaction the subreport will be visible as usual, listing the transactions of the employee. If there are no transactions, the second part of the union query will have the information we want to show.

Note: In my SQL queries, I referred to my public functions placed inside a VBA module; GetCurrentEmployeeNumber(), GetStartDate(), GetEndDate(). That’s my way of collecting and holding the user inputs as criteria affecting my query results, using global variables; basically, setting them and getting them when required.

Tags: ,
Categories: