Update Query using Table & Query

[cwoelfe]

I have a Query that lists anyone enrolled that is a senior... it is querying
from the college's SIS database.

Then I have a table called "Withdrawals"... This is a seperate table that is
created from another query entirely. But it does not list if the students are
Seniors.

I want to create an update query that will put a "SR" into the Seniors field
in my Withdrawals TABLE if the Student ID from my Withdrawals TABLE matches
the Student ID in the Seniors QUERY.

I was able to create a Select Query that would pull out and list the the
students.... however, when i tried to turn it into an Update Query, it
wouldn't work.. it said i needed an updatable query... i just don't
understand..

andways, here is the SELECT QUERY that worked...

SELECT [SS Enrollments - Seniors - Step 3].TERM_YYT, [SS Enrollments -
Seniors - Step 3].SECTION_ID, [SS Enrollments - Seniors - Step 3].STU_ID, [SS
Enrollments - Seniors - Step 3].STU_NAME, [SS Enrollments - Seniors - Step
3].RT_TCAL_OPT1
FROM [Withdrawal List] RIGHT JOIN [SS Enrollments - Seniors - Step 3] ON
[Withdrawal List].STU_ID = [SS Enrollments - Seniors - Step 3].STU_ID;


Now all I need is how to make an Update Query that will update the "Senior"
field in the Withdrawals table from the RT_TCAL_OPT1 field in this query...

 

[KARL DEWEY]

Try this --
UPDATE [Withdrawal List] INNER JOIN [SS Enrollments - Seniors - Step 3] ON
[Withdrawal List].STU_ID = [SS Enrollments - Seniors - Step 3].STU_ID SET
[Withdrawal List].[Senior] = [SS Enrollments - Seniors - Step
3].RT_TCAL_OPT1;

 

[John Spencer]

I'm too lazy to type those long table/query names. It seems to me that all
you need is:

UPDATE Enrollments INNER JOIN Withdrawals
ON Enrollments.Stu_ID = WithDrawals.Stu_ID
SET WithDrawals.Senior = [Enrollments].[RT_CAL_OPT1]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Krzysztof Naworyta]

| cwoelfe wrote:
|| I have a Query that lists anyone enrolled that is a senior... it is
|| querying from the college's SIS database.

If this query is not updateable, then the "update" query, based on it,
wouldn't work.

Workaround:
use this query to create new temporary table:


Select * Into tmp
From SISQuery

and use this table in update query

UPDATE
[Withdrawal List] w
INNER JOIN
tmp x
ON
w.STU_ID = x.STU_ID

SET
w.[Senior] = x.RT_TCAL_OPT1;

--
KN

|| Then I have a table called "Withdrawals"... This is a seperate table
|| that is created from another query entirely. But it does not list if
|| the students are Seniors.
||
|| I want to create an update query that will put a "SR" into the
|| Seniors field in my Withdrawals TABLE if the Student ID from my
|| Withdrawals TABLE matches the Student ID in the Seniors QUERY.
||
|| I was able to create a Select Query that would pull out and list the
|| the students.... however, when i tried to turn it into an Update
|| Query, it wouldn't work.. it said i needed an updatable query... i
|| just don't understand..
||
|| andways, here is the SELECT QUERY that worked...
||
|| SELECT [SS Enrollments - Seniors - Step 3].TERM_YYT, [SS Enrollments
|| - Seniors - Step 3].SECTION_ID, [SS Enrollments - Seniors - Step
|| 3].STU_ID, [SS Enrollments - Seniors - Step 3].STU_NAME, [SS
|| Enrollments - Seniors - Step 3].RT_TCAL_OPT1
|| FROM [Withdrawal List] RIGHT JOIN [SS Enrollments - Seniors - Step
|| 3] ON [Withdrawal List].STU_ID = [SS Enrollments - Seniors - Step
|| 3].STU_ID;
||
||
|| Now all I need is how to make an Update Query that will update the
|| "Senior" field in the Withdrawals table from the RT_TCAL_OPT1 field
|| in this query...