Access Update Query with Subqueries

[LevelThought]

I am interested in updating my local access table with a update query and having some difficulty trying to build this in the Access QBE environment.

How should the following calculated fields be input in Access QBE?

"CoveredCharges", "AllowedAmt", and "LatestPayorPaymentDate"


The SQL statement is displayed below:

SELECT AL3.TOTAL_CHARGES - Sum (AL2.NONCOVERED_DT_CHARGES +
AL2.NONCOVERED_IP_CHARGES) AS CoveredCharges, AL3.TOTAL_CHARGES -
Sum (AL6.ADJUSTMENT_AMOUNT) AS AllowedAmt,
AL8.PAYMENT_DATE AS LatestPayorPaymentDate
FROM ENCOUNTER_PAYMENT_DETAIL AL1, ENCOUNTER_PAYOR AL2, PATIENT_ENCOUNTER AL3,
ENCOUNTER_TRANSACTION_DETAILS AL6 WHERE (AL2.ACCOUNT_NO=AL3.ACCOUNT_NO AND
AL1.ACCOUNT_NO=AL3.ACCOUNT_NO AND AL6.ACCOUNT_NO=AL3.ACCOUNT_NO AND
AL6.TRANSACTION_CODE in ('47003','47009','47010','47011','47012') AND
Max (AL1.PAYMENT_DATE) IN (SELECT AL8.PAYMENT_DATE FROM
ENCOUNTER_PAYMENT_DETAIL AL8 WHERE (AL8.TRANSACTION_CODE in
('48601','48602','48603','48604','48605','48606')))

 

[dhookom]

I doubt this could ever be an udpateable query in Access/Jet. You have Sum()s without GROUP BY.

One work-around is to use DSum() or other similar functions. You can also try user-defined functions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

Another point, storing calculated values breaks normalization rules and there should be a valid reason for breaking these rules.

Why are you trying to store calculated values?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[LevelThought]

Basically, at the request of Management, I am required to store the calculated values in a Access table and update several columns (that contain calculated values) on a daily basis!!

After approximately 3 months trying to figure out the best method, I definitely understand why calculated values should not be stored in a table.

With that said, the following questions arise;

Are there instances where calculated values are stored in a Access table for archival purposes? If so, what is the most efficient and timely method to update the values?

The manual method that I have tested involves 3 queries of the Oracle database using Brio (Hyperion Intelligence).

Query 1 extracts all accounts that had a payment that interfaced the Oracle database today (date_updated = sysdate). Note, the payments received could be the initial payment from the customer and then, a new record needs to be appended to the database. However, if the payment is an additional payment for a customer, then several fields should be updated for the customer (who should already be within the database).

Is it possible to update a table by first "bringing in" the components of a calculated field and storing on a form. Then, populate the table with the result on a daily basis. In that case, what if components of a calculated field does not change? For instance, I have the following calculated field:
CoveredCharges = TotalCharges - Sum(AL2.NONCOVERED_DT_CHARGES +
AL2.NONCOVERED_IP_CHARGES)

Say, if "Noncovered_ip_charges" changes.

Isn't there a way to make the Access table a linked server or something whereby everytime there is a change in any of the fields within Oracle, the fields within the Access table are "automatically" changed?

Just trying to solve this problem...

Thanks in advance for any insight.

 

[PHV]

Just a thought, why not using stored procedures and triggers in Oracle ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LevelThought]

More thoughts,

Is it possible to daily update the non-calculated fields within my local Access table using an Access update query similar to the following?:

Update tblHospVarRpt_TestUpdateJuly20 INNER JOIN (((Account_Payment_Detail INNER JOIN Account_Payor ON Account_Payment_Detail.AccountNo = Account_Payor.AccountNo) INNER JOIN Account_Transaction_Details ON Account_Payment_Detail.AccountNo = Account_Transaction_Details.AccountNo)
INNER JOIN Patient_Account ON (Account_Payment_Detail.AccountNo = Patient_Account.AccountNo) AND (Account_Transaction_Details.AccountNo =
Patient_Account.AccountNo) AND (Account_Payor.AccountNo = Patient_Account.AccountNo)) ON tblHospVarRpt_TestUpdateJuly20.AccountNo =
Patient_Account.AccountNo SET
tblHospVarRpt_TestUpdateJuly20.CIDCurrent=Account_Payor.CONTRACT_ID,tblHospVarRpt_TestUpdateJuly20.TotInsPymtsCurrent=Account_Payor.Total_Payments, tblHospVarRpt_TestUpdateJuly20.TotPymts=Patient_Account.TOTAL_PAYMENTS,...
Where tblHospVarRpt.AccountNo in
(Select AccountNo from Account_Payment_Detail, Account_Payor, Patient_Account Where
Account_Payment_Detail.date_updated=trunc(sysdate) or Account_Payor.date_updated=trunc(sysdate) or Patient_Account.date_updated=trunc(sysdate);

Is the syntax correct?

In essence, I am interested in updating several fields on the records within the local access database - only the records that had a field updated within the Oracle database on the current day...

Then, if I understand correctly, I can update the calculated fields within my local Access table via a form using the "After Update" property of the text boxes that would each contain the components of the calculated field.

Would I need a text box for each component of a calculated field?

How do I setup the update of the textboxes that contain the components of the calculated fields to occur when there is a account within the access database that have a field that was updated on the current day?