Combine data from one table

[Ray67]

Hello-

Need a little help with queries. I have two tables, one holds all of patient demo and the second has charges, payments and adjustments.



This is how the data looks on the second table

BillingProviderLastName	PtAcct	SvcDateStamp	FinTransDateStamp	Charges	Payments	Adjustments	PtProcCodeCPT	TransType	TransDesc
John	3585	9/5/2013	9/9/2013	$150.00			20610	Charges	Charges
John	3585	9/5/2013	9/9/2013	$100.00			99214	Charges	Charges
John	3585	9/5/2013	9/9/2013	$25.00			J1030	Charges	Charges
John	3585	9/5/2013	10/21/2013			$50.00	20610	Adjustments	Adjustment - Contractual
John	3585	9/5/2013	10/21/2013		$100.00		20610	Payments	Ins Pay Lockbox
John	3585	9/5/2013	10/21/2013			$50.00	99214	Adjustments	Adjustment - Contractual
John	3585	9/5/2013	10/21/2013		$50.00		99214	Payments	Ins Pay Lockbox
John	3585	9/5/2013	10/21/2013		$20.00		J1030	Payments	Ins Pay Lockbox
John	3585	9/5/2013	6/16/2014			$5.00	20610	Adjustments	ADJ - OFFDISC
				$275.00	$170.00	$105.00

I Would like the data to look like this

BillingProviderLastName	PtAcct	SvcDateStamp	PtProcCodeCPT	Charges	Payments	Adjustments
John	3585	9/5/2013	20610	$150.00	$100.00	$50.00
John	3585	9/5/2013	99214	$100.00	$50.00	$50.00
John	3585	9/5/2013	J1030	$25.00	$20.00	$5.00
				$275.00	$170.00	$105.00

[June7]

Options:

1. build a report and use grouping & sorting features with aggregate calcs in footers

2. build two queries
query 1 would be an aggregate (GROUP BY) totals query for the accounting data
query 2 would join query 1 to the patient demo table

[Ray67]

Thank you June7
It worked out great

I have another questions. In the query i add a field(Balance). It's subtracting charges,paments and adjustments.
The results are coming out correct. I would like to remove zero balance but keep any credits.

PtProcCodeCPT	SumOfCharges	SumOfPayments	SumOfAdjustments	Balance
99214	$150.00	$125.00	$50.00	($25.00)
J1040	$15.00	$5.00	$5.00	$5.00

[June7]

Do you mean exclude PtProcCodeCPT records where the balance is 0? Apply filter criteria to the Balance field: <>0

[Ray67]

Yes June that's what i mean.
I try putting <>0 but it didn't work.

Thank you

[June7]

Filter criteria on an aggregate calc works for me.

Post the SQL statement or provide db for analysis. Follow instructions at bottom of my post.

[Ray67]

i was going to provide you with a db but after setting it up it works on that db.
Not sure why it’s not working on the original one.

Here is the SQL
SELECT Query1.BillingProviderLastName, Query1.PtAcct, Query1.SvcDateStamp, Query1.SumOfCharges, Query1.SumOfPayments, Query1.SumOfAdjustments, [SumOfCharges]-[SumOfPayments]-[SumOfAdjustments] AS Balance
FROM Query1
GROUP BY Query1.BillingProviderLastName, Query1.PtAcct, Query1.SvcDateStamp, Query1.SumOfCharges, Query1.SumOfPayments, Query1.SumOfAdjustments, [SumOfCharges]-[SumOfPayments]-[SumOfAdjustments]
HAVING ((([SumOfCharges]-[SumOfPayments]-[SumOfAdjustments])<>0));

[June7]

Why are you grouping in this query? The grouping was already done in Query1.

SELECT Query1.BillingProviderLastName, Query1.PtAcct, Query1.SvcDateStamp, Query1.SumOfCharges, Query1.SumOfPayments, Query1.SumOfAdjustments, [SumOfCharges]-[SumOfPayments]-[SumOfAdjustments] AS Balance
FROM Query1
WHERE Balance<>0;

[Ray67]

Not sure why i put grouping on that query.
Try using your sql and i'm getting this error
Enter Parameter value Balance

[June7]

Open the query in Design view. Under the Balance calculated field on the criteria row type <>0. Then switch to SQL view to see the syntax. What does it look like?

[Ray67]

SELECT Query1.BillingProviderLastName, Query1.PtAcct, Query1.SvcDateStamp, Query1.PtProcCodeCPT, Query1.SumOfCharges, Query1.SumOfPayments, Query1.SumOfAdjustments, [SumOfCharges]-[SumOfPayments]-[SumOfAdjustments] AS Balance
FROM Query1
WHERE ((([SumOfCharges]-[SumOfPayments]-[SumOfAdjustments])<>0));

[Ray67]

i think i found the problem
Under balance calculation in datasheet view, some of the numbers are coming out like this 2.8421709430404E-14

[June7]

So that is a negative value to 14 decimals? Access can do some weird stuff with calculations. Perhaps you should use Round() function on the calc.

[Ray67]

Not sure what you mean
Did you want me to use Round() on this field Balance: [SumOfCharges]-[SumOfPayments]-[SumOfAdjustments]

[June7]

Yes, can't hurt to see what happens.