How to reorganize pieces after import from large database

[smilee8_28]

Sorry this is long...I am trying to recreate Month End reporting which
currently is done in Excel only it requires manual data entry, the copying
and pasting of formulas, moving columns of data, etc., every month. Quite a
mess if you ask me. This is for a large medical department.
I hope I can explain this...

I can import the data I need from our University's main database creating
text files. This comes in the form of about 7 different files. I can't figure
out how to reorganize the relationships of the data once I get it into Access.

The most basic data I am working with are essentially:

Charges
Payments
Refunds
Discounts
etc,.

Each file of data I import is the result of a different scenario, for example:

Location - Doctor - [above basic data elements]

another file:

Doctor - Area - [above basic data elements]

and:

Nurse - Location - Area - [above basic elements]

etc.

The data I import is only understandable and identifiable in the context it
is already in. The doctor's name is given. Then the area the doctor was in.
Then the charges, payment, refunds, discounts for that doctor in that area.
The whole file is a list giving multiple areas for several doctors.

I can probably strain my brain to figure out how the relationships should
work once I get the data to Access but how do I keep from duplicating a lot
of the data I need. Every file will list the providers causing duplicates but
the basic data elements won't makes sense otherwise. If I just imported the
charges, payments, refunds and discount data from all the files I would lose
all the context.

It almost seems like there should be a way I can just set up each file as
it's own table, so about 7 tables, then create the queries and reports I need
from that but it sounds like this would be a big no-no.

Help? I have obviously never done anything like this.

Thanks so very very much!
Kristine

 

[Edward Reid]

Charges

Payments
Refunds
Discounts

Kristine,

With names like those, I have to ask if you don't really need an
accounting package. Trying to do accounting yourself is a huge amount
of work, and you'll never get more than basic capabilities. By
contrast, lots of accounting software is available.

If this is something much more limited, then I think you need to
explain more about your data and what you want to do with it. Other
than doctors, nurses, and locations, what are your basic objects? How
are charges, payments, etc related to doctors, nurses, and locations?
What are the data fields in the files you are able to retrieve from the
main University database?

Edward

 

[smilee8_28]

Well, as far as using accounting software, I haven't gone through all of the
detail on all the reports currently in use but so far the formulas have all
been simple addition and subtraction. The reports currently work fine in
Excel as far as calculations go. My objective is to streamline it all so it
will just be a matter of importing the data each month then simply viewing
the reports. If you think an accounting software would be a better option
what would you say the advantage would be?

Otherwise, the data I pull for each file is as follows:

DIV LOC DOC CAT file data
LOC DOC (CAT)MONTH $ UNITS WORK
RVU
Salt Lake Clinc Smith, Bob MD $1,000 10
1.62

DIV DOC TYP CAT file data
DOC BILL AREA (CAT) MONTH$ UNITS WORK RVU
Hansen, Lou MD Derm Srvics $2,500 20 1.87

DIV DOC TYP PRO file data
DOC BILL AREA CODE DESCRIPT (CAT)MONTH$ UNITS WORK RVU

Hansen, Lou Derm Srvics 99212 Office Visit $800 2
1.43

MTD SUMMARY file data
DOC PAYMENT ADJUST CREDITS DEBITS REFUND CHARGES
Smith, Bob $800 $550 $1,500 $1600 $80
$900

There's more but this should give you an idea. After writing the above info
I am even more skeptical about all this but my main question at this point is
how would I import this data without duplicating things like the Location,
Bill Area and Doctor? If I set up seperate tables, one listing doctors, one
listing locations, another billing areas and another with the actual values
(CAT), how do I import it?

What I have done already is to just keep the file data grouped as it is so
that each file is it's own table and it seems like I may be able to get the
queries and reports to work but I don't want to get too involved with that to
find out it won't. The way I am doing it definitely means there is duplicate
data (ie, doctor names, locations, billing area, etc.), which I know is not
good.

Kristine

 

[Edward Reid]

If you think an accounting software would be a better option
what would you say the advantage would be?

What concerns me here is that I see (from the consumer side) the
complexities of medical billing, with insurance submissions, partial
payments, etc. If your situation doesn't involve all those complexities --
and if you are sure you can handle whatever complexities you have -- then
you can go with an Access solution.

There's more but this should give you an idea. After writing the above info
I am even more skeptical about all this but my main question at this point is
how would I import this data without duplicating things like the Location,
Bill Area and Doctor? If I set up seperate tables, one listing doctors, one
listing locations, another billing areas and another with the actual values
(CAT), how do I import it?

I did something similar just recently. Here's the general method.

1) As I think you already know, create separate tables for each item which
would otherwise have duplicates. In your case, this is at least Doctors,
Locations, and BillAreas. Each table should have an auto-number primary key
(PK) with a field size of long integer. I'll call these the item tables,
and the names stored in them "items".

2) Create whatever tables you need which use these items, with the items
represented by links (also called foreign keys, or FK). The FKs should be
numbers with field size long integer to match the PKs in the other tables.
I'll call these the transaction tables.

3) Set up database relationships to show the link between the FKs in the
transaction tables and the PKs in the item tables. Because the PKs are
"index no duplicates", Access knows that the relationships are one-to-many.
(You can set up the relationships later in queries, but by setting them up
at this time, they are automatically preset when you define the query.)

4) Create an input data table to store a copy of the input data, with the
same fields as your input data file. You will initially import your data
into this table, since it's much easier to deal with data already in the
database. (This will be one table for each different format of input file.
If you only have one input file format, then you only have one table.) The
important items (Location etc) should be indexed, but WITH duplicates,
because you haven't eliminated the duplicates at this point.

5) Set up an Update Query to edit and correct the input data. This is not
strictly necessary, but it's better to plan for it. In my case, some fields
imported as nulls when they needed to be zeroes.

6) Set up an Append Query for each item table, with the item table as the
destination. Each append query involves the input data table and the item
table Change the relationship to be a left join (option two in the join
properties) just for this query. In the first column, field = the input
data table field, table = the input data table, append to = the item table
field. In the second column, field = the item table field, table = the item
table, append to is blank, and criteria = "is null".

7) Set up an Append Query for the transaction table. This query will
include the transaction table and all the item tables. In the "field" line,
list all the fields in the input query you want to put in the transaction
table, and the corresponding transaction table names in the "append to"
line. But for the items, give the field name of the PK in the item table on
the "field" line, the item table on the "table" line, and the FK field name
in the transaction. Note that you do not directly reference the item names
from the input table; Access figures out from the relationships what PK
from the item table needs to go into the FK in the transaction table.

8) Set up a Select (the default) Query using the transaction table and the
item tables, selecting all the non-key fields in the transaction table and
the item fields from the item tables. This isn't strictly necessary but
makes building forms and reports a lot easier -- base the form/report on
this query rather than on all the tables.

9) To add new data:
import into the input data table
run the append query for the item tables
run the append query for the transaction tables
delete all the records from the input data table.
You can automate this, and will probably want to do so if this is a daily
process.

Edward

 

[smilee8_28]

Thanks so much! I'm going to give it a try!

 

[smilee8_28]

I'm stuck on step 6. Can you explain what it does? Is this used to populate
the Item tables? For the most part, the Item tables won't change so I'm a
little confused. Why the is null criteria? And....how does this work when I
have mulitple input tables? This is looking good I'm just stuck....thanks!

Kristine

 

[Edward Reid]

I'm stuck on step 6. Can you explain what it does? Is this used to populate
the Item tables?

Yes. Ah, but I'm glad you asked, because I missed one important thing. In
the step 6 queries, go to the query's property sheet, and set the Unique
Values property to Yes. The query doesn't make much sense without this --
and in fact would cause a run-time error when there's more than one
instance of a new value in one input file.

For the most part, the Item tables won't change so I'm a
little confused.

If they really don't change, then you can populate them some other way and
skip this step. In my case, I do get new values and I do want to
automatically add them. I should have made this clear.

However, as written, if your input data has items which are not in the item
table, then the append to the transaction table will silently drop those
records. Most likely this is a Bad Thing. ;-) So you need to either run the
query to append any new values to the item tables, or else create a query
which will tell/warn you when new values are present so that you can handle
them in whatever way is appropriate in your context. No matter if the
people sending the file tell you there will never be new values: trust but
verify! If adding the new values is the right thing to do, then just let
the append query run -- even if it usually doesn't update anything, it'll
be quick and won't do any harm.

Why the is null criteria?

This is to select input records for which there is no corresponding record
in the item table. The left join gives you a record for each value on the
input whether it's in the item table or not; the "is null" selects those
records from the left join which had no record in the item table. These of
course are the values which need to be added to the item table. (Or
reported as errors, if adding them automatically is not appropriate.)

And....how does this work when I
have mulitple input tables?

Do you mean multiple input files in the same format, that is, importing
multiple files at the same time? Or multiple input formats, requiring
multiple formats of input table, which is what I mentioned?

If you need to import multiple files at the same time (all in the same
format), then you just repeat the first part of step 9 until you have
appended all the input files to the input table, then proceed with the
remaining steps. (In my case, where I've automated it, I have a VB
procedure which finds all the files in a folder, imports them to the input
table, and renames them into another directory. Actually I've gone even one
step farther -- when I import a file, I save its name in a table. The file
names contain the date, so then I check new file names against this table
and reject them if I previously imported them.)

If you have multiple different formats, then you'll need to repeat steps 4
through 8 for each format -- that is, the input table and update queries
will be different for the different input formats. Then you'll have to run
step 9 for each file format -- though you could start with all the imports
and then do the remaining steps for each format. An alternative would be to
have a second input table, but as soon as you import into it, move the
records to the first table, reassigning fields as needed. Note that if the
only difference is the order of the fields, and the field names are in the
first record of the input file, and you can arrange for your input table to
have the same names, you may be able to get away with only one table and
even only one import procedure by using the "first record contains field
names" option of the import process.

Edward

 

[smilee8_28]

Okay....that helps clarify things for me a lot. I think the 'Unique Values
Property' bit is especially helpful. I do have multiple input
formats....several actually...so your advice on that is much
appreciated...well all of it is. I'll get back to work on this now but I have
a feeling I'll be back with more questions.
Thanks again for your help!
Kristine