Can no longer edit queries after Table divide

[DarrylJP]

I'm sure this is a simple resolution but probably above my intermediate Access skills in terms of quickly identifying/troubleshooting.

I had a table, that over time, had grown to well over 90 fields which was causing some issues regarding restrictions on number of indexed fields, etc. For this reason I decided to sub divide the data into three new tables with "RPATSID" being the common field that linked the three together.

When running queries now, everything works find and I get all the data I need like i did before with the single table, but if I include fields across more then one of these tables I cannot edit any of the data. I have checked the easy issues like the status of "Allow Edits", "Data Entry", etc

Attached is the screenshot showing the relationship between the three tables (linked by RPATSID).

Any help in letting me know if there is any correction to this.

[#2 · Accepted solution]

Hi,

1. You can have 32 indexes in a table. With just 90 fields you would have to have a very special use case to need so many indexes. Also be sure to not have duplicate indexes as e.g. Access already creates a hidden index on the foreign key field in every relationship.
2. In order for the query to be updatable your common field RPATSID should have a unique index in all 3 tables.

Servus

Karl
*********
http://AccessDevCon.com
Access FAQ (German/Italian): http://donkarl.com

[#3]

@Karl_DonaubauerThanks so much Karl! I don't know how I didn't notice I had RPATSID set to "Yes (duplicates OK)" when I recreated the tables. Everything is working great now after following your instructions! Your help is greatly appreciated 🙂