Relationship Help 2

[nwalk81]

Hello all,

I have a situation where I get a feed of information. This information has to then be broken down by queries to be seperated between employy info and claim info. I am having trouble with the relationships because from the feed file comes two necessary numbers (claim code and employee number). Claim code is always unique however employee number is not. I need to be able to have a base form (by employee) that connects to my base subform by employee and then to several other subforms by claim code. The problem is employee is not unique, however the combination of employee and claim is. Does anyone know how I would work this?

 

[SgtJarrow]

If I follow your logic, you have many claims per employee. Let me know if I am way off here.

Let me use a health insurance company as an example. When an employee gets injured, the employee number is assigned. It may be social, or whatever. Each visit to a doctor or whoever generates an "invoice" for the employee. So when the data arrives you have a unique invoice number, but the employee number matches something already in the system.

Sound about right?

I think you may be thinking backwards here....because in all actuality the employee number truly is unique. The claim number is related to the employee...not the other way around. This means you have a one-to-many relationship (one employee to many claims).

So your tables would be something like:

tblEmployee
EmployeeID PK
EmployeeName
etc
etc
etc

tblClaims
ClaimNumber PK
EmployeeRef FK to EmployeeId in tblEmployee
etc
etc
etc

Again, if I have not understood your problem, let me know.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[BobRodes]

Sounds exactly right to me as well.

 

[nwalk81]

Yes both are correct. I figured this one out before anyone replied. I extracted the unique employee information from the feed as well as the unique claim info from the feed, then linked the two. Thanks for replying.