Hi Sir's Good Day I have a 3 Ms Access DB named DB2019, DB2020 & DB2021 they are all identical in Tables, queries forms, reports etc..
what i want to achieve is it possible to pull out Data, example those 3 DB have a Search Form if im in DB2021 and use that Search Form i can only Search the Data that is in DB2021. what i need is i can also search Data from DB2020 and DB2019 while im in DB2021 SearchForm and Also Vice Versa....
is there a way to connect those 3 using a Query or something?
ive been banging my head in the past week thinking how to do this hehe Please Help... Thank you..
Why? Why do you have three databases of the same thing. Is each database near 2 Gig?
If there is a legitimate reason for this then make a single front end and link to the tables in the three databases. Then use Union queries on to search through all records.
Please explain the why?
There's no reason all that data should be in 3 seperate databases. Year is just a piece of data--if its relevant it shouldn't be stored in a database name, but in a field. If you are building a sales database you don't have a seperate database for every product you sell--you have a field in your Sales table which lets you know which product was sold. Same principle with year.
You need to get all your databases' data into 1 database. Do that and your Search form will work with all your data.
I find it interesting how people commonly see time as being non-contiguous, or that it is a distinctly different kind of stuff than other scalar data points. Nobody builds a separate table for financial transactions in excess of $1000, for instance, or a separate table for temperatures that fall below zero. But add one second to 12/31/2020 23:59:59, and it is not uncommon that people want to store everything that follows in a completely different container.
This is a sample of forgetting to take off his spreadsheet hat by the database creator. Spreadsheets tend to get unwieldy over type so they are sometimes separated by year or month or customer or whatever the designer decides is a rational break. This type of separation prevents easy consolidated reporting or event just looking up old stuff. Items from December are not all that old in January and yet you have to open a different database to view them.
No matter what you decide, the solution is going to require modifications to the application so, I would suggest making the modifications that will solve the problem PERMANENTLY rather than just until next year.
Add year if it doesn't already exist in the database. You didn't say what king of application data this is but "year" is a component of date and so it is quite possible that the "year" of the data is already stored in the table.
If the tables are using autonumbers as PK's, importing the parent tables will generate new pks for the appended records from prior years. This isn't a problem unless you have child tables to import. The problem is only a small blip though. You have to add an additional colum to the parent tables. call it OLDautonumber of something like that. When you append the old tables, map the original PK value into OLDautonumber. Nothing gets mapped to the actual autonumber PK. Access just generates a value as the record is appended. When you append the child tables, you have to join the child table to the consolidated table on the childFK to the parent OLDautonumber, Run the append. After validating that everything got appended, run an update query on the parent table to set OLDautonumber to null. Only ONE set of data can have values at one time otherwise, your child table will join to multiple parent rows as you import sucessive old sets.
Then depending on how you want the app to function, you may need to add additonal criteria on your forms so that the user can pull up just current data or include old data. This may be zero impact if the forms already take criteria.
Any other solution requires annual maintenance as you add additional years.
If you have so much old data that you are running into a problem with the 2G limit, it is time to move the BE to SQL Server. Again, this could be zero impact or major depending on how the app is designed. I build all my apps with ACE tables initially and the vast majority get converted to SQL Server or some other RDBMS so I design them with that in mind. I start with Jet/ace only because Access has better tools to build schemas. Once I think the schema is stable, I convert the BE and archive the Jet/ACE be. I want to do most of my testing with the target BE in place.
I fully concur with the other responders, this should all be in one db, all you are doing is building a headache for yourself. But to answer this question to get you out of a hole for now or this is a one off, the following should work to list records if you are only talking about one table. If more than one and tables are related then probably won't work
Assume your table is called 'tblData'
1. in the db2021 database, create a linked table to tblData in each of the other db's - when created they will have a numeric suffix added
2. create a union query along these lines (this assumes you have worked backwards through the years when linking
Code:
SELECT 2021 as dbYear, * FROM tblData
UNION ALL SELECT 2020 as dbYear, * FROM tblData_1
UNION ALL SELECT 2019 as dbYear,* FROM tblData_2
etc
Another option that will apply indexing is to ally your search criteria to each element of the union query
Code:
SELECT 2021 as dbYear, * FROM tblData WHERE somefield=forms!myform!searchcombo
UNION ALL SELECT 2020 as dbYear, * FROM tblData_1 WHERE somefield=forms!myform!searchcombo
UNION ALL SELECT 2019 as dbYear,* FROM tblData_2 WHERE somefield=forms!myform!searchcombo
etc
A way that does not involve linked tables is to use a union query with the path to the db - something like
Code:
SELECT *
FROM tblData
UNION ALL SELECT *
FROM tblData IN 'folderpath\db2020.accdb'
UNION ALL SELECT *
FROM tblData IN 'folderpath\db2019.accdb'
etc
or
Code:
SELECT 2021 as dbYear, *
FROM tblData
UNION ALL SELECT 2020 as dbYear, *
FROM [MS Access;DATABASE=folderpath\db2020.accdb].tblData
UNION ALL SELECT 2019 as dbYear, *
FROM [MS Access;DATABASE=folderpath\db2019.accdb].tblData
I'll leave you to adapt to allow the use of indexing
In any event, having found your records, what are you going to do? Open another form to display all the information? If all in one db, not a problem, but now which table is the recordsource going to be?
Using the Union works ONLY for parent tables. If there are child tables in the mix, the union doesn't work because there is no unique PK on which to join to the child table's FK
And If there are no child tables, the import is simple because it doesn't matter that the autonumber PK is going to change.
Don't agree - will work for any single table. But going into a more complex area you can include joins to bring both records through providing you use one of the two versions that use the IN method
for example
Code:
SELECT 2021 as dbYear, *
FROM tblData INNER JOIN tblchild ON tblData.PK= tblChild.FK
UNION ALL SELECT 2020, *
FROM tblData INNER JOIN tblchild ON tblData.PK= tblChild.FK IN '' [ms access;Database=folderpath\db2020.accdb]
UNION ALL SELECT 2019, *
FROM tblData INNER JOIN tblchild ON tblData.PK= tblChild.FK IN '' [ms access;Database=folderpath\db2019.accdb]
and you can still include a where clause
Code:
SELECT 2021 as dbYear, *
FROM tblData INNER JOIN tblchild ON tblData.PK= tblChild.FK WHERE somefield=forms!myform!searchcombo
UNION ALL SELECT 2020, *
FROM tblData INNER JOIN tblchild ON tblData.PK= tblChild.FK IN '' [ms access;Database=folderpath\db2020.accdb] WHERE somefield=forms!myform!searchcombo
UNION ALL SELECT 2019, *
FROM tblData INNER JOIN tblchild ON tblData.PK= tblChild.FK IN '' [ms access;Database=folderpath\db2019.accdb] WHERE somefield=forms!myform!searchcombo
We don't know what the OP's data looks like, whether it is normalised or not and clearly if the user is in 2019 the query would be structured differently to provide the same result
The problem is that the autonumbers for the three tables are almost certainly starting from 0 and so producing overlaps. Once you union rows from multiple parent tables, you have duplicate parent records. That messes up joins to the child tables So parent 123 occurs 3 times. Once for each year. Joining to the child table, you get 9 rows. 123 joined to child from tbl1, child from tbl2, child from tbl3 repeated three times.
You might be able to get past this by adding a dummy field to indicate the year in each of the subqueries and the join would then be on the original PK plus the year. That will join 123-2019 to 123-2019 rather than to 2020 and 2021 also.
Not relevant. If this is a one time event, then it doesn't matter much how it is implemented. If this is a permanent issue, then I would do it right which is to normalize the schema and keep all the data in a single table.
An early boss used to have a sign in his office:
If you don't have time to do it right the first time, what makes you think you have time to do it twice?
agree there are duplicate parent records but the only reason for including a join within the union query is to interrogate the child record and be able to reference the parent. As I stated it is to get out of a hole quickly, it is not a permanent or ideal solution - which is to combine the data into one db.
Thank you so much Guys i just temporarily solve this problem of mine by linking the previous DB tables from the current DB then use a Union query. i appreciate all your help if have another question please help me again hehe thanks again take care be safe always God Bless!
