Access Query - Group by 4

[LevelThought]

I have a Microsoft Access Query that extracts adjustment amounts from a linked Oracle table. Specifically, there are 5 types of adjustments that are recorded within the Oracle table in a field titled "Adjustment Amount" with a unique transaction code; Charge Adj (code 4600), Review Adj (code 4700), Vendor Adj (code 4800), Mfg Adj (code 4900), and Final Adj (5000).

Current Access SQL is:

SELECT DISTINCTROW ACCOUNT_PAYMENT_DETAIL.ACCOUNT_NO AS AcctNo, ACCOUNT_PAYOR.ACCOUNT_ID AS AcctCurrent, ACCOUNT_DETAILS.ADJUSTMENT_AMOUNT AS ChargeAdj, ACCOUNT_DETAILS.ADJUSTMENT_AMOUNT AS ReviewAdj, ACCOUNT_DETAILS.ADJUSTMENT_AMOUNT AS VendorAdj, ACCOUNT_DETAILS.ADJUSTMENT_AMOUNT AS MfgAdj, ACCOUNT_DETAILS.ADJUSTMENT_AMOUNT AS FinalAdj
FROM CUSTOMER INNER JOIN (((ACCOUNT_PAYMENT_DETAIL INNER JOIN ACCOUNT_PAYOR ON ACCOUNT_PAYMENT_DETAIL.ACCOUNT_NO = ACCOUNT_PAYOR.ACCOUNT_NO) INNER JOIN ACCOUNT_DETAILS ON
ACCOUNT_PAYMENT_DETAIL.ACCOUNT_NO = ACCOUNT_DETAILS.ACCOUNT_NO) INNER JOIN REVIEW_ENCOUNTER ON
(ACCOUNT_PAYOR.ACCOUNT_NO = REVIEW_ENCOUNTER.ACCOUNT_NO) AND (ACCOUNT_DETAILS.ACCOUNT_NO = REVIEW_ENCOUNTER.ACCOUNT_NO) AND (ACCOUNT_PAYMENT_DETAIL.ACCOUNT_NO =
REVIEW_ENCOUNTER.ACCOUNT_NO)) ON CUSTOMER.PATIENT_NO = REVIEW_ENCOUNTER.PATIENT_NO
GROUP BY ACCOUNT_PAYMENT_DETAIL.ACCOUNT_NO, ACCOUNT_PAYOR.ACCOUNT_ID,
REVIEW_ENCOUNTER.TOTAL_CHARGES, REVIEW_ENCOUNTER.EXPECTED_PAYMENT,
ACCOUNT_PAYOR.TOTAL_PAYMENTS, REVIEW_ENCOUNTER.TOTAL_PAYMENTS,
ACCOUNT_DETAILS.ADJUSTMENT_AMOUNT, ACCOUNT_DETAILS.ADJUSTMENT_AMOUNT,
ACCOUNT_DETAILS.ADJUSTMENT_AMOUNT, ACCOUNT_DETAILS.ADJUSTMENT_AMOUNT,
ACCOUNT_DETAILS.ADJUSTMENT_AMOUNT,
CDate(Format([ACCOUNT_PAYMENT_DETAIL].[DATE_INSERTED],"m/d/yy")),
ACCOUNT_DETAILS.TRANSACTION_CODE
HAVING (((ACCOUNT_PAYOR.ACCOUNT_ID) Not In ("FVGB","GVGB","GVGC")) AND
((CDate(Format([ACCOUNT_PAYMENT_DETAIL].[DATE_INSERTED],"m/d/yy")))=Date()-1) AND
((ACCOUNT_DETAILS.TRANSACTION_CODE) In ('4600','4700','4800','4900','5000')))
ORDER BY ACCOUNT_PAYMENT_DETAIL.ACCOUNT_NO, ACCOUNT_PAYOR.ACCOUNT_ID;

Current Results are something similar to:

AcctNo---ChargeAdj---AdjAmt
245VB----10000-------20
245VB----10000-------250
245VB----10000-------10
245VB----10000-------20


Desired results are:

AcctNo---ChargeAdj---ReviewAdj--VendorAdj--MfgAdj--FinalAdj
245VB----10000-------20---------250--------10------20
245VC----20000-------45---------200--------25------25

How should I revise the Access Query within the QBE area to display the different adjustments in separate columns in my results?

 

[LittleSmudge]

Can you modify your existing query to add an additional field in the output which contains the Adjustment Types

Eg

AcctNo---ChargeAdj---AdjAmt---AdjType
245VB----10000-------20-----ReviewAdj
245VB----10000-------250----VendorAdj
245VB----10000-------10-----MfgAdj
245VB----10000-------20-----FinalAdj


And then you just need to put that whole lot into a Transform ( CrossTab Query )

Something like
TRANSFORM Sum(ACCOUNT_DETAILS.ADJUSTMENT_AMOUNT ) As AdjAmt
{your existing SELECT query goes here }
PIVOT AdjType




'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[LevelThought]

Thanks for the insight,

Ideally, I would like to maintain 1 record per account number in the query results where there is no duplication in the fields.

Then, I plan to base a form on the query that will be used to update values within a local Access table on a daily basis.

However, I will try the suggestion and post back on the results.

 

[LevelThought]

Currently, the QBE section looks similar to this:

Field:----ChargeAdj:AdjustmentAmt----RevAdj:AdjustmentAmt
Table:----AccountDetails-------------AccountDetails-----
Total:----GroupBy--------------------GroupBy
Sort:
Show:
Criteria: TransCode='4600'-----------TransCode='4700'

It appears that one cannot construct the query like this.

Also, there are over 150 Adjustment Types!


Any thoughts?

 

[PCLewis]

What you are trying to do is called a crosstab query in Access. There is a limit to the number of columns though - maybe about 250 or so.

What you should do, is start with a crosstab query that the wizard creates and then build up your criteria from there.

I'd recommend considering using a stored procedure in Oracle instead though. Especially if you have a lot of records to query.

 

[LevelThought]

Upon reviewing this "problem" in detail, I believe that there is more than 1 way to resolve.

Specifically, could I use a case statement (similar to the example below) to create columns that sum the adjustment amount conditionally based on the transaction code? Further, I only have 5 transaction codes and could just create 5 columns named after the unique transaction codes.

Example 1, Sample Case Statement

SELECT ...,
SUM(dbo.[Account_Details.Adjustment_Amount].Subtotal) AS [TotalAdj],
SUM(CASE Transaction_Code(Transaction_Code) WHEN 4600 THEN dbo.[Adjustment Amount].Subtotal ELSE 0 END) AS [ChargeAdj],
SUM(CASE Transaction_Code(Transaction_Code) WHEN 4700 THEN dbo.[Adjustment Amount].Subtotal ELSE 0 END) AS [ReviewAdj],
SUM(CASE Transaction_Code(Transaction_Code) WHEN 4800 THEN dbo.[Adjustment Amount].Subtotal ELSE 0 END) AS [VendorAdj],
SUM(CASE Transaction_Code(Transaction_Code) WHEN 4900 THEN dbo.[Adjustment Amount].Subtotal ELSE 0 END) AS [MfgAdj],
SUM(CASE Transaction_Code(Transaction_Code) WHEN 5000 THEN dbo.[Adjustment Amount].Subtotal ELSE 0 END) AS [FinalAdj],
FROM ...


For discussion sake, why would the use of a cross-tab query be preferred over the Case statement in this situation?

I would like to use this query to eventually update several fields within a local Access table on a daily basis.

 

[PHV]

Have a look at the IIf function for JetSQL:
,SUM(IIf(Transaction_Code=5000, [Adjustment Amount].Subtotal, 0)) AS [FinalAdj]

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

 

[sjck]

I had a very similar problem (thread: Update Table/column with info in a different table & multiple Columns) and PHV's code with the IFF worked great

 

[LevelThought]

The Cross Tab worked!

Can I base a form on a cross-tab query?

Ideally, what I hope to accomplish is to use a form to update all records within the local Access table on a daily basis. (Note, there are approximately 5000 records within the database. Instead of specifying the update of particular records, I will update all records within the local Access table...)

For example,

I plan to place the following code in the "After Update" event of the form:

Dim BalAfterPymt as integer
BalAfterPymt = ExpPymt - TotalPymt

CurrentDB.execute "UpdatetblReview Set BalAfterPymt = " &BalAfterPymt &" where AcctNo=" & txtAcctNo

Any additional insight as to the most effective method to update all records, using a cross tab query, within the local Access table that contain calculated fields would be appreciated.

 

[PCLewis]

If you were asking about editing records within the crosstab query then certainly you cannot.

You can certainly have a crosstab query as the recordsource of a form but of course, no edits are allowed.

I notice you useing the Case..End SQL in your example above. If you are more at home with Oracle PL/SQL then I you could stick with that. The IIF statement in Access is like like a single clause DECODE statement. The equivalent to a DECODE in access is SWITCH. There is no CASE equivalent in Access SQL.

To make your crosstab query useful as a basis for a data update form, you might have to use the crosstab form as a subform and have a KeyPress event for each field in the subform. You can then have the subform call a popup or send the focus to a control on the main form to allow the user to enter data. Then, create an sql statement and run it from the main form. If you prefer, you can use a double click event or use both to trigger the data entry action.

 

[LevelThought]

Basically,

I am interested in updating a local access table that contains non-calculated and calculated fields! I do realize that storing calculated values in an Access table is not "normal." However, I am "forced" to complete the database, store and update the calculated fields on a daily basis!

My non-calculated fields are:

AcctNo
TotChg
ExpectedPymt
TotInsPymt
TotPymt
SumAdjAmts

My calculated fields are:

OtherPymts = TotPymt - TotInsPymt
Variance = ExpPymt - (TotChg - SumAdjAmts)
Bal = ExpPymt - TotPymt

If I want to update all records within the local Access table on a daily basis that contain non-calculated and calculated fields, would the following steps be:

Step 1. Create a query to update the non-calculated fields on the local Access table using an inner join
Step 2. Create a cross-tab query to obtain separate columns for the adjustment amounts (per account number)
Step 3. Base a form on the two queries, the query in step 1 and the query in step 2. Then, include text boxes on the form for the calculated fields and populate the local Access table using the "After Update" event on the form.

Any additional insight is appreciated.