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 ?