Returning Query Data to a Table

[Engels]

I am creating an "events" style database (training courses) and i have a
select query that returns a count of all the people enrolled on each
course... i want to return this query data back into an existing Table called
Training Course into a column called "CountOfNumber Enrolled"

The Query runs off a table called "Registrations" that has a relationship
back to the Table i want to put the Data in (Training course) based around
the individual course ID (Primary key in the Training course table). The
query returns a count of the number of records in the Registration table for
each Course ID.

How do i put this data in the existing table (i don't really want to create
a new table)

I'm running Access 2000 and am very rusty in my database design skills so
please trat me as a newbie...

 

[kingston via AccessMonster.com]

Create the additional field in the table and perform an update query. Create
a query that links your existing query and the table via Course ID. Add the
new field to the output of the query and change the query type to an update
query. In the update section, use the result of the first query:
[CountOfNumber Enrolled].

 

[Engels]

I either only half understand what you are saying or I am struggling to
implement it if i do understand it.

I created a new query (an update query) and i understand it is meant to link
the new field in the existing Training Courses table to the existing query.
However i am confused as to what fields i should be including in this new
query. (either using the wizards or in design view). I basically can include
the Course ID field and the CountOfNumber Enrolled but these exist over
several tables and queries and i guess i'm struggling to relate them
together.

Do you mean me to relate the new query with joins to both the table adn 1st
query through Course ID?

How do i add the new field to the output of the query?

Could you baby step me a little as i am confused.

Create the additional field in the table and perform an update query. Create
a query that links your existing query and the table via Course ID. Add the
new field to the output of the query and change the query type to an update
query. In the update section, use the result of the first query:
[CountOfNumber Enrolled].

I am creating an "events" style database (training courses) and i have a
select query that returns a count of all the people enrolled on each
course... i want to return this query data back into an existing Table called
Training Course into a column called "CountOfNumber Enrolled"

The Query runs off a table called "Registrations" that has a relationship
back to the Table i want to put the Data in (Training course) based around
the individual course ID (Primary key in the Training course table). The
query returns a count of the number of records in the Registration table for
each Course ID.

How do i put this data in the existing table (i don't really want to create
a new table)

I'm running Access 2000 and am very rusty in my database design skills so
please trat me as a newbie...

 

[kingston via AccessMonster.com]

Add the field to the existing table in the table's design mode. I'm going to
call it [NumEnrl].
Create a query and add your original query and the table as the datasources.
Join the field [CourseID] between the query and table.
Add the new field [NumEnrl] to the output (bottom) of the query; drag it to
one of the columns in the row Field:.
Change the query type to update; look for Query in the menu bar at the top.
In the Update To: block in the query's output pane, enter the field that was
calculated in the first query [CountOfNumber Enrolled].
Run the query and the table will be updated.

If you have more than one table that needs to be updated, you'll need to
create queries for all of them.

I either only half understand what you are saying or I am struggling to
implement it if i do understand it.

I created a new query (an update query) and i understand it is meant to link
the new field in the existing Training Courses table to the existing query.
However i am confused as to what fields i should be including in this new
query. (either using the wizards or in design view). I basically can include
the Course ID field and the CountOfNumber Enrolled but these exist over
several tables and queries and i guess i'm struggling to relate them
together.

Do you mean me to relate the new query with joins to both the table adn 1st
query through Course ID?

How do i add the new field to the output of the query?

Could you baby step me a little as i am confused.

Create the additional field in the table and perform an update query. Create
a query that links your existing query and the table via Course ID. Add the

[quoted text clipped - 18 lines]

 

[Engels]

That all made sense and i would expec it to work BUT.....

I get an error wheni try to run the query saying "Operation must use an
updatable query"

BTW thanks for the help here i'm banging my head against a brick wall to do
seemingly such a simple thing...

Add the field to the existing table in the table's design mode. I'm going to
call it [NumEnrl].
Create a query and add your original query and the table as the datasources.
Join the field [CourseID] between the query and table.
Add the new field [NumEnrl] to the output (bottom) of the query; drag it to
one of the columns in the row Field:.
Change the query type to update; look for Query in the menu bar at the top.
In the Update To: block in the query's output pane, enter the field that was
calculated in the first query [CountOfNumber Enrolled].
Run the query and the table will be updated.

If you have more than one table that needs to be updated, you'll need to
create queries for all of them.

I either only half understand what you are saying or I am struggling to
implement it if i do understand it.

I created a new query (an update query) and i understand it is meant to link
the new field in the existing Training Courses table to the existing query.
However i am confused as to what fields i should be including in this new
query. (either using the wizards or in design view). I basically can include
the Course ID field and the CountOfNumber Enrolled but these exist over
several tables and queries and i guess i'm struggling to relate them
together.

Do you mean me to relate the new query with joins to both the table adn 1st
query through Course ID?

How do i add the new field to the output of the query?

Could you baby step me a little as i am confused.

Create the additional field in the table and perform an update query. Create
a query that links your existing query and the table via Course ID. Add the

[quoted text clipped - 18 lines]

I'm running Access 2000 and am very rusty in my database design skills so
please trat me as a newbie...

 

[kingston via AccessMonster.com]

Here is one quick way around the problem:
Change the select query used to calculate the count into a make table query.
Run it. Then replace the query in the update query with the table created.

There are better ways of doing this if you're interested, but it would
involve some code writing. HTH

That all made sense and i would expec it to work BUT.....

I get an error wheni try to run the query saying "Operation must use an
updatable query"

BTW thanks for the help here i'm banging my head against a brick wall to do
seemingly such a simple thing...

Add the field to the existing table in the table's design mode. I'm going to
call it [NumEnrl].

[quoted text clipped - 33 lines]