I'm facing following problem :
I have a database which contains companydata. One of the tables is called tblEmployees. This database is managed by HR. On a regular base I run some queries on this table to divide the records in different tables like tblWhiteCollars, tblBlueCollars_Shift1, tblBlueCollars_Shift2, ... My intention of doing so is having the correct data available for other access-applications without having to run the queries all the time.
Now I have a new database where I linked this table to.
Problem is now that, when the new application is active, I cannot run my queries anymore, because my table is locked. It is not an option to close the new database, because this is used on everey machine in our productionplant.
I hope there is a workarround for this, because if not, it will ruin my whole concept.
Anyone any idea?
I'm afraid that your whole concept is ruined from the very beginning. As you describe it your database is not normalized because there is no serious reason to store the same data in several tables.
You can have a column in tblEmployees identifying the status of each row (WhiteCollars, BlueCollars_Shift1, BlueCollars_Shift2, ...) or if this is subject to change often, you can have a second table with a column identifying this status and another column containing a foreign key to the table tblEmployees. A set of queries (qryWhiteCollars, qryBlueCollars_Shift1, qryBlueCollars_Shift2, ...) joining both tables will then yield the desired data (sub)sets.