Update query using iif and select

[josh2009]

Hi,



I am trying to write an update query that is baed on certain conditions. We have an interface that uses MS access and working with pre-import sql rules. Here is my query -

Update Txtrpt a Inner Join TxtRpt b On a.ss_intermediate_id <> b.ss_intermediate_id and a.patientid = b.patientid and a.documentid =  b.documentid Set a.ss_process = IIf(Count(*)>1,  "More than 1 record", "Only 1 record") WHERE a.TxtType ="TX" AND a.SS_Intermediate_ID = 6 and  a.ss_intermediate_id <> b.ss_intermediate_id

I get the ff error message though -
You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)

I can write Transact-SQL stored procedures for conditional update queries but having trouble with MS Access. What I really need to accomplish is based on a condition, I will be able to run an update query whether the condition yields a true or false.

I've tried the ff -

IIF((Select count(*) From TxtRpt a Inner Join TxtRpt b On a.ss_intermediate_id <> b.ss_intermediate_id and a.patientid = b.patientid and a.documentid =  b.documentid Where a.TxtType="TX" AND a.SS_Intermediate_ID=6) > 1, "More than 1 record", "Only 1 record")

And it gives the error -

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. (Error 3129)

Like I said, I am hoping to write a SQL in MS Access to be able to condition an update query. Here is the condition that I'd like to use and this select query actually works in access -

Select  IIf(Count(*)>1,  "More than 1 record", "Only 1 record") From TxtRpt a Inner Join TxtRpt b On a.ss_intermediate_id <> b.ss_intermediate_id and a.patientid = b.patientid and a.documentid =  b.documentid WHERE a.TxtType ="TX" AND a.SS_Intermediate_ID = 6

And then based on the above condition, be able to run an update query based on the result if true or false.

Any help will be greatly appreciated. Thanks

[Fre]

What is the problem that you are trying to solve?

Not knowing exactly what real world problem you're trying to solve, I'll take a stab at adjusting the SQL.

First off, I'm going to break this into two update queries because Access is weird that way.

Update Txtrpt a Inner Join TxtRpt b On a.ss_intermediate_id <> b.ss_intermediate_id and a.patientid = b.patientid and a.documentid = b.documentid
Set a.ss_process = "Only 1 record"
WHERE count(select * from Txtrpt a Inner Join TxtRpt b On a.ss_intermediate_id <> b.ss_intermediate_id and a.patientid = b.patientid and a.documentid = b.documentid)=1 AND a.TxtType ="TX" AND a.SS_Intermediate_ID = 6 and a.ss_intermediate_id <> b.ss_intermediate_id

Update Txtrpt a Inner Join TxtRpt b On a.ss_intermediate_id <> b.ss_intermediate_id and a.patientid = b.patientid and a.documentid = b.documentid
Set a.ss_process = "More than 1 record"
WHERE count(select * from Txtrpt a Inner Join TxtRpt b On a.ss_intermediate_id <> b.ss_intermediate_id and a.patientid = b.patientid and a.documentid = b.documentid)>1 AND a.TxtType ="TX" AND a.SS_Intermediate_ID = 6 and a.ss_intermediate_id <> b.ss_intermediate_id

Good luck