Querying Data from 2 Historical Databases 2013-2017 and Current Historical 2018-Present

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
184
I have a form with a drop down consisting of Plan_Numbers. When you input a plan number or use the dropdown, the subform in which I need to query has 2 link tables from different databases into my main database form, I had to separate the Historical DB because the database was over 2gigs. I created another Historical database which consist of Historical_2013-2017 data which I need to query with the current. My problem is how do I just query the two tables together and pull just what I need in the form based off the Dropdown Plan_Number (Example: 1125).

Link Tables:
HistoricalData
HistoricalData_2013_2017
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
I had to separate the Historical DB because the database was over 2gigs.
Do you regularly "Compact & Repair" the database to keep it lean?
Note that in Access, when you "delete" data, it isn't really deleted until you "Compact & Repair" it, so you won't see the size of the database decrease until you do this (if you don't do that, the database becomes "bloated" over time).

I have no idea how many data you have, and how much it might compact it, but I just want to make sure that doing this doesn't essentially solve the issue for you by making it small enough to fit everything into one database.

My problem is how do I just query the two tables together and pull just what I need in the form based off the Dropdown Plan_Number (Example: 1125).
Note that you can "link" tables from one database into another (and that does not add much memory to that database).
So you could link the "historical" table in your newer database so both data tables are visible from the new database.

Then, form there, there are two ways of going about doing what you want, and they both involve using a Union query (see: Use a union query to combine multiple queries into a single result).
1. The first way is to create a Union query to combine these two tables together into one, and then writing a query based on that query that searchs for your desired results.
2. The second way is to perform the query on each table individually, and then create a Union query to combine the results from those two queries into one.
 
Upvote 0
Do you regularly "Compact & Repair" the database to keep it lean?
Note that in Access, when you "delete" data, it isn't really deleted until you "Compact & Repair" it, so you won't see the size of the database decrease until you do this (if you don't do that, the database becomes "bloated" over time).

I have no idea how many data you have, and how much it might compact it, but I just want to make sure that doing this doesn't essentially solve the issue for you by making it small enough to fit everything into one database.


Note that you can "link" tables from one database into another (and that does not add much memory to that database).
So you could link the "historical" table in your newer database so both data tables are visible from the new database.

Then, form there, there are two ways of going about doing what you want, and they both involve using a Union query (see: Use a union query to combine multiple queries into a single result).
1. The first way is to create a Union query to combine these two tables together into one, and then writing a query based on that query that searchs for your desired results.
2. The second way is to perform the query on each table individually, and then create a Union query to combine the results from those two queries into one.
I understand how the UNION works but is there a way for me to tie the union to just that specific Plan_Number on the dropdown menu when selected only? I Compact and Repair twice a week but we are talking about data housed since 2013 by Accounting and the Billing Dept and yes I understand SQL server would be a better solution but I have to use what I have at the time...
 
Upvote 0
If you do the Union Query on the two tables first, then you would just apply the value from the drop-down on that Union query.
 
Upvote 0
If you do the Union Query on the two tables first, then you would just apply the value from the drop-down on that Union query.
Thanks Joe but I can't do the UNION because it yields too many rows and I get a error saying unable to proceed with query, you have exceeded the 2gig mark
 
Upvote 0
Then it looks like you are going to have to do the two queries first, and apply the criteria to each.
Then do a Union Query on the results of those two queries.
 
Upvote 0
Solution
Then it looks like you are going to have to do the two queries first, and apply the criteria to each.
Then do a Union Query on the results of those two queries.
Thanks Joe, that worked and it's actually a lot faster now...
 
Upvote 0
Excellent, that is good news!
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top