473,322 Members | 1,699 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Complex Update query

Hello everyone,

Please help me revise this query to make it work. I don't know much about update query so I will need some help with it. Thanks,


update encounter
set mergedpayment = totalactualcashpayments
from encounter E, paymentITEM P, encounterfinancialrecord F
where (E.encounterid = P.FINANCIALRECNUM) AND (E.encounterid = F.FINANCIALRECNUM) and (transtypecode = 'C') and (F.INSPLANCODE not in ('UNINSURED', 'SP', 'Selfpay'));
May 8 '06 #1
2 5790
cweiss
36
Try this:

Expand|Select|Wrap|Line Numbers
  1. UPDATE encounter E
  2. SET E.mergedpayment = (
  3.     SELECT <TableAlias>.totalactualcashpayments
  4.     FROM 
  5.        paymentITEM P, 
  6.        encounterfinancialrecord F
  7.     WHERE
  8.        E.encounterid = P.financialrecnum AND
  9.        E.encounterid = F.financialrecnum AND
  10.     <TableAlias>.transtypecode = 'C' AND 
  11.        F.insplancode NOT IN('UNINSURED', 'SP', 'Selfpay'));
May 10 '06 #2
It worked!! Thank you so much.


Try this:

Expand|Select|Wrap|Line Numbers
  1. UPDATE encounter E
  2. SET E.mergedpayment = (
  3.     SELECT <TableAlias>.totalactualcashpayments
  4.     FROM 
  5.        paymentITEM P, 
  6.        encounterfinancialrecord F
  7.     WHERE
  8.        E.encounterid = P.financialrecnum AND
  9.        E.encounterid = F.financialrecnum AND
  10.     <TableAlias>.transtypecode = 'C' AND 
  11.        F.insplancode NOT IN('UNINSURED', 'SP', 'Selfpay'));
May 10 '06 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Christoph Bisping | last post by:
Hello! I'm seeking advice on a rather complex type of query I need to build in an Access ADP (SQL-Server 7). There are four tables: tblPeople ID(PK) PRENAME --------------- 1 Thomas 2 Frank
1
by: ravi | last post by:
I have created the following interest to calculate the interest for the following currency pairs. I have tried to combine them in macros using conditions but the next query that is run in the macro...
6
by: someguy | last post by:
Hi, I'm trying to accomplish the following on a form on which i have removed navigational buttons (I apologize in advance if this is too many questions); The two easy ones are.... 1) When...
1
by: Joe | last post by:
Dear Friends, How do write the below dynamic update Query? I am new to this SQL Query.Its very very urgent requirements. If anyone knows please help me the below SQL query. I would appreciate...
2
by: Ben de Vette | last post by:
Hi, I'm using the querybuilder when updating a record in a table (Access). However, I get a "Query is too complex" message. The Primary key is autonumbered. Why is it making such a complex...
1
by: Mark Dexter | last post by:
In Microsoft SQL Server, I can write an UPDATE query as follows: update orders set RequiredDate = (case when c.City IN ('Seattle','Portland') then o.OrderDate + 2 else o.OrderDate + 1 end)...
3
by: Slower Than You | last post by:
I am trying to write an SQL UPDATE statement for an MSAccess table and am having some problems getting my head around it. Can anyone help? TableName: CustTransactions TransactionKey AutoNumber ...
1
by: koehlerc14 | last post by:
So here is the deal, I am attempting to make what is the most complex form i have made yet. It really is not much, but as an amatuer it is a little overwhelming. Here's a few critical background...
1
by: Rudolf Bargholz | last post by:
Hi, We have created triggers to log modifications to tables in our application. The triggers work fine, just on one of the tables in our database the triggers fail with the error message...
11
by: sloney | last post by:
Hello All! I have an update query that is rather large and I keep getting a "Query is too complex" error. Does anyone know the limitations on update queries for MS Access 2000? I am running on...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.