AX2077
Using multiple tables in an Axiom query
It is important to structure your Axiom query appropriately so that the data results are as expected.
Including reference tables when the primary table is a data table
Axiom queries can include data from any reference table that the primary table has a lookup relationship with. By default, when a reference table is included in an Axiom query, it is joined to the data table by using an inner join.
For example, if the reference table column ACCT.ACCT is the assigned lookup column for the data table column GL2019.ACCT, then you can include any column from the ACCT table in the Axiom query. In this case, GL2019.ACCT is known as a validated column, ACCT.ACCT is known as a lookup column, and the table ACCT is known as a lookup table. Lookup relationships are defined when creating or editing table columns.
The reference table can be used in the field definition, to bring in information from the reference table. For example, you could enter ACCT.Description and ACCT.AcctGroup into the field definition, to bring in a description for each account and its designated account group.
Reference tables can be used in Axiom query settings that impact the data query, such as the data filter and the sum by setting. For example, you could define a data filter of ACCT.AcctGroup='Benefits' to restrict the data to only those accounts belonging to the Benefits group. Reference tables can also be used to define data range filters and column filters.
When using a reference table in an Axiom query setting, you must use fully qualified Table.Column syntax. For example, you must enter ACCT.Description, not just Description. If you only enter Description, then Axiom Software will assume the primary table, which probably does not contain a column named Description.
Axiom Software supports multiple levels of lookups. For example, the data table GL2019 can look up to the ACCT table (GL2019.ACCT to ACCT.ACCT), and then the ACCT table can look up to another reference table (ACCT.Category to Category.Category), and so on. You can query data from any lookup reference table, regardless of how many levels deep the lookups extend. For more information on including multiple-level lookups in Axiom query settings, see Using multiple levels of column lookups.
Including additional data tables when the primary table is a data table
When the primary table is a data table, then the Axiom query can include data from additional data tables. By default, queries to additional data tables use outer joins.
For example, you might query another data table to compare the results of two different plan scenarios or plan years. If the primary data table is BGT2020, you might also want to bring in data from the BGT2019 table for comparison purposes. The data query will be made against both tables—so if you planned for an account this year that you didn't include last year, that record would still be brought into the sheet, and vice versa.
When multiple data tables are included in an Axiom query, the following settings must use a shared column (or a column in a shared lookup table):
- Sum level
- Data filter
This means that any additional data table included in the query must—at minimum—share one column with the primary table. In most cases, the data tables will share multiple columns, such as the shared validated key columns Dept and Acct. See the detailed topics on each setting for more information on valid entries for queries with multiple data tables.
In most cases, when including an additional data table in an Axiom query, you must use fully qualified Table.Column syntax when referencing literal columns in that table. For example, you must specify BGT2019.M1 in the field definition instead of just M1. If you omit the table name, the primary table is assumed. However, if using alias names, then you can enter just the column alias (for example: CYB1), and it will point to the appropriate table.
When referencing shared validated columns in the data tables, such as ACCT, you must either specify the column in the shared lookup table (ACCT.ACCT) or use "column-only" syntax (ACCT). This way the setting applies to all data tables in the query. Note that if there are any possible ambiguities (for example, if the data tables link to two different reference tables that contain a column ACCT), then you must use the fully qualified syntax.
Including additional tables when the primary table is a reference table
When the primary table is a reference table, the field definition can include columns from the following additional tables:
-
Any reference table that the primary table has a lookup relationship with (a lookup table). This means that the primary table looks up to the reference table, not the other way around.
-
Any data or reference table that has a direct lookup relationship to the primary table. This means that the table contains a validated column where the lookup points to the primary table. If the table has a multiple-level lookup to the reference table (where the validated column looks up to a reference table that in turn looks up to the primary table), then it cannot be included.
Generally speaking, lookup reference tables can also be used in other Axiom query settings, such as the sum by and the data filter, whereas other tables cannot. See the detailed topics on the applicable settings for more information on valid entries.
When referencing columns from tables other than the primary table, you must use fully qualified Table.Column syntax, or use an alias name that resolves to a column that is valid for inclusion.
