I 've created a database with a sub-form on a one to many relationship.

The information within the primary table changes on a weekly basis (employee records).

I created a make table query from another database to update the primary table and make a new table, however because of the relationship the table cannot be deleted. I need to keep the relationship to enter records.

Any ideas please

Instead of deleting and remaking the wheel every time, use the same table and just use a linked copy to ensure you have up-to-date data.

If that's not possible, please explain your situation a little more. I'm not sure I understand.

Another thought - build two queries to update your data from a temporary source. Import your data to the temporary table, which has no relationships. So you can erase it anytime.

One query does an UPDATE for all records where the keys in the primary table match the corresponding keys in the temporary table.

The other query does an APPEND for all records where the keys in the temporary table record are not matched by a record in the primary table.

Since this is a thing you don't run all the time, you can afford to be a little sloppy. You could use a DCount in both queries to find whether the count was 0 or 1. (If it is ever >1, you left out a key or your tables aren't normalized, one or the other...)

What this doesn't address (and I leave this to you) is whether you want to build a third query to delete entries from the primary table when the count of dependent entries is 0 - another DCount and a DELETE query. The good side of this is that cleanup is a good thing. The bad side of this is that tossing out the trash before you were sure it was trash is a bad thing.

Another thought: With a weekly churning of the primary table, this database might benefit from a regularly scheduled period of maintenance such as a repair and a compress, followed by a backup to whatever you can use as a backup medium. Tape, ZIP drive, SuperDisk, another internal HD, network drive, CD-RW - you name it.

Thanks for your help.

I am using the linked table. It seems to work.

I'm not very experienced in using Access a great deal, therefore I have been amending databases that have been created by someone else. Perhaps I might be better starting from scratch.

Make sure you read up on database normalization (you can find it in the archives here under Search, on the Microsoft Knowledge Base at support.microsoft.com, or in countless websites and books).

No need to reinvent the wheel unless you realize the database is either badly unnormalized, or can't do something you need it to do. Which will likely be the case if its unnormalized... :mad:

Good luck! Post back with any further questions (to a new topic if they're unrelated to this one, of course)
David R