Unable to edit records > how to fix??

[mbannen]

I have two tables

1) TBL_CLAIM_AUDIT_DETAIL
2) MCT_AUDIT_ITEM

I want to pull in audit_cat_nme (category name) from MCT_AUDIT_ITEM (table 2) and still be able to update audit_item_status in TBL_CLAIM_AUDIT_DETAIL (table 2).

But it ain't workin'.

Here's the query:

SELECT TBL_CLAIM_AUDIT_DETAIL.audit_detail_id, MCT_AUDIT_ITEM.audit_cat_nme, TBL_CLAIM_AUDIT_DETAIL.audit_claim_folder_id, TBL_CLAIM_AUDIT_DETAIL.audit_item_nme, TBL_CLAIM_AUDIT_DETAIL.audit_item_status, TBL_CLAIM_AUDIT_DETAIL.audit_item_auditor_comment, TBL_CLAIM_AUDIT_DETAIL.audit_item_employee_comment

FROM TBL_CLAIM_AUDIT_DETAIL LEFT JOIN MCT_AUDIT_ITEM
ON TBL_CLAIM_AUDIT_DETAIL.audit_item_nme = MCT_AUDIT_ITEM.audit_item_nme

ORDER BY TBL_CLAIM_AUDIT_DETAIL.audit_item_nme;


Primary keys for the tables are

Table: MCT_AUDIT_ITEM
Keys:
audit_item_nme
audit_cat_nme
audit_product_cde

Table:
Keys: TBL_CLAIM_AUDIT_DETAIL
audit_claim_folder_id
audit_item_nme

 

[dhookom]

I would expect MCT_AUDIT_ITEM to have a single field primary key and TBL_CLAIM_AUDIT_DETAIL to have a single related field.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SonOfEmidec1100]

Not sure how to fix it but you cant always update the data in a query result set when using outer joins (amongst other things). Look in Access help and search the phrase "When can I update data from a query"

Hope this gives you some help.

 

[mbannen]

I could create associative tables to get down to 1 key field per table, but would this solve my problem...also, not sure I could do that w/o a lot of rework. Any thoughts along this line or alternative solutions are much appreciated.

 

[dhookom]

The way your tables are set up, you could have multiple records in MCT_AUDIT_ITEM with the save audit_item_nme value. Is this true? How would your value in TBL_CLAIM_AUDIT_DETAIL know which record in MCT_AUDIT_ITEM to link to?

You could use a form bound to just the detail records. Use a combo box to display the related record from MCT_AUDIT_ITEM.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.