Delete value in another table

[LanWanMan]

Hello,

I have a main table called Claims which has, among others, the following
fields:

ClaimID
EmpInvID1
EmpInvLast1
EmpInvFirst1
EmpInvID2
EmpInvLast2
EmpInvFirst2....etc

And then a second table called EmployeeInvolved and the fields:

EmpInvID
ClaimID
EmpInvLast
EmpInvFirst

If the EmployeeInvolved form, I would like to have a command button to
delete the record in this table but also delete the values in EmpInvIDX,
EmpInvLastX and EmpInvFirstX in the Claims table where the EmpInvID in the
EmployeeInvolved table equals EmpInvIDX. There is a relationship defined
for ClaimID. ClaimID in EmployeeInvolved record will equal the correct
record in the Claims table.

I hope I am not too ambiguous. Any help would be greatly appreciated.
Please reply to the group.

Thanks,

Michael
LanWanMan

 

[Jeff Boyce]

Michael

Your Claims table appears to have repeating (and redundant) data.

If you already have an Employee table with First and Last names, and an
EmployeeID, you don't need to store the employee's First and Last Names in
the Claims table (this is the "redundant" part).

If your claim can have more than one Employee related to it, consider using
a third table to relate/join the two together. By using repeating fields
(one set for each employee), your table is more like a spreadsheet than a
relational table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[LanWanMan]

Thanks Jeff.....that just gave me a mini revelation. I should not have the
EmpInvFirst1, etc fields in the main table, just a reference to them from
the second table using the EmpInvID from that second table. All I should
have in the first table is ID1, ID2, ID3, etc. referencing the EmpInvID in
the second table.

Now all I have to do is learn to delete the ID1 value in the first table
should the corresponding record be deleted in the second table. If you can
help, I would appreciate it.

Thanks again,

Michael
LanWanMan

 

[Jeff Boyce]

Michael

You got it ... or at least half of it.

The second part is to remove the repeating fields (ID1, ID2, ID3...). The
reason you need to do this is because as soon as you need to add ID4, you'll
have to remodel your entire application. All the queries, forms, reports,
and code modules that refer to the "3" IDs would have to be revised to refer
to 4. And then when you have to add a fifth?!

Instead, use a Claims table, and Employee table and a ... "AssignedEmployee"
table. The third table will contain:

trelAssignedEmployee
AssignedEmployeeID (primary key)
ClaimID (foreign key ... points to the ClaimID in the Claims
table)
EmployeeID (foreign key ... points to the EmployeeID in the Employee
table)

With this design, you can have a claim without any assigned Employees, and
one with 20 assigned Employees. Each valid combination of Claim X Employee
requires another row in the table.

?You want to see how many claims EmployeeID = 999 has? Run a query on the
new table, looking for EmployeeID=999. In your design, you have to look in
ID1, and in ID2, and in ID3, and in ... (you get the picture).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[LanWanMan]

Jeff,

That is great help and it is all starting to become clear now. Currently I
have the command button which opens the form "Employee" with openargs. The
form "OnCurrent" brings the ClaimID from the Claims into the
Employee.ClaimID. This creates a new Employee record and EmployeeID. How
then do I create the new record in tblAssignedEmployees and plug in the
values ClaimID and EmployeeID? Would it be an "OnClose" event?

Thanks again....you MVP's are great!

Michael
LanWanMAn

 

[Jeff Boyce]

Take a look at a main-form/subform construction. It sounds like you'd list
Claims in the main form and use the subform to add Employees.

Regards

Jeff Boyce
Microsoft Office/Access MVP