Update Query not working

[raugust]

Hi there:

My database is set up so membership information for a selected contact is displayed in a subform. I am trying to run an update query from a command button macro to select the subform data file record for the most recent membership record and set a field on that record to a value of 1. My code is:

UPDATE ContactMasterT INNER JOIN MembersT ON ContactMasterT.MasterID=MembersT.MasterID SET MemberSpecial = 1
WHERE MembersT.[MemberCardNo] IN (SELECT Max(MembersT.[MemberCardNo])
FROM MembersT);

I'm not getting an error message, but I'm also not getting my update. Can anyone see where I am going wrong?

 

[CJ_London]

You need to alias your subquery and remove the tablename from the max

(SELECT Max(MemberCardNo) FROM MembersT AS M)

Also this will only update one record - is this your expectation?

 

[raugust]

Thanks CJ. I think, however, my understanding of aliasing is deficient. Per your note, yes, my intent is just to update the most recent membership record. My code after your suggestion is:

UPDATE ContactMasterT INNER JOIN MembersT ON ContactMasterT.MasterID=MembersT.MasterID SET MemberSpecial = 1
WHERE MembersT.[MemberCardNo]
IN (SELECT Max(MemberCardNo) FROM MembersT AS M);

I am still; not getting the update. Could I be doing something wrong in the macro that runs it? I am using OpenQuery, queryname, Datasheet, Edit.

 

[CJ_London]

this is an action query so you should be using (in VBA)

docmd.runsql

or

currentdb.execute

I don't use macros so I don't know what the equivalent macro would be

 

[raugust]

Actually, I just realized I am getting an update to one record, but it is not the correct one. It appears to be updating the MembersT record with the highest value of MasterID in the whole file, rather than the highest value of MemberCardNo among the records selected for a given individual in the subform.

 

[CJ_London]

It appears to be updating the MembersT record with the highest value of MasterID in the whole file

I pointed that out in my first post

Modify your subquery to

SELECT Max(MemberCardNo) FROM MembersT AS M WHERE MasterID=ContactMasterT.MasterID

 

[raugust]

So, trying to absorb two pieces of advice, the update query is now coded as follows:

Dim SQL As String

SQL = "UPDATE ContactMasterT " & _
"INNER JOIN MembersT ON ContactMasterT.MasterID=MembersT.MasterID " & _
"SET MemberSpecial = 1 " & _
"WHERE MembersT.[MemberCardNo] " & _
"IN (SELECT Max(MemberCardNo) FROM MembersT AS M WHERE MasterID=ContactMasterT.MasterID)"

DoCmd.RunSQL SQL

Executing this from the subform command button updated 1414 of 1711 MembersT records (instead of one) on selection criteria that I can't figure out.

 

[CJ_London]

updated 1414 of 1711

implies you have 297 members without a card number or perhaps they have more than one card - and only one can be the 'max'

selection criteria that I can't figure out

Read the subquery

SELECT Max(MemberCardNo) FROM MembersT AS M WHERE MasterID=ContactMasterT.MasterID

Think of it as a query - it is saying 'find the biggest card number where the masterID = the masterid from another table'. You could just copy this subquery into another query and substitute ContactMasterT.MasterID with a number to see how it works

you need to alias as M ( or anything else you like) because you have that table in your query - which in fact you don't need. This query would do the same job

SQL = "UPDATE ContactMasterT " & _
""SET MemberSpecial = 1 " & _
"WHERE ContactMasterT.[MemberCardNo] " & _
"= (SELECT Max(MemberCardNo) FROM MembersT WHERE MasterID=ContactMasterT.MasterID)"

Note the MembersT table is now not joined to the ContactMasterT and the IN has been changed to =

 

[raugust]

I figured out that my 1414 updated records represented the MembersT record with the highest MemberCardNo for each unique MasterID in MembersT.

You are right, the sub-query works properly when tested for a specific MasterID. MemberCardNo is in MembersT, however. When I made a slight modification to my code:

SQL = "UPDATE MembersT " & _
"SET MemberSpecial = 1 " & _
"WHERE MemberCardNo " & _
"= (SELECT Max(MemberCardNo) FROM MembersT WHERE MembersT.MasterID=ContactMasterT.MasterID)"

it prompts for a value of ContactMasterT. MasterID, then correctly updates the right instance of that MasterID's record in MembersT. But it appears in that code that the sub-query and the main query are not connecting to provide a filtering value of MasterID from the subform. When I tried to restore a version of the inner join to the code as follows:

SQL = "UPDATE MembersT " & _
"INNER JOIN ContactMasterT ON ContactMasterT.MasterID=MembersT.MasterID " & _
"SET MemberSpecial = 1 " & _
"WHERE MemberCardNo " & _
"= (SELECT Max(MemberCardNo) FROM MembersT WHERE MembersT.MasterID=ContactMasterT.MasterID)"

... I am back to updating 1414 records.

 

[CJ_London]

That revised query won't work - because you have missed the alias off and also referring to a field which doesn't exist - hence the prompt. Try this instead

SQL = "UPDATE MembersT " & _
"SET MemberSpecial = 1 " & _
"WHERE MemberCardNo " & _
"= (SELECT Max(MemberCardNo) FROM MembersT AS Tmp WHERE MasterID=MembersT.MasterID)"

 

[raugust]

That still updates 1414 records, unfortunately.

 

[CJ_London]

What are you trying to do? Update all the records? Is so, you don't need the subquery

 

[raugust]

No, I want to put a flag on the MembersT record corresponding to the most recent record on MembersT (i.e. highest MemberCardNo) just for the individual from ContactMasterT whose MembersT records can be seen in a subform.

So each update query would only update one record in MembersT. The code is on a command button event on the subform.

The aim of this is to allow the operator to manually flag individual members for a later batch membership card print. Once the operator prints the batch, I would give them another update query command button to clear the flags.

 

[CJ_London]

OK, in that case your code will be something like this

SQL = "UPDATE MembersT SET MemberSpecial = 1 " & _
"WHERE MembersT.[MemberCardNo] " & _
"= (SELECT Max(MemberCardNo) FROM MembersT AS M WHERE MasterID=" & me.MasterID & ")"

 

[raugust]

That works perfectly, CJ, thank you very much. I'll have to spend some time studying the difference in the sub-query statement to understand why.