Editing data derived from multiple junction tables - Access mdb

[MuscatCalling]

Hi,

I am tracking employees, their trades, the equipment that they are authorised to work on, and the tasks that they are authorised for on that equipment.

Although a few instances may occur where a combination of variables is not permissible, after reviewing existing hard copy records I estimate over 95% of all possible combinations of all four variables are possible. That is, effectively none of the variables are functionally dependent upon any other variable.

I have set up tables for tblPersonnel, tblEquipment, tblTrade, tblTask. I then set up junction tables with composite keys for:

tblPersonnelTradeDetails (PersonnelID, TradeID)
tblPersonnelEquipmentDetails (PersonnelID, EquipmentID)
tblPersonnelTaskDetails (PersonnelID, TaskID)
tblEquipmentTradeDetails (EquipmentID, TradeID)
tblEquipmentTaskDetails (EquipmentID, TaskID)
tblTradeTaskDetails (TradeID, TaskID)

All tables have referential integrity enforced with cascading updates.

I have populated tblPersonnel, tblEquipment, tblTrade, tblTask with data. The junction tables use lookup functions to these tables. I can input data into the junction tables individually, and a select query based on all six junction tables returns the correct recordsets.

I now need to develop a form….. I want to select PersonnelID, enter the trade(s) for which the individual is authorized, enter the equipment(s) against each trade(s), and then enter the task(s) against each equipment/trade (forms and subforms?) This is where I have hit a wall… I have a form for PersonnelID and a subform based on the select query. I can enter data into the form, but cannot save it. The error message states that there is no matching record in tblTrade... This occurs even with the form (and subform) properties set to Dynaset (inconsistent updates).




Any suggestions?

Best regards,

Mark

 

[fneily]

You don't have an updatable query and not taking into account referential integrity. See:
Harnessing the Power of Updatable Queries

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp

 

[Remou]

You mention that tasks are related to equipment tasks that they are authorised for on that equipment

So should this table exist in this form:
tblPersonnelTaskDetails (PersonnelID, TaskID)?

Should it not be:
tblPersonnelTaskDetails (PersonnelID, EquipmentID, TaskID)?

 

[MuscatCalling]

Fneily, Remou,

Thanks for your replies. Fneily, with regard to your url link for updateable queries - I confess that I am relatively new to this game, but the url does not seem to cover multiple M:M tables in a query. Access help states that such queries are not updateable although a form based on the query, and set to dynaset inconsistent update, should be updateable. All my tables have autonumber PK's. My understanding of inconsistent updates is that this 'bypasses' referential integrity by writting a new PK against the descriptive fields i.e. Joe Bloggs with a PK of, say, 1, will be entered into the table as a new recordset - Joe Bloggs with a new PK of, say, 20. If I am correct I do not know how that new recorset would be retrievable??

Remou, my problem with your suggestion is that Personnel, Equipment, and Task are not functionally dependent. Any combination of PersonnelID, EquipmentID, and TaskID is permissible. If I used your suggested table, I would have to enter multiple Personnel and Equipment records against each task, which, if I understand correctly, would all need to be entered in the form for each task entered. That is, all three fields would comprise a single PK. Does this make sense?

Please forgive my inability to accurately describe this scenerio. As I said, I'm learning but didn't anticipate the needs of my database outgrowing my technical skills quite so rapidly!!

Thanks again,

Regards,

Mark

 

[fneily]

Concept - relational databases, which Access is one of, do not deal directly with many-to-many relationships. You create what's known as a junction table which will create two one-to-many's. At minimum, a junction table contains the primary keys of the two tables.
Setting "dynaset inconsistent update" is not an appropriate way to update tables. But, you said you set some "referential integrity". If you want to try the inconsistent update thing, then try setting Cascade Update.
I'd take a closer look at your design.

 

[dhookom]

I rarely base forms on more than a single table. I use subforms or open additional forms as necessary.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MuscatCalling]

Thanks to all for responding,

fneily, I didn't write clearly before: I have set up junction tables containing the PK from the parent tables. With four parent tables I ended up with six junction tables. I also do not like the idea of inconsistent updates, but was testing to see whether I could make the form work. Cascade updates is enabled for each relationship.

dhookum, I have a single form based on the tblPersonnel, and nested subforms to display other data. When the sfrms are based on a single tbl - no problem, I can edit / add to that recordset. But when I need to base the sfrm on a qry developed from 3 or more junction tbls, I cannot add a record to that sfrm without incurring referential integrity error mssgs. This is the part I don't understand - all the parent tbls are fully populated, so how can I be breaking referential integrity?

This appears to be the crux of my problem. How do I edit or add records to a sfrm that is based on a qry that is itself based on 3 or more junction tbls??? As I mentioned before, running the junction tbl query on its own returns the correct records - I just can't find a way to edit it...

Regards,

Mark

 

[dhookom]

You have mentioned "qry developed from 3 or more junction tbls" without providing the SQL view. I still can't imagine using a form record source with more than a couple tables.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]