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

[Mac1206]

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

 

[Joe4]

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.

 

[Mac1206]

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

 

[Joe4]

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.

 

[Mac1206]

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

 

[Joe4]

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.

 

[Mac1206]

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

 

[Joe4]

Excellent, that is good news!