Storing data in two fields without allowing Duplicate entries

[bdhtexas]

I need to know if I can store data in more than one field, without allowing Duplicate entries.

Example: We have to send out letters with a claim number on it, sometimes we need multiple claim numbers on a letter, up to about 10 claim #'s is the most I've seen thus far.

We mail merge into Word to create the letters.

However, these claim numbers cannot be duplicated within the database and in order for this to work with the mail merge, I need several fields to store the claim numbers.

Fields are:
ClaimNumber
ClaimNumber2
ClaimNumber3
etc....

To be more specific. I probably have a bad data structure as I was kind of rushed and a little inexperience with putting the database together. It's for work and sometimes management just doesn't want to wait, they are so negative.

In brief, most everything is in 1 table and I know from reading this forum that I shouldn't do that, but that's another story.

Further, a claim# is tied together by the same claimant, the relationship code is what separates the claimant with the family, the insured id# is used for all claimants within the family.

You can also tie the claim# to a provider id#, some providers such as clinics and large hospitals have multiple providers that use the same provider id#, so a suffix code is used to separate the providers within that same #.

Please feel free to ask additional questions and I will give you the additional information you need to help me. Thanks again.

P.S. - Last year I created the database and this year I am improving it, so expect more posts from me in the future. I really appreciate all the help I get from everyone here!!!!

 

[bdhtexas]

I think you are misunderstanding, a claim number is unique and is based on the date of when it was processed. We send letters to providers of service and sometimes we have several claims for the same provider processed through time. So, claimnumber, claimnumber2 and claimnumber3 are unique and MUST be separated.

I need to know how to create multiple fields to store the claim numbers, so that I can allow more than 1 claim number to be presented on a letter via mail merge, but the claim number can never repeated or duplicated. Thanks.

Example: Dr. Smith, we have 3 claims for him, 123456-01, 234561-02 and 345789-01. The sequence is a must, a claim can only hold 12 lines of service, so when a claim has more than 12 lines you would combine the sequences, example 456789-01-04, combines 456789-01-02-03 & 04. So, in the letter to the provider, we need all these claim numbers to show up on the letter via mail merge.

 

[bdhtexas]

Here is more information

RIMS claims processing system and the processor 1 system stores the Patient name, address, policy number and claim numbers, provider name, address and id#'s (which is federal tax id#'s).

When the claim examiner identifies an overpayment on one of these claims, they send an email to my department with a list of claim numbers that are overpaid.

None of these numbers are derived from Access.

At this point, we enter the information into Access.

I could limit only 1 claim number per letter be entered into Access, but I was hoping to make Access more productive and use more than 1 claim number per letter.

Note: The claim and provider numbers are derived from the claims processing systems, changing them to make them better for Access is not an option.

On a funnier side note, it took Computer Programmers 20 years to figure out how to create a claims payment system that worked well.

 

[WayneRyan]

bhd,

The thing that sticks out first in this is (as Pat said) ClaimNumber, ClaimNumber2,
ClaimNumber3 ...

You will be forever doing things like:

If ClaimNumber = 'Something' or ClaimNumber2 = 'Something' or ClaimNumber3 = 'Something' ...

Do you really want to repeat that for all 12, or 14, or whatever?

Every time that you want to insert an entry in "Cell #8", do you really want to manually
loop through #1 - #7, #9 - #14 to check for it?  How do you check that it's not in someone 
else's #1 - #14?

Just had to isolate that in the box above to make it stand out.

What you need to do is organize that information into tables. Then you can pick
info out much easier. Without writing a lot of code.

I don't know your insurance claim business, but on the surface.

tblPolicies
===========
PolicyID - AutoNumber
PolicyNumber
PolicyOwner - FK to tblPeople
A billion other things about policies.

tblPeople
=========
PersonID - AutoNumber
PolicyID - FK to tblPolicies
PersonLastName
PersonFirstName
SocialSecurityNumber
Owner_Dependant
BirthDate

tblClaims
=========
ClaimID - AutoNumber
ClaimNumber
ClaimDate
ClaimAmount
A billion other things about claims.

That's just a simplistic view. I'm sure that someone with knowledge of the application
could readily break claims/policies down further due to repeating categories. And probably
suggest several/many lookup tables to aid in data entry.

The bottom line is that if you properly organize your tables, then your efforts won't
revolve around issues like how to avoid duplicates in a repeating field, the database
uses things Primary Keys and unique indexes to control that. Your efforts should center
on extracting info thru queries.

How many dependants have submitted a claim within the last 60 days that was over $1,000?

Select PersonLastName, PersonFirstName
From   tblPeople
Where  Owner_Dependant = 'D' And
       PolicyID In (
          Select ClaimNumber
          From   tblClaims
          Where  ClaimDate < Date() - 60) And
       ClaimAmount > 1000;

Just some thoughts,
Wayne

 

[bdhtexas]

Here is a copy of my letter, currently I put the additional claim numbers in the claim comments field, but I have no way of tracking duplicates. This is the reason for my Question (Post). Each Access ID# is unique by claim number.

The << >> indicates that the information was derived from the Access database.

«M_1stReqDt»


«ProviderName»
«Address2»
«Address1»
«City_State_and_Zip_Code»



Re: Insured’s ID# «PolicySS»
Claim# «Claim»«ClaimComments»
Patient Last Name «PatientLastName»
Patient First Name «PatientFirstName»
Account# «Account_»
AC ID# «ID»
Date of Service From«DOS_From»
Date of Service To «DOS_To»
Amount Overpaid «AmtOvpd»


Dear Provider:

We have received and processed your claim for benefits for this patient.

As a result of a recent review of this claim, we find that we have overpaid benefits in the amount of «AmtOvpd». The reason for this overpayment is «Reason_for_the_Letter».

Please consider this a formal request for reimbursement of this overpayment. To ensure proper credit, please return this letter along with your check or money order payable to Our Company Name Goes Here in the full amount of this overpayment. Should you have any questions or require additional information, feel free to contact us.

We thank you in advance as we anticipate your reimbursement.

Sincerely,


Our Company Name Goes Here
Department Name
Address
City, State and Zip Code
Phone Number



«cc_Name»
«cc_Address_2»
«cc_Address_1»
«cc_City_State_and_Zip_Code»

 

[WayneRyan]

bdh,

Pat's right!

She was right the first time! BUT, can a person have multiple policies?
(She's probably right again). Maybe this time we'll listen to her.

Wayne

 

[bdhtexas]

A person can only have 1 policy
A person can have multiple claim numbers
A claim number can only have 1 provider
A provider can have multiple claims

Example: These are all Medical Claims and you are the Patient (person). So you can only have 1 policy, but you can have multiple claims because you could go to your doctor or the hospital and you could go more than once, that is why a provider can have multiple claims. And that claim number is tied to only 1 provider because we only input 1 provider per claim.

Let me know if you need any additional information and I will be glad to comply as soon as I can.

Thanks...