Update Query Syntax Issue

[dearlandlord88]

Hello all.

Hoping someone may be able to help with an error I keep getting with the syntax of an update query I'm putting together. I'm sure it's an embarrassingly basic mistake I'm making, but I'm fairly new to Access and can't seem to find a solution. Have searched this and other forums for answers to similar questions but still can't resolve.

I have an update query that updates a table containing students' test results. The original SQL, which predates when I first took over managing this database is as follows:

UPDATE [tbl_Assessmentyr1] INNER JOIN [tbl_*ModuleGrades_Yr1] ON [tbl_Assessmentyr1].[Student ID] = [tbl_*ModuleGrades_Yr1].[Student ID] SET [tbl_*ModuleGrades_Yr1].[Student ID] = [tbl_Assessmentyr1].[Student ID], [tbl_*ModuleGrades_Yr1].[Mod1 Prelim A] = [tbl_Assessmentyr1].[Mod1 Prelim A],[tbl_*ModuleGrades_Yr1].[Mod2 Prelim A] = [tbl_Assessmentyr1].[Mod2 Prelim A]

That's just an excerpt; it goes on much longer because there are around 20 fields, but obviously the syntax of the '[tbl_*ModuleGrades_Yr1].[Mod2 Prelim A] = [tbl_Assessmentyr1].[Mod2 Prelim A]' section just repeats with different field names, separated by commas

The problem with this is that the table containing the updates [tbl_Assessmentyr1] is a record of only new grades achieved within a recent time period. Therefore it will contain blanks in some fields where a student may already have a grade recorded in the main table [tbl_*ModuleGrades_Yr1] because they took that particular test in a previous time period. So you could easily overwrite valid results with blanks.

To fix this I tried to amend the syntax so that will only make updates where the field in the main table does not already contain a result. I used the following:

UPDATE [tbl_Assessmentyr1] INNER JOIN [tbl_*ModuleGrades_Yr1] ON [tbl_Assessmentyr1].[Student ID] = [tbl_*ModuleGrades_Yr1].[Student ID] SET [tbl_*ModuleGrades_Yr1].[Mod1 Prelim A] = [tbl_Assessmentyr1].[Mod1 Prelim A] WHERE [tbl_*ModuleGrades_Yr1].[Mod1 Prelim A] is Null

That works fine if my statement deals with just a single field, but if I do this for all 20 fields that the statement originally covered, i.e. so it reads like this...

UPDATE [tbl_Assessmentyr1] INNER JOIN [tbl_*ModuleGrades_Yr1] ON [tbl_Assessmentyr1].[Student ID] = [tbl_*ModuleGrades_Yr1].[Student ID] SET [tbl_*ModuleGrades_Yr1].[Student ID] = [tbl_Assessmentyr1].[Student ID], [tbl_*ModuleGrades_Yr1].[Mod1 Prelim A] = [tbl_Assessmentyr1].[Mod1 Prelim A] WHERE [tbl_*ModuleGrades_Yr1].[Mod1 Prelim A] is Null, [tbl_*ModuleGrades_Yr1].[Mod2 Prelim A] = [tbl_Assessmentyr1].[Mod2 Prelim A] WHERE [tbl_*ModuleGrades_Yr1].[Mod2 Prelim A] is Null, [tbl_*ModuleGrades_Yr1].[Mod3 Prelim A] = [tbl_Assessmentyr1].[Mod3 Prelim A] WHERE [tbl_*ModuleGrades_Yr1].[Mod3 Prelim A] is Null

...and so on I get 'syntax error: comma in query statement'. There were no such problems before, so I assume the problem lies in the syntax of the WHERE part of the statement that I've added, but I can't figure out where I've gone wrong.

Very grateful for any assistance.

 

[namliam]

Lets start by re-wwriting your sql into a more readable version

UPDATE            [tbl_Assessmentyr1] 
       INNER JOIN [tbl_*ModuleGrades_Yr1] ON [tbl_Assessmentyr1].[Student ID] = [tbl_*ModuleGrades_Yr1].[Student ID] 
SET [tbl_*ModuleGrades_Yr1].[Student ID] = [tbl_Assessmentyr1].[Student ID]
  , [tbl_*ModuleGrades_Yr1].[Mod1 Prelim A] = [tbl_Assessmentyr1].[Mod1 Prelim A] 
WHERE [tbl_*ModuleGrades_Yr1].[Mod1 Prelim A] is Null
  , [tbl_*ModuleGrades_Yr1].[Mod2 Prelim A] = [tbl_Assessmentyr1].[Mod2 Prelim A] 
WHERE [tbl_*ModuleGrades_Yr1].[Mod2 Prelim A] is Null
, [tbl_*ModuleGrades_Yr1].[Mod3 Prelim A] = [tbl_Assessmentyr1].[Mod3 Prelim A] 
WHERE [tbl_*ModuleGrades_Yr1].[Mod3 Prelim A] is Null

The proper update statement goes
Update
Set field1, field2
Where

Only one where allowed
So either you have to go with multiple update statements so you can work with the where clauses
Or use an NZ function to leave the existing value untouched but still update all records.

 

[dearlandlord88]

Many thanks, I'll explore both options and see where I get.

 

[jdraw]

I think you have included as statement that is not necessary

You have (using mailman's edited)
tbl_Assessmentyr1]
INNER JOIN [tbl_*ModuleGrades_Yr1] ON
[tbl_Assessmentyr1].[Student ID] = [tbl_*ModuleGrades_Yr1].[Student ID]
and then you do a set on studentid--- But you joined them based on the fact that studentId is a match in both tables
so
SET [tbl_*ModuleGrades_Yr1].[Student ID] = [tbl_Assessmentyr1].[Student ID], (at least the studentId part is not needed.

Totally agree that only 1 where clause is permitted.

Also just a comment:
The naming convention with embedded spaces and * is not a recommended
approach. And the yr1 suffix suggests to me that you have a on normalized structure (guessing)