Tables Problem

[Guest]

Running Access 200

I have a problem with data on my tables. I have one table w/ patient information, one w/ payment info for each patient, and one with denial codes for each patient's account. They are all linked by acct_num (account number).

My problem is this. I want to pull distinct payment information for each patient but I have to generate reports based on the denial codes. Some accounts have multiple denial codes and when I create a report for one week I may have the same account appear multiple times on the report, along with it's cooresponding payment info. This really inflates my grand totals at the end and causes a problem

How can I get distinct payment information? I was thinking of putting a date in my patient info table that was the date the acct was originally denied. I pull this info in from daily import. I have this date in my denial codes table also but it is associated with, if the patient has them, multiple denial codes

Any suggestions

TIA

John

 

[Tim Ferguson]

I have one table w/ patient information, one w/ payment info for each
patient, and one with denial codes for each patient's account.

I don't know what a Denial code is -- is it something that applies to a
patient, or to a treatment, or to a payment? Do you have lots of denials
per patient, or many payments per denial?

It might help if you would post the relevant parts of your schema: the keys
and a couple of explicatory fields would be enough, and how the
relationships are enforced.

B Wishes


Tim F

 

[Guest]

Here is an example of what information I get

acct_num pat_name total_charges acct_bal denial_code denial_dat
1234567890 Smith, Joe $5,123.00 $123.00 T5001 11/24/0
1234567890 Smith, Joe $5,123.00 $123.00 T5005 11/24/0
1234567890 Smith, Joe $5,123.00 $123.00 T5700 11/24/0

This is some of the info that I would get in my import file everyday. Not all accts have multiple denial codes, most do though
I have a Patient Info table with all of the patient demographics (name, dob, acct #, date of service ...) The primary key is the acct_num
I have a Payment Info table that has acct_num as the primary key. It has all of the payment info for each patient acct. If the payment info changes I have an update query that changes the payment info
I have a Denial Codes table that has no primary key. The acct_num is indexed but there are a lot of duplicate acct #'s because of the multiple denial codes for a specific acct

I pull my reports using query that links all three tables together via the acct # (acct_num
When I do my reporting for 1 week (example) I will have 10 different denial codes that show subtotals for total charges and acct balance so that I know how much money I am dealing with with each denial code. The problem is the grand total actually adds all of the subtotals together. The grand total is actually bigger than it should be.

 

[Tim Ferguson]

Here is an example of what information I get:

Okay, but it doesn't answer any of the questions I asked.

....

Not all accts have multiple denial codes, most do though.

Well, that sound like a one-to-many relationship.

I have a
Patient Info table with all of the patient demographics (name, dob,
acct #, date of service ...)

Date of service surely isn't a demographic. How many services do patients
have? What happens if the same patient comes back for another service -
does he or she get a new Account number or reuse the old one? If it means a
new ServiceDate value, what happens to the old one?

The primary key is the acct_num. I have
a Payment Info table that has acct_num as the primary key.

That doesn't make sense: it would imply a one-to-one relationship and I
can't see any reason for having that.

It has all
of the payment info for each patient acct. If the payment info
changes I have an update query that changes the payment info.

Payment or payments?

I have a
Denial Codes table that has no primary key.

In that case it's not a table.

The acct_num is indexed
but there are a lot of duplicate acct #'s because of the multiple
denial codes for a specific acct.

There should be one record for each Denial code for each patient (or
account? it seems to have changed).

I pull my reports using query that links all three tables together via
the acct # (acct_num)

The problem is that you simply have to get your head around what each of
these entities is, how many of them there are, and how they relate to each
other. I still don't know whether a Denial code belongs to a payment or a
patient or an account or a service or what. It is my guess that there is
some kind of many-to-many relationship that is being mangled into a many-
to-one; and that is probably causing the multiple counting, but I can't say
where, with the information given.

There are at least two different scenarios that you may be trying to model,
or perhaps something completely different:-

1234 John Smith $4560.90
denial codes 4509
4610
7777

1235 Samantha Smith $3009.60
denial codes 7777


and so on, or

1234 John Smith 4509 $1290.75
4610 $2090.40
7777 $ 450.00

total (3) $3831.15

1235 Samantha Smith 7777 $89070.00

total (1) $89070.00



Hope that helps


Tim F

 

[Guest]

Can I send you a copy of the tables that I have? Email me at dailyjo1 at memorialhealth dot com if you would like to see tha tables that I have.

Thanks

John

 

[Tim Ferguson]

Can I send you a copy of the tables that I have?

No: it should be possible to describe what you are trying to do here; and
by posting in public you get the benefit of everyone else's oversight too;
and I really don't have time to commit to an individual project as I have a
job of my own (and a dead car too, as of this afternoon, but you don't
really want to know about that ... boo hoo).

In any case, you have described what you have already done; but what we
need to see is what you are trying to model. Design comes from
understanding the real-life things that you are dealing with and how they
interact. You have mentioned patients and accounts and services and denials
and payments, but not what any of these mean nor how they relate to each
other, and it is the last of the these that you really have to get right in
your model.

B Wishes


Tim F

 

[Guest]

Ok, I think I can do this

Everything revolves around the patient's account. Each patient will have a different account # for each and every visit. The same number is never used.
In my patient info table I have the acct#, patient name, dob, medical rcrd#, admit date, discharge date, financial class, patient type. There are no duplicate account numbers. There may be duplicate patient names, dob, medical record #, financial class, or patient type. Each patient will have this info on their acct
The payment info table holds the total charges, total payments, total adjustments, and account balance. There are no duplicate account numbers as I have an update query to update any changes in payments for that patients particular account
My big issue is my Denial Codes table. 1 patient's account can have multiple denial codes. Here is the layout of the table: account number, denial code, denial date, denial description, responsible dept. When I receive a denial from an insurance co. for an individual account it can have multiple denial codes or it can have just one. Sometimes one account can have the same denial code but have a different denial date associated with it
I think that since I have a lot of repeated data with the denial description and responsible dept that I should put this into another table so that it is not repeated so much in my denial code table. In the other table I would only have it once and it can be linked by the denial code to my denial code table. Am I correct in this

I do think that I have a problem with the way my tables are set up. Any advice would be great

Thanks Tim

John

 

[Tim Ferguson]

"=?Utf-8?B?Sm9obiBEYWlseQ==?=" <[email protected]>
wrote in
There is a lot to respond to here, so I'll do it in bits...

Everything revolves around the patient's account. Each patient will
have a different account # for each and every visit. The same number
is never used.

OK, already -- we have two separate entities here, Patients and Accounts.
If you really, genuinely, hand-on-heart, nevereverever want to link
subsequent patient accounts together, then you can get away with not having
a Patients table. If once, though, just once, your manager says "how come
we didn't know he never paid his last account", then you do. I would play
it safe every time and have one.

In my patient info table I have the acct#, patient
name, dob, medical rcrd#, admit date, discharge date, financial class,
patient type.

See above: all that patient stuff should be out in the patient record.
Admit and Discharge dates, if you _ever_ send someone home and bring them
back for the same account, should be in an Admissions table. Presumably
somewhere there is stuff about next of kin, diagnosis and so on.

The
payment info table holds the total charges, total payments, total
adjustments, and account balance.

Bad: the Total Charge is surely part of the Account; Total Payments should
come from adding up the Payments records; you've never mentioned
Adjustments before but I assume the same thing applies. The Account Balance
should be calculated from Account.TotalCharge, the Payments records, and
the Adjustments records.

My big issue is my Denial Codes
table.

Well, yes..

Here is
the layout of the table: account number, denial code, denial date,
denial description, responsible dept.

Departments? We seem to have another entity!

When I receive a denial from
an insurance co. for an individual account it can have multiple denial
codes or it can have just one.

Okay, so we have Denials and DenialCodes...

Sometimes one account can have the
same denial code but have a different denial date associated with it.

I think you mean a Denial can have the same DenialCodes but with different
dates? And are you interested in the Denials themselves or only the codes?
Can you reconstruct the Denials just from Codes having the same
AccountNumber and DeniedDate?

I think that since I have a lot of repeated data with the denial
description and responsible dept that I should put this into another
table so that it is not repeated so much in my denial code table.

But you've just said it's not repeated: if I've understood you correctly,
you can have

Account DeniedDate DenCode
1234 12 Jan 4501
1234 15 Jan 4501
1234 15 Jan 4902

but there is no repeated data in this table, because you can't tell the
contents of any field by knowing its neighbours. I am very suspicious of
the Denial Description field you mentioned earlier: if that is a narrative
description of the code number, then you are right that it should not be
there, and you should have a separate table of DenialDescriptions.

Oh, and I can't help you with the Responsible Dept because you just slung
this under the wire.

I think you still have a lot of work to do before you are ready to go near
a keyboard and start programming. The other thing that strikes me is that
you might want to get some professional input, if real people and their
health provisions are going to be affected by the system. It is one thing
to put together a stamp-collecting database by trial and error; bankrupting
a hospital or a family already coping with illness is another thing.

Best of luck


Tim F