Consolidating data from multiple data files
Overview
ODBClink can consolidate data from multiple data files and accounting software.
Consolidating data from multiple data files
In order to consolidate data from multiple data files, you need to setup multiple data file connections. See Managing Data Files for more information.
Once multiple data files are connected and the get data process has run, ODBClink will have downloaded the data from each data file to each data warehouse you created.
How to determine where the data came from
ODBClink will also include an additional field in each table, the CompanyRecordID field. This field contains the identifier of your actual data file. As example, data from first data file may have CompanyRecordID of 1. Data from second data file may have CompanyRecordID of 2, etc.
ODBClink may also include an additional field in each table, the CompanyName field. This field contains the name of your data file as defined in the manage data files dialogue.
To make sense of which ID represents which data file, you have three approaches:
- If the CompanyName field is present and populate, you can use that field to determine the source.
- ODBClink may automatically include a AA_tblMYOBDataFiles which you can join to get the name you used during your setup.
- Alternatively, including the MYOB_DataFileInformation or MYOX_DataFileInformation table (or similar named table for other accounting software) and joining data onto this table will allow you to get the name as defined within MYOB (or you accounting software).
Consolidating data from different accounting software
The tables to which the data gets depends on your accounting package.
- Data from MYOB AccountRight Classic will get downloaded to MYOB_ tables;
- Data from MYOB AccountRight Live will get downloaded to MYOX_ tables;
- Data from Reckon Accounts and QuickBooks get downloaded into QuickBooks_ tables;
- Data from Xero gets downloaded to XERO_ tables.
If you consolidate data from multiple accounting software, be mindful that you may then need to union multiple tables together to get the complete picture. This is because MYOB tables will not contain XERO data and vice versa.