What's wrong with this query?

  • Thread starter Thread starter vvenk
  • Start date Start date
V

vvenk

I am new to SQL and your help is very much appreciated.

The Table VISIT_TYPE has a o-M relationship with PATIENT_VISIT.

I am trying to update the fields, Amount_Billed_N in PATIENT_VISIT with the
Fee_N from VISIT_TYPE based on Visit_Type_CD_C.

I have the statement below:

UPDATE PATIENT_VISIT AS A
SET A.AMOUNT_BILLED_N =
(
SELECT B.Charge_N
FROM VISIT_TYPE AS B
WHERE B.Visit_Type_CD_C = A.Visit_Type_CD_C
)
WHERE A.INVOICE_NO_N IS NULL

When I try to execute the query, I get the error, "Operation must be an
updateable query" suggesting that the query may not be syntactically correct.

Can somebody help me?

Thanks.

Venki
 
Not updateable is not a systax error, but just that, not updateable.

You can try:



UPDATE partient_visit
SET amount_billed_n = DLOOKUP("charge_n", "visit_type",
"visit_type_cd=""" & visit_type_cd_c & """" )



Vanderghast, Access MVP
 
Not updateable means that the query is not updateable. It is not a syntax error.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Try this version of your query.

UPDATE PATIENT_VISIT AS A INNER JOIN VISIT_TYPE as B
ON A.Visit_Type_CD_C = B.Visit_Type_CD_C
SET A.AMOUNT_BILLED_N = .[Charge_N]
WHERE A.INVOICE_NO_N IS NULL

One way to build this is to build a SELECT query that has the fields you want
to update and the information you want to use to update. If the select query
will allow you to change values then (in most cases) you can turn it into an
update query by selecting Query: Update from the menu.

Example:
This query should allow updates:
SELECT A.Visit_Type_CD_C , B.Visit_Type_CD_C
FROM PATIENT_VISIT AS A INNER JOIN VISIT_TYPE as B
ON A.Visit_Type_CD_C = B.Visit_Type_CD_C
WHERE A.INVOICE_NO_N IS NULL

This query would not be allow updates:
SELECT A.AMOUNT_BILLED_N. =
(
SELECT B.Charge_N
FROM VISIT_TYPE AS B
WHERE B.Visit_Type_CD_C = A.Visit_Type_CD_C
) as TheAmount
FROM Patient_VISIT as A
WHERE A.INVOICE_NO_N IS NULL

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
John:

Thanks. Your first statement worked.

I tried to extend it by adding one more field but I get the same cryptic
"not updateable" message:

UPDATE PATIENT_VISIT AS A INNER JOIN VISIT_TYPE AS B ON
A.Visit_Type_CD_C=B.Visit_Type_CD_C SET A.AMOUNT_BILLED_N = B.[Charge_N],
A.INV_NO_N=(SELECT MAX(INV_NO_N) FROM INVOICE)
WHERE A.INVOICE_NO_N IS NULL;

Can you suggest how I can do this?

Venki

John Spencer said:
Not updateable means that the query is not updateable. It is not a syntax error.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Try this version of your query.

UPDATE PATIENT_VISIT AS A INNER JOIN VISIT_TYPE as B
ON A.Visit_Type_CD_C = B.Visit_Type_CD_C
SET A.AMOUNT_BILLED_N = .[Charge_N]
WHERE A.INVOICE_NO_N IS NULL

One way to build this is to build a SELECT query that has the fields you want
to update and the information you want to use to update. If the select query
will allow you to change values then (in most cases) you can turn it into an
update query by selecting Query: Update from the menu.

Example:
This query should allow updates:
SELECT A.Visit_Type_CD_C , B.Visit_Type_CD_C
FROM PATIENT_VISIT AS A INNER JOIN VISIT_TYPE as B
ON A.Visit_Type_CD_C = B.Visit_Type_CD_C
WHERE A.INVOICE_NO_N IS NULL

This query would not be allow updates:
SELECT A.AMOUNT_BILLED_N. =
(
SELECT B.Charge_N
FROM VISIT_TYPE AS B
WHERE B.Visit_Type_CD_C = A.Visit_Type_CD_C
) as TheAmount
FROM Patient_VISIT as A
WHERE A.INVOICE_NO_N IS NULL

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I am new to SQL and your help is very much appreciated.

The Table VISIT_TYPE has a o-M relationship with PATIENT_VISIT.

I am trying to update the fields, Amount_Billed_N in PATIENT_VISIT with the
Fee_N from VISIT_TYPE based on Visit_Type_CD_C.

I have the statement below:

UPDATE PATIENT_VISIT AS A
SET A.AMOUNT_BILLED_N =
(
SELECT B.Charge_N
FROM VISIT_TYPE AS B
WHERE B.Visit_Type_CD_C = A.Visit_Type_CD_C
)
WHERE A.INVOICE_NO_N IS NULL

When I try to execute the query, I get the error, "Operation must be an
updateable query" suggesting that the query may not be syntactically correct.

Can somebody help me?

Thanks.

Venki
Click to expand...
Click to expand...
 
note that using max to get a value is not updateable. You can use the VBA
function DMAX in an expression to get the value.

A.Inv_No_N = DMax("inv_no_N","Invoice")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John:

Thanks. Your first statement worked.

I tried to extend it by adding one more field but I get the same cryptic
"not updateable" message:

UPDATE PATIENT_VISIT AS A INNER JOIN VISIT_TYPE AS B ON
A.Visit_Type_CD_C=B.Visit_Type_CD_C SET A.AMOUNT_BILLED_N = B.[Charge_N],
A.INV_NO_N=(SELECT MAX(INV_NO_N) FROM INVOICE)
WHERE A.INVOICE_NO_N IS NULL;

Can you suggest how I can do this?

Venki

John Spencer said:
Not updateable means that the query is not updateable. It is not a syntax error.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Try this version of your query.

UPDATE PATIENT_VISIT AS A INNER JOIN VISIT_TYPE as B
ON A.Visit_Type_CD_C = B.Visit_Type_CD_C
SET A.AMOUNT_BILLED_N = .[Charge_N]
WHERE A.INVOICE_NO_N IS NULL

One way to build this is to build a SELECT query that has the fields you want
to update and the information you want to use to update. If the select query
will allow you to change values then (in most cases) you can turn it into an
update query by selecting Query: Update from the menu.

Example:
This query should allow updates:
SELECT A.Visit_Type_CD_C , B.Visit_Type_CD_C
FROM PATIENT_VISIT AS A INNER JOIN VISIT_TYPE as B
ON A.Visit_Type_CD_C = B.Visit_Type_CD_C
WHERE A.INVOICE_NO_N IS NULL

This query would not be allow updates:
SELECT A.AMOUNT_BILLED_N. =
(
SELECT B.Charge_N
FROM VISIT_TYPE AS B
WHERE B.Visit_Type_CD_C = A.Visit_Type_CD_C
) as TheAmount
FROM Patient_VISIT as A
WHERE A.INVOICE_NO_N IS NULL

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I am new to SQL and your help is very much appreciated.

The Table VISIT_TYPE has a o-M relationship with PATIENT_VISIT.

I am trying to update the fields, Amount_Billed_N in PATIENT_VISIT with the
Fee_N from VISIT_TYPE based on Visit_Type_CD_C.

I have the statement below:

UPDATE PATIENT_VISIT AS A
SET A.AMOUNT_BILLED_N =
(
SELECT B.Charge_N
FROM VISIT_TYPE AS B
WHERE B.Visit_Type_CD_C = A.Visit_Type_CD_C
)
WHERE A.INVOICE_NO_N IS NULL

When I try to execute the query, I get the error, "Operation must be an
updateable query" suggesting that the query may not be syntactically correct.

Can somebody help me?

Thanks.

Venki
Click to expand...
Click to expand...
Click to expand...
 
John:

I am trying to accomplish the task using ANSI-SQL, even though the database
is Access.

Venki

John Spencer said:
note that using max to get a value is not updateable. You can use the VBA
function DMAX in an expression to get the value.

A.Inv_No_N = DMax("inv_no_N","Invoice")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John:

Thanks. Your first statement worked.

I tried to extend it by adding one more field but I get the same cryptic
"not updateable" message:

UPDATE PATIENT_VISIT AS A INNER JOIN VISIT_TYPE AS B ON
A.Visit_Type_CD_C=B.Visit_Type_CD_C SET A.AMOUNT_BILLED_N = B.[Charge_N],
A.INV_NO_N=(SELECT MAX(INV_NO_N) FROM INVOICE)
WHERE A.INVOICE_NO_N IS NULL;

Can you suggest how I can do this?

Venki

John Spencer said:
Not updateable means that the query is not updateable. It is not a syntax error.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Try this version of your query.

UPDATE PATIENT_VISIT AS A INNER JOIN VISIT_TYPE as B
ON A.Visit_Type_CD_C = B.Visit_Type_CD_C
SET A.AMOUNT_BILLED_N = .[Charge_N]
WHERE A.INVOICE_NO_N IS NULL

One way to build this is to build a SELECT query that has the fields you want
to update and the information you want to use to update. If the select query
will allow you to change values then (in most cases) you can turn it into an
update query by selecting Query: Update from the menu.

Example:
This query should allow updates:
SELECT A.Visit_Type_CD_C , B.Visit_Type_CD_C
FROM PATIENT_VISIT AS A INNER JOIN VISIT_TYPE as B
ON A.Visit_Type_CD_C = B.Visit_Type_CD_C
WHERE A.INVOICE_NO_N IS NULL

This query would not be allow updates:
SELECT A.AMOUNT_BILLED_N. =
(
SELECT B.Charge_N
FROM VISIT_TYPE AS B
WHERE B.Visit_Type_CD_C = A.Visit_Type_CD_C
) as TheAmount
FROM Patient_VISIT as A
WHERE A.INVOICE_NO_N IS NULL

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

vvenk wrote:
I am new to SQL and your help is very much appreciated.

The Table VISIT_TYPE has a o-M relationship with PATIENT_VISIT.

I am trying to update the fields, Amount_Billed_N in PATIENT_VISIT with the
Fee_N from VISIT_TYPE based on Visit_Type_CD_C.

I have the statement below:

UPDATE PATIENT_VISIT AS A
SET A.AMOUNT_BILLED_N =
(
SELECT B.Charge_N
FROM VISIT_TYPE AS B
WHERE B.Visit_Type_CD_C = A.Visit_Type_CD_C
)
WHERE A.INVOICE_NO_N IS NULL

When I try to execute the query, I get the error, "Operation must be an
updateable query" suggesting that the query may not be syntactically correct.

Can somebody help me?

Thanks.

Venki
Click to expand...
Click to expand...
Click to expand...
 
I don't know of a way to do this using ANSI-SQL or even Access SQL against a
JET database without using a VBA function.

(Jet is the native engine for Access up to Access 2007)

You could use VBA to build a query string and execute that string. You would
be placing a literal value in the query for the invoice number.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John:

I am trying to accomplish the task using ANSI-SQL, even though the database
is Access.

Venki

John Spencer said:
note that using max to get a value is not updateable. You can use the VBA
function DMAX in an expression to get the value.

A.Inv_No_N = DMax("inv_no_N","Invoice")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John:

Thanks. Your first statement worked.

I tried to extend it by adding one more field but I get the same cryptic
"not updateable" message:

UPDATE PATIENT_VISIT AS A INNER JOIN VISIT_TYPE AS B ON
A.Visit_Type_CD_C=B.Visit_Type_CD_C SET A.AMOUNT_BILLED_N = B.[Charge_N],
A.INV_NO_N=(SELECT MAX(INV_NO_N) FROM INVOICE)
WHERE A.INVOICE_NO_N IS NULL;

Can you suggest how I can do this?

Venki

:

Not updateable means that the query is not updateable. It is not a syntax error.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Try this version of your query.

UPDATE PATIENT_VISIT AS A INNER JOIN VISIT_TYPE as B
ON A.Visit_Type_CD_C = B.Visit_Type_CD_C
SET A.AMOUNT_BILLED_N = .[Charge_N]
WHERE A.INVOICE_NO_N IS NULL

One way to build this is to build a SELECT query that has the fields you want
to update and the information you want to use to update. If the select query
will allow you to change values then (in most cases) you can turn it into an
update query by selecting Query: Update from the menu.

Example:
This query should allow updates:
SELECT A.Visit_Type_CD_C , B.Visit_Type_CD_C
FROM PATIENT_VISIT AS A INNER JOIN VISIT_TYPE as B
ON A.Visit_Type_CD_C = B.Visit_Type_CD_C
WHERE A.INVOICE_NO_N IS NULL

This query would not be allow updates:
SELECT A.AMOUNT_BILLED_N. =
(
SELECT B.Charge_N
FROM VISIT_TYPE AS B
WHERE B.Visit_Type_CD_C = A.Visit_Type_CD_C
) as TheAmount
FROM Patient_VISIT as A
WHERE A.INVOICE_NO_N IS NULL

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

vvenk wrote:
I am new to SQL and your help is very much appreciated.

The Table VISIT_TYPE has a o-M relationship with PATIENT_VISIT.

I am trying to update the fields, Amount_Billed_N in PATIENT_VISIT with the
Fee_N from VISIT_TYPE based on Visit_Type_CD_C.

I have the statement below:

UPDATE PATIENT_VISIT AS A
SET A.AMOUNT_BILLED_N =
(
SELECT B.Charge_N
FROM VISIT_TYPE AS B
WHERE B.Visit_Type_CD_C = A.Visit_Type_CD_C
)
WHERE A.INVOICE_NO_N IS NULL

When I try to execute the query, I get the error, "Operation must be an
updateable query" suggesting that the query may not be syntactically correct.

Can somebody help me?

Thanks.

Venki
Click to expand...
Click to expand...
Click to expand...
 
Back
Top