Grouping

[Stefan]

I have an external medical billing database I am linking to.

I am working with 3 tables Deposit, Payment, Transaction

When creating a transaction (medical procedure for billing) a unique charge
entry# is created.

You post the payments through the deposit table (unique deposit#) which
stores them in the payment table. There are typically payments for many
patients on the same check.

In the payment table a unique payment entry# is created. In the payment
table there is also a charge entry# field for reference to the corresponding
charge entry#.


Each charge item(medical procedure) typically has an insurance payment a
discount or disallowed amount.


In the transaction table an entry of each these fields is also created. So
there is a unique charge entry# and unique payment entry#.

So without being too confusing for every charge entry# in the transaction
table there can be many related payment entry# (payments, discounts etc....)

In my query I only want the charge entry# to appear 1 time but I am getting
the same charge entry# for each related payment entry#. (So I will get the
same 2 charges if there is a payment and a discount.)

I tried to group by but this doesn't work since the fields are all in the
same transaction table.


I appreciate any Help!

Stefan

 

[Chris]

There is nothing wrong with including multiple fields from the same table in
a group by clause. Post back with the SQL from your query and someone can
take a look at it to see what might be causing the problem.

 

[Mark]

Stefan,
I would need more information, but I think in your query you need to add
criteria that excludes past payments.

 

[Mark]

Also forgot to mention you probably would be better off with a unique
identifier for each payment, not one that is created every time a payment or
charge is made.

 

[Stefan]

Hope this helps.

I cannot change the unique ID's since I am only linking to another program
"Medisoft"

SELECT MWPAX.[Deposit ID], MWTRN.[Entry Number], MWPAX.[Payment Reference],
MWPAX.[Charge Reference], MWTRN.[Chart Number], MWPAX.[Payment Amount],
MWTRN.[Transaction Type], MWTRN.[Date From], MWTRN.[Procedure Code],
MWTRN.Amount, MWTRN.[Insurance 1 Amount Paid], MWTRN.[Insurance 2 Amount
Paid], MWTRN.[Adjustment Amount], [Amount]+[Insurance 1 Amount
Paid]+[Insurance 2 Amount Paid]+[Adjustment Amount] AS Due, MWTRN.[Claim
Number], MWPAT.[Last Name], MWPAT.[First Name]
FROM MWPAX INNER JOIN (MWTRN INNER JOIN MWPAT ON MWTRN.[Chart Number] =
MWPAT.[Chart Number]) ON MWPAX.[Charge Reference] = MWTRN.[Entry Number]
WHERE (((MWPAX.[Deposit ID])=18636) AND ((MWTRN.[Transaction Type]) Between
"A" And "H"));

 

[Stefan]

The first one didn't have the group by selected

SELECT MWPAX.[Deposit ID], MWTRN.[Entry Number], MWPAX.[Payment Reference],
MWPAX.[Charge Reference], MWTRN.[Chart Number], MWPAX.[Payment Amount],
MWTRN.[Date From], MWTRN.[Procedure Code], MWTRN.Amount, MWTRN.[Insurance 1
Amount Paid], MWTRN.[Insurance 2 Amount Paid], MWTRN.[Adjustment Amount],
[Amount]+[Insurance 1 Amount Paid]+[Insurance 2 Amount Paid]+[Adjustment
Amount] AS Due, MWTRN.[Claim Number], MWPAT.[Last Name], MWPAT.[First Name]
FROM MWPAX INNER JOIN (MWTRN INNER JOIN MWPAT ON MWTRN.[Chart Number] =
MWPAT.[Chart Number]) ON MWPAX.[Charge Reference] = MWTRN.[Entry Number]
WHERE (((MWTRN.[Transaction Type]) Between "A" And "H"))
GROUP BY MWPAX.[Deposit ID], MWTRN.[Entry Number], MWPAX.[Payment
Reference], MWPAX.[Charge Reference], MWTRN.[Chart Number], MWPAX.[Payment
Amount], MWTRN.[Date From], MWTRN.[Procedure Code], MWTRN.Amount,
MWTRN.[Insurance 1 Amount Paid], MWTRN.[Insurance 2 Amount Paid],
MWTRN.[Adjustment Amount], [Amount]+[Insurance 1 Amount Paid]+[Insurance 2
Amount Paid]+[Adjustment Amount], MWTRN.[Claim Number], MWPAT.[Last Name],
MWPAT.[First Name]
HAVING (((MWPAX.[Deposit ID])=18636));