Error message : Operation must use an updateable query on update query

[vba317]

I am getting this message when I run this update query that I have developed. I don't know what causes this type of error. I would appreciate any help that someone could provide

UPDATE (dbo_bi_PatientData INNER JOIN dbo_rpt_dat_AdjustmentDetail ON (dbo_bi_PatientData.aid = dbo_rpt_dat_AdjustmentDetail.aid) AND (dbo_bi_PatientData.clntid = dbo_rpt_dat_AdjustmentDetail.clntid)) INNER JOIN tblARDetail ON (dbo_bi_PatientData.AcctNu = tblARDetail.AcctNu) AND (dbo_bi_PatientData.PatName = tblARDetail.PatName) SET dbo_rpt_dat_AdjustmentDetail.adjamt = [tblARDetail.wo]
WHERE (((dbo_rpt_dat_AdjustmentDetail.UCI)=[Enter UCI]) AND ((dbo_rpt_dat_AdjustmentDetail.rptpd)=377) AND ((dbo_rpt_dat_AdjustmentDetail.trantype)=3));

 

[BigRed1212]

Do you get the fields you want to update when you run this?

select dbo_rpt_dat_AdjustmentDetail.adjamt
from (dbo_rpt_dat_AdjustmentDetail
INNER JOIN dbo_bi_PatientData ON (dbo_rpt_dat_AdjustmentDetail.aid= dbo_bi_PatientData.aid) 
AND (dbo_rpt_dat_AdjustmentDetail.clntid = dbo_bi_PatientData.clntid)) 
INNER JOIN tblARDetail ON (dbo_bi_PatientData.AcctNu = tblARDetail.AcctNu) 
AND (dbo_bi_PatientData.PatName = tblARDetail.PatName) 
WHERE (dbo_rpt_dat_AdjustmentDetail.UCI=[Enter UCI]) 
AND (dbo_rpt_dat_AdjustmentDetail.rptpd=377) 
AND (dbo_rpt_dat_AdjustmentDetail.trantype)=3);

That may have a dropped paren or two in there so you might have to tweak it.

I would think you would want to perfect that query and join before working it into an update.

 

[vba317]

The select query that I use to get this information is:

SELECT dbo_rpt_dat_AdjustmentDetail.UCI, dbo_rpt_dat_AdjustmentDetail.rptpd, dbo_rpt_dat_AdjustmentDetail.transagnst, dbo_bi_PatientData.PatName, dbo_bi_PatientData.AcctNu, dbo_rpt_dat_AdjustmentDetail.adjcat, dbo_rpt_dat_AdjustmentDetail.trantype, Sum(dbo_rpt_dat_AdjustmentDetail.adjamt) AS writeoff
FROM dbo_bi_PatientData INNER JOIN (dbo_rpt_dat_AdjustmentDetail INNER JOIN dbo_rpt_FYInfo ON (dbo_rpt_dat_AdjustmentDetail.rptpd = dbo_rpt_FYInfo.rptpd) AND (dbo_rpt_dat_AdjustmentDetail.UCI = dbo_rpt_FYInfo.uci)) ON (dbo_bi_PatientData.aid = dbo_rpt_dat_AdjustmentDetail.aid) AND (dbo_bi_PatientData.clntid = dbo_rpt_dat_AdjustmentDetail.clntid)
GROUP BY dbo_rpt_dat_AdjustmentDetail.UCI, dbo_rpt_dat_AdjustmentDetail.rptpd, dbo_rpt_dat_AdjustmentDetail.transagnst, dbo_bi_PatientData.PatName, dbo_bi_PatientData.AcctNu, dbo_rpt_dat_AdjustmentDetail.adjcat, dbo_rpt_dat_AdjustmentDetail.trantype
HAVING (((dbo_rpt_dat_AdjustmentDetail.UCI)=[Enter UCI]) AND ((dbo_rpt_dat_AdjustmentDetail.rptpd)=377) AND ((dbo_rpt_dat_AdjustmentDetail.adjcat)="WRITE_OFF") AND ((dbo_rpt_dat_AdjustmentDetail.trantype)=3))
ORDER BY dbo_rpt_dat_AdjustmentDetail.transagnst, dbo_rpt_dat_AdjustmentDetail.adjcat;

 

[BigRed1212]

The select query that I use to get this information is:

And "this information" would be the fields you want to update???

That query has a 7 fields and a calculated sum. You can't update a calculated sum...

Maybe you could spell out in words exactly what you are trying to do? That often helps me translate my thoughts into proper syntax. In your initial query, for example, I would have guessed you were trying to update the field adjamt in the dbo_rpt_dat_AdjustmentDetail table for records where dbo_rpt_dat_AdjustmentDetail.aid equals dbo_bi_PatientData.aid, dbo_rpt_dat_AdjustmentDetail.clntid equals dbo_bi_PatientData.clntid, dbo_bi_PatientData.AcctNu equals tblARDetail.AcctNu, and dbo_bi_PatientData.PatName equals tblARDetail.PatName and for where dbo_rpt_dat_AdjustmentDetail.UCI equals some entered value (maybe a problem), dbo_rpt_dat_AdjustmentDetail.rptpd equals 377, and dbo_rpt_dat_AdjustmentDetail.trantype=3.

 

[vba317]

Sorry for the confusion. I didn't realize that you couldn't use a calculated field in an update query. I have changed that in my current query.
In english,
if (((dbo_rpt_dat_AdjustmentDetail.UCI)="VIT" AND
dbo_rpt_dat_AdjustmentDetail.rptpd equals 377 AND
dbo_rpt_dat_AdjustmentDetail.trantype=3 AND
dbo_rpt_dat_AdjustmentDetail.adjcat)="WRITE_OFF"

Link the following tables
(dbo_rpt_dat_AdjustmentDetail.rptpd = dbo_rpt_FYInfo.rptpd)
(dbo_rpt_dat_AdjustmentDetail.UCI = dbo_rpt_FYInfo.uci)
(dbo_bi_PatientData.aid = dbo_rpt_dat_AdjustmentDetail.aid)
(dbo_bi_PatientData.clntid = dbo_rpt_dat_AdjustmentDetail.clntid)

My original goal is to design a query to get information from about ten tables. I was finding that I was stopping the query after about an hour with no results. So I decided to change my strategy and develop an append query for the bulk of the information. This append query works fine and I get about 12500 records into my database. What I am asking help with is my first update query to add records to the writeoff field.
I figured that if I can crack this updateable issue that other queries should be the similar. I just discovered that I have many duplicate records that I am working on a procedure to delete the duplicate records.

SELECT dbo_rpt_dat_AdjustmentDetail.UCI, dbo_rpt_dat_AdjustmentDetail.rptpd, dbo_rpt_dat_AdjustmentDetail.transagnst, dbo_bi_PatientData.PatName, dbo_bi_PatientData.AcctNu, dbo_rpt_dat_AdjustmentDetail.adjcat, dbo_rpt_dat_AdjustmentDetail.trantype, dbo_rpt_dat_AdjustmentDetail.adjamt AS writeoff
FROM dbo_bi_PatientData INNER JOIN (dbo_rpt_dat_AdjustmentDetail INNER JOIN dbo_rpt_FYInfo ON (dbo_rpt_dat_AdjustmentDetail.rptpd = dbo_rpt_FYInfo.rptpd) AND (dbo_rpt_dat_AdjustmentDetail.UCI = dbo_rpt_FYInfo.uci)) ON (dbo_bi_PatientData.aid = dbo_rpt_dat_AdjustmentDetail.aid) AND (dbo_bi_PatientData.clntid = dbo_rpt_dat_AdjustmentDetail.clntid)
GROUP BY dbo_rpt_dat_AdjustmentDetail.UCI, dbo_rpt_dat_AdjustmentDetail.rptpd, dbo_rpt_dat_AdjustmentDetail.transagnst, dbo_bi_PatientData.PatName, dbo_bi_PatientData.AcctNu, dbo_rpt_dat_AdjustmentDetail.adjcat, dbo_rpt_dat_AdjustmentDetail.trantype, dbo_rpt_dat_AdjustmentDetail.adjamt
HAVING (((dbo_rpt_dat_AdjustmentDetail.UCI)="VIT") AND ((dbo_rpt_dat_AdjustmentDetail.rptpd)=377) AND ((dbo_rpt_dat_AdjustmentDetail.adjcat)="WRITE_OFF") AND ((dbo_rpt_dat_AdjustmentDetail.trantype)=3))
ORDER BY dbo_rpt_dat_AdjustmentDetail.transagnst, dbo_bi_PatientData.PatName, dbo_rpt_dat_AdjustmentDetail.adjcat;

 

[BigRed1212]

In english,
if (((dbo_rpt_dat_AdjustmentDetail.UCI)="VIT" AND
dbo_rpt_dat_AdjustmentDetail.rptpd equals 377 AND
dbo_rpt_dat_AdjustmentDetail.trantype=3 AND
dbo_rpt_dat_AdjustmentDetail.adjcat)="WRITE_OFF"

Link the following tables
(dbo_rpt_dat_AdjustmentDetail.rptpd = dbo_rpt_FYInfo.rptpd)
(dbo_rpt_dat_AdjustmentDetail.UCI = dbo_rpt_FYInfo.uci)
(dbo_bi_PatientData.aid = dbo_rpt_dat_AdjustmentDetail.aid)
(dbo_bi_PatientData.clntid = dbo_rpt_dat_AdjustmentDetail.clntid)

Yes, but what do you want to update? The UPDATE query changes the value of a field(s) for an existing record. The joins and the conditions you have specified above may create a set of very specific records but they don't tell us which field(s) in which table you want to update with what value(s).

 

[vba317]

For this query I want to update the adjamt column in the tblARDetail table with the adjamt column from the dbo_rpt_dat_AdjustmentDetail table

 

[Andrzejek]

Just a side note,
Your SQLs may be easier to read if you would use short aliases for your tables:

SELECT AdjD.UCI, AdjD.rptpd, AdjD.transagnst, PD.PatName, 
PD.AcctNu, AdjD.adjcat, AdjD.trantype, AdjD.adjamt AS writeoff
FROM [blue]dbo_bi_PatientData PD[/blue] INNER JOIN ([blue]dbo_rpt_dat_AdjustmentDetail AdjD [/blue]
INNER JOIN [blue]dbo_rpt_FYInfo FYI[/blue] ON (AdjD.rptpd = FYI.rptpd) 
AND (AdjD.UCI = FYI.uci)) ON (PD.aid = AdjD.aid) 
AND (PD.clntid = AdjD.clntid)
GROUP BY AdjD.UCI, AdjD.rptpd, AdjD.transagnst, PD.PatName, PD.AcctNu, 
AdjD.adjcat, AdjD.trantype, AdjD.adjamt
HAVING (((AdjD.UCI)="VIT") 
AND ((AdjD.rptpd)=377) 
AND ((AdjD.adjcat)="WRITE_OFF") 
AND ((AdjD.trantype)=3))
ORDER BY AdjD.transagnst, PD.PatName, AdjD.adjcat;

Just one guy’s opinion


Have fun.

---- Andy

 

[vba317]

Andy,
You are absolutely correct! I will try to provide aliases in the future.

Tom

 

[vba317]

After thinking about this issue last night I came to the realization that I can't resolve this issue. I think I am getting this error message because the tables I am using can't be joined the way I am joining them because the tables I am using are ODBC connections from an SQL database. Today I am going to try and write a select query in SQL and see if I can get the results I need.

Tom