Update a table from a form that contains a calculated field

[joberti]

I am new to Access and I have been trying to learn some basics as I feel there is much more things that can be done in Access than Excel. I am starting with something simple like creating the Eisenhower Matrix (importance, Urgency, Effort).
I am trying to create a Form that pulls fields from a table and calculates a separate field based on the data. This would also be dynamic as any changes to the data will automatically calculate the field.

tbl_Effort


EffortID
Rate (1-5)
Description

tbl_Impact
ImpactID
Rate (1-5)
Definition

tbl_Task
TaskID
Prioritize (calculated field Effort+Impact)
Effort (Combo Box; Rate)
Impact (Combo Box; Rate)

I am trying to create a way automatically calculate the Prioritize field based on the selections in the Effort and Impact field. My understanding is that it is preferable to use Queries instead of calculation in a Table. Unfortunately, the Combo Box results are completely altered when making edits to those fields in that Form. It apparently no longer pulls the data from the table and it will not store updates to the table.

Combo Box Preferable Results Combo Box Results after editing a field
High 3
Medium Medium
Low Low

How do I approach this?

*Update: I attached a screenshot of the database relationship. Unfortunately, I could only reduce the file to 516 even after compiling and repairing it.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[joberti]

I had to pretty much create the database all over again and it's now smaller than 500kb. Here is the DB with some basic info. I used a Query to create the Prioritization field based on the Effort and Impact rates. I also created a form that I am intending to use to update the fields on the TaskList table. The intent is to automatically update the Prioritization field based on the Effort and Impact selection.

[davegri]

Here you go. See if this is an OK approach. Rearranged table key names a bit. Established table referential integrity. The query was not updatable, so it's gone.



Cardio-davegri-v01.zip

Here's a second version that stores Prioritize in the junction table if you must have it stored. It uses a bit of VBA behind the form.

Cardio-davegri-v02.zip

[joberti]

Thank you for your help. It is what I was looking for. I noticed there aren't any new tables in the 2nd version that was used as a Junction table or where the Prioritize field was added. I saw the VBA code but I only see the calculation used for the Prioritize field. Am I missing something?

[davegri]

Thank you for your help. It is what I was looking for. I noticed there aren't any new tables in the 2nd version that was used as a Junction table or where the Prioritize field was added. I saw the VBA code but I only see the calculation used for the Prioritize field. Am I missing something?

Not sure of the thrust of your question. If this doesn't address the issue, please clarify your needs.

tbl_TaskList is the junction table, so there's no NEW table. The prioritize field was added to that table. Here's the relationship diagram for V2:

[joberti]

Thank you for your help. It is what I was looking for. I noticed there aren't any new tables in the 2nd version that was used as a Junction table or where the Prioritize field was added. I saw the VBA code but I only see the calculation used for the Prioritize field. Am I missing something?

[joberti]

Thank you davegri. I now understand what was done and this is exactly what I needed. Thank you very much!

[davegri]

Glad that it worked for you. Good luck with the project!