Comapre Query Results From Two Access Databases

In a new database, make this query:

returns only matched records from both databases.

Select d2014.ID, d2014.Field1, d2014.Field2, d2014.CalculatedField As Calculated2014 , d2014.CalculatedField  As Calculated2014  From [C:\Folder1\Dtabase2014.accdb].tblData As  d2014 Inner Join [C:\Folder1\Dtabase2014.accdb].tblData  As  d2015 ON  d2014.ID =  d2015.ID

Select allOpen in new window

returns all records from 2014 database and their matched records from 2015 database.

Select d2014.ID, d2014.Field1, d2014.Field2, d2014.CalculatedField As Calculated2014 , d2014.CalculatedField  As Calculated2014  From [C:\Folder1\Dtabase2014.accdb].tblData As  d2014 Left Join [C:\Folder1\Dtabase2014.accdb].tblData  As  d2015 ON  d2014.ID =  d2015.ID

Select allOpen in new window

returns all records from 2015 database and their matched records from 2014 database.

Select d2014.ID, d2014.Field1, d2014.Field2, d2014.CalculatedField As Calculated2014 , d2014.CalculatedField  As Calculated2014  From [C:\Folder1\Dtabase2014.accdb].tblData As  d2014 Right Join [C:\Folder1\Dtabase2014.accdb].tblData  As  d2015 ON  d2014.ID =  d2015.ID

Select allOpen in new window

Later you can introduce variables like LastYear (for 2014) and ThisYear (for 2015) to automate the process. You can make maketable query on top of this query if you wish to capture data.

If need be, this solution will compare data for any two years you may choose just by specifying 4-digit year values.


Mike

There is no reason to use make tables.  Make tables will only bloat the current database.  In the current database, link to the tables of the previous database.  Then you can create queries that join the two sets of data.

Pat,

If these databases are archives, then making links and/ or deleting the links is an extra task year after year. Whereas in a new (or active database), tomfarrar can have on a form two text boxes year1: [2014] and Year2: [2015] where the query of choice (inner-, left-, right-join) will be composed on the fly via a bit of QueryDef coding to show the desired results for any two years.

I agree with you that there is no need to make new tables to store duplicate data. The resultant data could viewed or exported to Excel if required.

Mike

I elected to not go into the design issues caused by splitting data in this manner since sometimes you are forced into it.  I have a client that uses Timberline as their ERP.  Every January, they archive certain tables and that forces me to change the Access apps we have built around Timberline to relink to a different "last year's" database.    Think of it this way - at least you have a permanent maintenance assignment.

Given the choice, I would separate tables into different BE databases to allow me to keep multiple years in the transaction tables to facilitate reporting.   Then each January, I would archive data older than 5 years (or whatever we decided we needed for "current" reporting) Of course, even better would be to upsize to SQL Server or some other RDBMS that would remove Access' 2G limit.

<There is no reason to use make tables.  Make tables will only bloat the current database.>

HOW can you bloat a new db with only two tables ???

Each time you run the make table query, Access allocates new space.  It does not reuse the space occupied by the previous version which I'm sure you know.  I think you were assuming that this was a one time operation in which case, importing the data wouldn't cause a problem.  I was looking at as a recurring issue and in that case, continually recreating the tables causes bloat and forces you to compact frequently.

If you decide to actually import the data, the best solution is to create a new empty database each time and import the data into it.  That way you can discard it when you are done and not worry about bloat.  Then you can link to the "new" database and run your queries on it.

The data I need to compare (in the two initial databases) are created by queries and not coming from one table.  The iniital queries (in the original databases) already have a join between two tables to create the result.  So, the make table query in a third database might make sense as a possible answer, yes?   As for other options recommended I am still trying to grasp what has been offered.  Thanks.  - Reilly

<So, the make table query in a third database might make sense as a possible answer, yes?>

right..

On the other hand, the project I am working on will require a quarter-to-quarter comparison to determine what should be forecasted for the next year.  Getting too many tables and databases involved in the process could ultimately allow things to get out of hand.

The project is evolving "on the fly" so the final product is not fully defined.  That is why I am looking for efficient suggestions as I don't want this to become a maintenance nightmare.

actually, I've been doing this method for a long time.
the first part of the codes I posted was actually creating a NEW db on the fly and then running the make table queries to the NEWLY created db, so no need to worry about the BLOATING.

I do on occasion use make table queries and even separate "working" databases to avoid bloating the main FE or BE.  But, my first choice is always linking and running the queries against the live data.  That way I don't need to worry about the age of my static copy.  There is nothing better or more efficient about making a copy of your data.  In fact, just the opposite so make sure you have a real need to make static copies of data before you embark on that path.

One reason to use the "working" database to hold staging data is if you are aggregating a large set of data and the resultset will be used in more than one report.  So, I had one banking app that reduced 5 million transaction records to about 250,000 summarized records and that summary set was used in several reports to slice and dice in different ways.  It was ultimately faster than going back to the SQL server tables for each separate report.  In this case, the reports were monthly and got their data from a data warehouse which was only updated monthly anyway so there was little jeopardy of the data being outdated.  If my reports were against active data, I wouldn't duplicate the data unless I had no other choice.  Nothing you have said indicates that you have a problem with size or speed running the queries in their separate databases.

Hi Reilly,

Regardless the data is coming from a table or a query from another database (because in the queries I have suggested, table name could be easily replaced with query names), you can remotely access and view the data you want dynamically. I agree with you in making the application work with on the fly dynamic queries. Take a look at my original post closely.

Mike

If you want an automated, commercial solution that requires no coding, our Total Access Detective program lets you compare any two Access objects or databases for differences. It'll let you compare two queries for data differences as well. See Microsoft Access Query Differences for details.

Thanks for all the input.  Unfortunately, I have not been able to give all the attention to eghtebas' proposed solution asI have not been able to get it to work, and I am getting nasty notes from EE that I am ignoring this question.  And EE has a point, but I have a lot going on with holiday time and other work.  

Ray's solution is the most practical for me as the need is immediate, and the make-table solution is great.  As for Pat's and Luke's comments, thanks as all the input has been valuable.  - Reilly

Practical and easy to implement.  Thanks.