Solved

MS Access update statement with subquery in the set clause?

Posted on 2007-12-02
3
4,644 Views
Last Modified: 2012-05-05
Experts,
I am trying to run the following update this update statement works:

UPDATE MJM_ATTEND_PAY_INDIV SET MJM_ATTEND_PAY_INDIV.ATTEND_PER_MONTH = 4
WHERE (((MJM_ATTEND_PAY_INDIV.ROW_ID) In (SELECT [MJM_ATTEND_PAY_INDIV].[ROW_ID]
FROM MJM_SCHOOL, MJM_CLASS, MJM_STUDENT, MJM_ATTEND_PAY_INDIV
WHERE [MJM_SCHOOL].[ROW_ID]=[MJM_CLASS].[SCHOOL_ID] And
[MJM_CLASS].[ROW_ID]=[MJM_STUDENT].[CLASS_ID] And
[MJM_STUDENT].[ROW_ID]=[MJM_ATTEND_PAY_INDIV].[STUDENT_ID] And
[MJM_STUDENT].[ACTIVE_FLG] = -1 And  
[MJM_ATTEND_PAY_INDIV].[PAID_FLG]=0 And
[MJM_SCHOOL].[ROW_ID] IN (select row_id  from mjm_school as sc where sc.active_flg = -1 and sc.billing_flg = -1 and sc.payment_type = 'Individual') And
[MJM_ATTEND_PAY_INDIV].[BILLING_MONTH]=[Date])));

However instead of hardcoding the number 4 at the end of the set statement, I want to use a subselect:

UPDATE MJM_ATTEND_PAY_INDIV SET MJM_ATTEND_PAY_INDIV.ATTEND_PER_MONTH = (select config_value from mjm_lu_config where config_type = 'Week Override')
WHERE (((MJM_ATTEND_PAY_INDIV.ROW_ID) In (SELECT [MJM_ATTEND_PAY_INDIV].[ROW_ID]
FROM MJM_SCHOOL, MJM_CLASS, MJM_STUDENT, MJM_ATTEND_PAY_INDIV
WHERE [MJM_SCHOOL].[ROW_ID]=[MJM_CLASS].[SCHOOL_ID] And
[MJM_CLASS].[ROW_ID]=[MJM_STUDENT].[CLASS_ID] And
[MJM_STUDENT].[ROW_ID]=[MJM_ATTEND_PAY_INDIV].[STUDENT_ID] And
[MJM_STUDENT].[ACTIVE_FLG] = -1 And  
[MJM_ATTEND_PAY_INDIV].[PAID_FLG]=0 And
[MJM_SCHOOL].[ROW_ID] IN (select row_id  from mjm_school as sc where sc.active_flg = -1 and sc.billing_flg = -1 and sc.payment_type = 'Individual') And
[MJM_ATTEND_PAY_INDIV].[BILLING_MONTH]=[Date])));

I know the subselect only returns 1 row.  However, when I do this I get an error that says "query must be updateable".

I would think I would be able to do this, am I doing something wrong?

Thank you in advance for the help.

- Mike
0
Comment
Question by:uconnfb13
  • 2
3 Comments
 
LVL 75
ID: 20391776
I'm not sure you can use a sub selelect stmt in the SET part of an update query.

mx
0
 
LVL 75
ID: 20391782
Possibly a DLookup instead ?

mx
0
 
LVL 16

Accepted Solution

by:
Rick_Rickards earned 400 total points
ID: 20392213
PARAMETERS [Week Overide] Text ( 255 );
UPDATE MJM_ATTEND_PAY_INDIV SET MJM_ATTEND_PAY_INDIV.ATTEND_PER_MONTH = (select config_value from mjm_lu_config where config_type = [Week Override])
WHERE (((MJM_ATTEND_PAY_INDIV.ROW_ID) In (SELECT [MJM_ATTEND_PAY_INDIV].[ROW_ID]
FROM MJM_SCHOOL, MJM_CLASS, MJM_STUDENT, MJM_ATTEND_PAY_INDIV
WHERE [MJM_SCHOOL].[ROW_ID]=[MJM_CLASS].[SCHOOL_ID] And
[MJM_CLASS].[ROW_ID]=[MJM_STUDENT].[CLASS_ID] And
[MJM_STUDENT].[ROW_ID]=[MJM_ATTEND_PAY_INDIV].[STUDENT_ID] And
[MJM_STUDENT].[ACTIVE_FLG] = -1 And  
[MJM_ATTEND_PAY_INDIV].[PAID_FLG]=0 And
[MJM_SCHOOL].[ROW_ID] IN (select row_id  from mjm_school as sc where sc.active_flg = -1 and sc.billing_flg = -1 and sc.payment_type = 'Individual') And
[MJM_ATTEND_PAY_INDIV].[BILLING_MONTH]=[Date])));
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question