Solved

Comapre Query Results From Two Access Databases

Posted on 2014-12-28
16
104 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
0
Comment
Question by:tomfarrar
  • 4
  • 4
  • 4
  • +2
16 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 
LVL 7

Author Comment

by:tomfarrar
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<So, the make table query in a third database might make sense as a possible answer, yes?>

right..
0
 
LVL 7

Author Comment

by:tomfarrar
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
 
LVL 10

Expert Comment

by:LukeChung-FMS
Comment Utility
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
 
LVL 7

Author Comment

by:tomfarrar
Comment Utility
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
 
LVL 7

Author Closing Comment

by:tomfarrar
Comment Utility
Practical and easy to implement.  Thanks.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now