What's wrong with this query?

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
 
V

vanderghast

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
 
J

John Spencer

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
 
V

vvenk

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...
 
J

John Spencer

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...
 
V

vvenk

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...
 
J

John Spencer

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access update query 2
Crosstab query as data source for update query 7
Update query problem 1
What's wrong with this UPDATE stmt? 13
update query 2
Subquery nested in Update query 1
What's wrong with this query?! 5
Update Query based on calc. Field in another query 2

Top