I am in the process of setting up a

[SpeedThink]

I am in the process of setting up a Microsoft Access database to track underpayments on customer accounts.

I have a voluminous set of information from an Oracle database, over 70,000 rows, that I imported into Access from Excel. Each and every month, there will be an additional 2000 to 5000 records to add to the database.

Basically, I am interested in using as few tables as possible.

I created a table in Access titled "Variance" to hold this data. I created a form, named "Data Entry." Source of data for the form is the table named "Variance"

Also, on the form I included combo boxes that the user will select the "Underpay Action", "Underpay Reason", and
"Status" of each account from a pull-down menu.

The fields for the "Variance" Table are:
Account No.
Name
Charges
Bill Date
Payments as of Sept 1, 2003
Last Payor Pymt
Date of Last Payment

Do I need to create another table with the following blank fields to store the user(s) actions on the form:

Account No. (Is it possible to link this to the "Variance table so that one could view the status of an account)
Underpay Action (drop down box on form)
Underpay Reason(drop down box on form)
Status (drop down combo box on form)
Recovered Amount (user types in this)

On the other hand, do I just modify the "Variance" table by adding additional columns titled "Underpay Action",
"Underpay Reason", "Status", and "Recovered
Amount?

SpeedThink

 

[RickSpr]

Quote:
"Basically, I am interested in using as few tables as possible."

I encourage you to give up this as a general goal when designing tables. It may be possible to store all your data in a single table, but chances are that it wouldn't be normalized, and you'd regret it in time. What you should set as your goal is to have the minimum number of tables you need. More about that later.

Unfortunately, I can't tell from the information you've given whether you need another table in this case. The missing fact is whether the additional columns will only occur once for any given account. If so, you should add these columns to the Variances table; otherwise, they need to be in their own table.

Basically, you don't need a new table if an existing table's key uniquely identifies the additional columns. To put that in a way that's easier to understand, and assuming that the Variances table has AccountNumber as its key, ask yourself this question: If all I know is an account number, should I be able to figure out the Underpay Action, etc.? Or could there be multiple Underpay Actions for an account number, so I need more information to decide which one to use?

If the account number is all you should need, then the table in which account number is the key (Variances) can hold the columns. The key "identifies" the column data--it distinguishes one set of data for these columns from all the other sets. In general, all the data that is identified by a given key should be in a table with that key.

If you need more than the account number, then you need another table. Usually, this table's key will be the combination of account number and the "more" data, whatever it may be.

Arranging data according to this rule is the better part of the process called "normalization", and you always need to normalize your table design when designing a relational database.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[SpeedThink]

Thanks for the insight, RickSpr

In essence, all of the information that will be in the Access Tables will originate from tables within an application with an Oracle database.

Currently, I perform the queries within the application and save the results as a text file. I then import this text file into Access on a daily basis. I need to update payments daily for the accounts that have been identified. Additionally, new accounts come over daily with a payment amount. Therefore, these accounts need to be added to the "Variance" table and the payments need to be updated going forward as well.

Now, the tables within the application do not contain the fields; "Underpay Action", "Underpay Reason", "Status" or "Recovered Amount."

Therefore, I will add several columns to the Variance table, use the wizard to create the form with the records from the "Variance" table as the source of data for the form. Then, setup the form whereby the users' selections on the form (Underpay Action, Underpay Reason, etc.) will be stored in the "Variance" table.

Additional question:

Could I use a pass-thru SQL query to directly query multiple tables within the application from Microsoft Access? If so, a link to the external table is necessary. To capture additional accounts, would I create a "make table" query to store the results and then append the results to the "Variance" table? Finally, a query to update payments for the ever-changing "Variance" table will complete this challenge.

However, the biggest challenge is the SQl script (used to identify variances between the expected reimbursement and the actual payment) that I currently run to obtain the data in the host application;

select ep.contract_id Cont, pe.account_no Acct, sum(epd.payment_amount)InsPymt, pe.date_updated BillDate,
pe.total_charges Charges
from patient_encounter pe, encounter_payor ep,
encounter_payment_detail epd
where pe.account_no = ep.account_no
and pe.account_no = epd.account_no
and trunc(epd.date_updated) = trunc (sysdate)

This SQL script only picks up accounts that had a Insurance payment today! If a Insurance Payment came in prior to today, the account will not show in the results!!Therefore, when Management starts to research the Variances, another payment might come in the following day and a variance does not exist anymore! I would like for it to summarize all payments posted 10 days ago. 20 days ago. 30 days ago, etc. What changes to the SQL script is needed??

Note, the Encounter Payment Detail does not contain a payment date.

Trying to save my job!!

Thanks in advance.

 

[RickSpr]

I apologize if my first response was too elementary. You seem to have some experience with relational databases. Perhaps I misunderstood the focus of your question.

A pass-through query via an ODBC link seems like the right approach to me--but I have no experience with Oracle or ODBC links.

You shouldn't have to go through the trouble of a make-table query. You should be able to use an Append query with a left outer join between the Oracle table and the Variance table, restricting on the Variance account code being Null. That will save a few steps and eliminate the temporary table. However, the make-table approach will work, too, if you have some reason to prefer it. (I'm unclear on whether the "new" accounts are to be added to the Variance table, or whether the Variance table is to be built from scratch each time.)

For summing payments over a period of time, I don't know Oracle so I can only express the syntax in a hybrid of Oracle and Access SQL. I guess you would use:
and trunc(epd.date_updated) BETWEEN trunc (sysdate) - 10 AND trunc (sysdate)
In Access, you can do date arithmetic with integers. I'm guessing that "trunc" removes the time portion, which suggests that Oracle might store dates similarly and therefore you could do date arithmetic the same way. If not, there should be some kind of DateAdd function for doing date arithmetic. The main thing is to use BETWEEEN...AND to select a range of dates.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein