You cannot add or change a record because a relate

问题:

I'm fairly new to Access.

I have a DB table that needs to be normalized. I have some information about a person. These people are authorized to grant access to areas at our work site. Every person may be authorized several times to manage different areas, and of course different people can be authorized to manage different areas. My first try at it was to include the authorization and the areas together, but I realized that I was really repeating the data that way. After doing some study I decided that the best way to do this was to create 4 tables

tblPerson, tblPermission, tblArea, tblArea_Permission

The tblArea_Permission is a join table for the many-to-many relationship between tblPermission and tblArea (this is something that I just learned about). I seemingly set up the table relationships OK on the relationship tab. I also use a query for adding the records to the join table. When I try to do this, with a query that is getting the records from the tables, I get "You cannot add or change a record because a related record is required in table XXX." This would seem to be impossible.

I decided that I could probably live with the DB not enforcing referential integrity and took that away and used a combined primary key for the two records because every person with permission will control an area in only one combination. That seemed to work, but then I noticed that the records would randomly change. I decided that the DB must be corrupt. Parts of the DB seem to be working correctly, so I started with a new database and imported the tables and one form, then started to rebuild the new tables as described above. I got the same error.

Any help would be greatly appreciated. I've read through some different books, and used google, but nothing addresses this.

回答1:

If a person is authorised to manage an area, you need a persons_area table:

PersonID ) Primary key
AreaID   )

Which shows which areas the person can manage. I am not sure where the permissions table is coming from.

You will then not be able to add a record to person_areas table unless you have an ID in the area table and an ID in the persons table. If either of these IDs are missing, you will get the error above.

If you want more relevant comments on your DB design, you will need to post schemas.