Update Query

[Teri Bridges]

Hi, I am hoping for some help. I have a "course_tbl" that has a "DeliveryHours" field. This field should capture the total duration of all lessons by courseID. I was attempting to create an update query that would take the values from an existing query and populate the fields in the table. I have not done any development with update queries. After watching many online videos I took a stab at it, and failed.

Basically the total of lesson duration from a query = the delivery hours in the table

I tried the following:
I created a "Lesson_query" which totals the lesson duration by course. This query runs and returns the correct summed values.

I made an update query, "qryCourseDuration" using the "Lesson_query" as a subquery which I want to populate the "DeliveryHours" in the "Course_TBL"

I created the "qryCourseDuration" then added the "Course_tbl" to the query. I then selected and added the "DeliveryHours" field to the query table.

I then clicked the Update query tool option making "DeliveryHours" my destination field in the "Course_tbl" . I then added the "Lessons_query" to the query creating my subquery. Access created the join.

In the Update to: field, I added the following statement: [Course_tbl].[DeliveryHours]=[Lesson_query].[SumOfLessonDuration] (assess change the title of the field by adding SumOf to my existing fieldname).

I viewed the query results and the query is empty. Note: I did try running the query with all three join options; all three failed to return any results. Any guidance with this type of query would be greatly appreciated.

 

[plog]

Why must DeliveryHours live in your table? You already have a query that produces the values you need--why not just use it when you need those values?

 

[plog]

Looked closer at your query and got a new question--What are you hoping to achieve with the First function in that query? It doesn't do what people think it does. I don't think it should even exist its such a nuisance.

 

[mike60smart]

Hi, I am hoping for some help. I have a "course_tbl" that has a "DeliveryHours" field. This field should capture the total duration of all lessons by courseID. I was attempting to create an update query that would take the values from an existing query and populate the fields in the table. I have not done any development with update queries. After watching many online videos I took a stab at it, and failed.

Basically the total of lesson duration from a query = the delivery hours in the table

I tried the following:
I created a "Lesson_query" which totals the lesson duration by course. This query runs and returns the correct summed values.

I made an update query, "qryCourseDuration" using the "Lesson_query" as a subquery which I want to populate the "DeliveryHours" in the "Course_TBL"

I created the "qryCourseDuration" then added the "Course_tbl" to the query. I then selected and added the "DeliveryHours" field to the query table.

I then clicked the Update query tool option making "DeliveryHours" my destination field in the "Course_tbl" . I then added the "Lessons_query" to the query creating my subquery. Access created the join.

In the Update to: field, I added the following statement: [Course_tbl].[DeliveryHours]=[Lesson_query].[SumOfLessonDuration] (assess change the title of the field by adding SumOf to my existing fieldname).

I viewed the query results and the query is empty. Note: I did try running the query with all three join options; all three failed to return any results. Any guidance with this type of query would be greatly appreciated.

Hi Teri
I don't think you need an Update Query

You currently have your "qryLessonsTotalCourseDurations" which list the Totals for Each Course.

Which table are you trying to update ?

 

[Pat Hartman]

For a recordset to be updateable, EVERY part of the query must be updateable. Therefore you cannot create a query that sums a column of values and then use that sum to update a table because the "totals" part of the query is not updateable. I know, the database engine should be smart enough to work this out but it isn't. Also, since the rules of normalization technically forbid storing calculated values, there is no reason to make the query smarter since it is violating second normal form anyway.