Avatar of Tom Farrar
Tom Farrar
Flag for United States of America asked on

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
Microsoft Access

Avatar of undefined
Last Comment
Tom Farrar

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Mike Eghtebas

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
PatHartman

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 Eghtebas

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
PatHartman

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.
Rey Obrero (Capricorn1)

<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 ???
PatHartman

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Tom Farrar

ASKER
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
Rey Obrero (Capricorn1)

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

right..
Tom Farrar

ASKER
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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Rey Obrero (Capricorn1)

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.
PatHartman

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 Eghtebas

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Luke Chung

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 Farrar

ASKER
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 Farrar

ASKER
Practical and easy to implement.  Thanks.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck