Comapre Query Results From Two Access Databases

So I have two Access databases with identical information except for a calculating field that uses different dates.  One is calculating results based on 12/15/2014, and the other is calculating results from the same data based on 01/15/2015.  The calcuated results of the two dates are used to perform a value calculation in the databases.

The question I have is how can I best compare the results of the two database calculations for each record in the databases.  These records are identical in field structure and data with the exception of the calculated fields.  So it would be easy to match the individual records on key fields, but I am not sure the best way to do this when the query results reside in separate databases.

There are two databases because of the size of the data.  I thought about creating a third database where I could send the two query results where I could compare them, but it appears I would first need to download the query results to a text file and re-import the data to the third database.

Thoughts on how best to accomplish this task?  Thanks.  - Reilly
LVL 7
tomfarrarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you can create a make table query based on your query or convert your query into a make table query, directing the query to a third db.

sample
from db1
SELECT f1, f2,f3 INTO tblResult1 IN 'C:\foldername\db3.accdb'
FROM YourTable

and
from db2
SELECT f1, f2,f3 INTO tblResult2 IN 'C:\foldername\db3.accdb'
FROM YourTable

you have two tables created in db3.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
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

Open 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

Open 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

Open 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
0
PatHartmanCommented:
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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mike EghtebasDatabase and Application DeveloperCommented:
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
0
PatHartmanCommented:
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.
0
Rey Obrero (Capricorn1)Commented:
<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 ???
0
PatHartmanCommented:
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.
0
tomfarrarAuthor Commented:
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
0
Rey Obrero (Capricorn1)Commented:
<So, the make table query in a third database might make sense as a possible answer, yes?>

right..
0
tomfarrarAuthor Commented:
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.
0
Rey Obrero (Capricorn1)Commented:
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.
0
PatHartmanCommented:
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.
0
Mike EghtebasDatabase and Application DeveloperCommented:
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
0
Luke ChungPresidentCommented:
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.
0
tomfarrarAuthor Commented:
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
0
tomfarrarAuthor Commented:
Practical and easy to implement.  Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.