Supplemental BIM Database Porn Part 2, Reporting Multiple Categories Together

This is part 2 of the highly anticipated and exciting mini series on supplemental BIM database design!

This post will focus on how data from two entirely different tables can be UNION queried together to aggregate counts and cost SUM's in a single report. The really cool part about how this works is that the fields don't have to match names or even data types between the two tables... I know, SUPER EXCITING~!!
The examples in this mini series have been built in MS Access 2007 in case you haven't noticed but could very easily be used with minor adjustments on SQL or my new favorite PostgreSQL.

A quick snapshot showing the 5 tables that we'll be working with is shown below. The type properties are in the category tables prefixed with "type" and the instance properties in the tables prefixed with "inst"...

UNION queries can only be built in MS Access 2007 using pure SQL syntax. This shouldn't be any big deal at all to a seasoned SQL DB Admin. To create a UNION query in MS Access 2007, click the "UNION" button in the query Design tab of the ribbon.

Union queries will show up in your MS Access 2007 Object Browser with a Union icon as shown below.

It is important to note that while the data types and names of the fields in each table can be entirely different when used in a UNION query, the quantity selected between each table must be equal. Select 5 fields in one table, then you better select 5 in the other. There is no limit to the amount of UNION queries you can run together, just remember to select the same quantity of fields in each of your table selections.


Another super important key is the order of the fields in each of your selection queries. That is to say that the first field in the first selection will list directly with the first field of the second selection query and so on, make sense? Great!


So what would the query look like? The example below shows the selection of all "Furniture" and "Furniture Systems" elements from four tables named "inst Furniture", "type Furniture", "inst Furniture Systems" and "type Furniture Systems." Each category holds a relationship between two tables for instance and type properties respectively. Yet another relationship to complicate matters further but also show the flexibility of such a query is the "ProjectInfo" table that holds the project information useful in populating report headers and footers.

The resulting query results (partial) looks like this (Quantity and TotalCost are aggregated across the two UNION queries in totally separate table groups):