Unique key

[CAM]

Hello,

I work for an insurance company and I am trying to build a database and
trying to figure out how do I make two fields unique on both tables. I have
a table called "Audits" the fields are:
Policy Number
Audit Type

Now I have a second table call "Billed", which has the following fields:
Policy
Billed Type

Policy Number or Policy is not unique by itself there maybe several of the
same Policy Number in the tables, the additional field Audit Type/Billed
Type makes it unique in combination with the Policy Number or Policy. Again
there will be many of the same Audit Type or Billed Type, but have different
Policy Numbers. This is how I want the match to be.

Table Audits
Policy Number AAA123
Audit Type AA

Table Billed
Policy AAA123
Billed Type AA

The Policy Number and Audit Type in "Audit" table should match with the
"Billed" table Policy and Billed Type.


Here is example using the same Policy Number / Policy, but with different
Audit Type/Billed Type that must matched.
Table Audits
Policy Number AAA123
Audit Type BB

Table Billed
Policy AAA123
Billed Type BB

Now if I have the same Policy Number/ Policy and both the Audit Type and
Billed type are different then obviously there is no match. How do I make
the thouse fields unique in my table? Thank you in advance.

 

[J. Goddard]

One way would be to base the PK of both tables on Policy Number + audit
type, and Policy + Billed Type; that will make them unique within their
respective tables.

The "matching" problem is a bit more difficult -

Question - if you have AAA123 + AA, in Audits, must there be a match in
Billed? If so, you should enter the data to both at the same time,
making sure that the keys match.

You will have difficulties otherwise - for example, suppose you put two
records into Audits:

AAA123 + AA, and
AAA123 + BB

Later on you want to add data to "Billed". You could add

AAA123 + AA, or
AAA123 + BB

and both would be valid - but which one would be correct?

That you could only determine by additional information, using VBA.

John

 

[Larry Daugherty]

First, my recommendation is to use Autonumber datatype Primary Keys
rather than natural keys.

Then I suggest three tables (could be two tables if we were told more
about the business and the real-world problem you're trying to solve):

tblPolicy Number
PolicyNumberID Autonumber
PolicyNumber Text

tblAudit
AuditID Autonumber
BolicyNumberID Long Integer (Foreign Key=primary key of
"parent" table)
AuditType Text (You should probably create an as yet
unmentioned table to list all of the Audit Types - one type to each
record)

tblBilled
BilledID Autonumber
BolicyNumberID Long Integer (Foreign Key=primary key of
"parent" table)
BillType Text (You should probably create an as yet
unmentioned table to list all of the Bill Types - one type to each
record)

Tables tblBilled and tblAudit are called "lookup" tables. They are a
good thing and not to be confused with "Look Fields in Tables" which
are a bad thing.

When entering data on a form, tblBilledType and tblAudit are each the
target of a combobox. That combobox will present all of the possible
choices. User selects just one.

Since we don't know what problem you're really trying to solve, I
suggest that, from what you've revealed, you need just the Policy
table and include one field each for Billed type and Audit type..
There is no need to establish Referential Integrity.

HTH