We help IT Professionals succeed at work.

Comapre Query Results From Two Access Databases

Tom Farrar
Tom Farrar asked
on
132 Views
Last Modified: 2015-01-04
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mike EghtebasDatabase and Application Developer

Commented:
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
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
Mike EghtebasDatabase and Application Developer

Commented:
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
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
CERTIFIED EXPERT
Top Expert 2016

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 ???
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
Tom FarrarConsultant
CERTIFIED EXPERT

Author

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
CERTIFIED EXPERT
Top Expert 2016

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

right..
Tom FarrarConsultant
CERTIFIED EXPERT

Author

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.
CERTIFIED EXPERT
Top Expert 2016

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.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
Mike EghtebasDatabase and Application Developer

Commented:
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
Luke ChungPresident
CERTIFIED EXPERT

Commented:
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.
Tom FarrarConsultant
CERTIFIED EXPERT

Author

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
Tom FarrarConsultant
CERTIFIED EXPERT

Author

Commented:
Practical and easy to implement.  Thanks.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.