Kathie G via AccessMonster.c om wrote:
Hello,
I have a delima that I am not sure how to resolve. Records get entered
manually or through an import. The records contain, RecordID, Client ID,
Specimen Date, Visit Date, Client DOB, and other info on the client. The
problem is, we bill off this information, and it is possible that the same
client can be entered more than once. So I need to have a unique record
based on Client, Visit (or) Specimen Date, and date entered. Presently, all
this stuff is in one table. I though I may be able to split the info out,
but I would still run into the same issue without a prompt which could become
kind of a pain as they could (theoretically) have the same client entered
(depending upon test results, etc.) multiple billing cycles in a row.
Any ideas?
I'm no database architect but I'd say you have a classic problem with a
lack of structure here.
As you say, you need unique information about your clients. This is a
sure sign you should have a separate table for Clients. A clients table
would contain information about clients and *only* about clients. It
would contain a primary key (ClientID) name, DOB, gender, etc. It would
not contain (in all liklihood) any information about an encounter.
Then you have encounters. An encounter involves a client, on a specific
date, at a certain time, and with some services/charges. So you need an
EncounterID, a reference to the ClientID, DOS, time, SpecDate, and...
charges?
Then you need a charges table, because there can be many charges for one
encounter. So you have a unique ChargeID, a reference to the
EncounterID, and (maybe) charge description, amount, charge type (pro,
lab, xray). Or maybe all the latter stuff goes into a more or less
static services table.
Then maybe you need an insurance table, with references to the client,
date entered, insurance name, coverage dates, etc...
Hopefully can see where this is going. All the business processes you
have ("encounter" , "charge", "bill") are separate entities. Your clients
are separate entities. These entities, kept in separate tables, are what
a relational database is all about.
--
Smartin
