How can I link fileds from two different tables?

[Steve Taber]

I'm building a database to track uniform inventories. I have one table that
shows what was issued and another that has what the employee has on hand. I
want to be able to enter the name of a new employee on one table, but have
that name transfer to the other table automatically. Vise versa when I delete
an employees records. Is this possible?
Thanks

 

[mnature]

Steve, it is difficult to envision what your database looks like. It almost
sounds like you should be using an Excel worksheet. I'm not sure what the
difference is between what an employee has been issued, and what they have on
hand. It seems like you could be using a single table:

tblEmployees
EmployeeID (key)
EmployeeName
UniformsIssued
UniformsOnHand

Then, the UniformsIssued and UniformsOnHand would be updated periodically.
But I have the feeling that what you need is a little more complicated than
that. More information would be appreciated.

 

[mscertified]

You should not be keeping a name in two places.
You should have an employee table with an id for each employee.
The id should be used in each table.
I dont understand the need to have a new employee update more than one
table, since presumably a new employee has no uniforms until they are issued.
Why do you even need an 'on hand' table. Would this not just equal the total
of uniforms issued?
For the deletions, you need to relate your tables (via employee id) and then
specify 'cascading deletes'. Thus when an employee is deleted, al uniform
records for that employee are also deleted.
However, I think you need to re-evaluate your design un;less I am not
understanding something.

Dorian

 

[Tim Ferguson]

I'm building a database to track uniform inventories. I have one table
that shows what was issued and another that has what the employee has
on hand. I want to be able to enter the name of a new employee on one
table, but have that name transfer to the other table automatically.
Vise versa when I delete an employees records. Is this possible?

It's probably possible but is sounds like a Really Bad Idea.

When designing a database, you need to begin with the Things You Need To
Know About. In this post you have mentioned:

WhatWasIssued (ProductID, Colour, MaximumDensity, etc)

Employee( StaffNum, FName, LName, MaxNumberOfThingsAllowed)

Issues(ProductID, StaffNum, DateOut, DateReturned, etc)


You can easily tell which employee has what things by looking at the
Issues table for things that have a DateOut and no DateReturned.

I guess that the real life situation is a tad more complex, though.
HTH anyway


Tim F

 

[John Vinson]

I'm building a database to track uniform inventories. I have one table that
shows what was issued and another that has what the employee has on hand. I
want to be able to enter the name of a new employee on one table, but have
that name transfer to the other table automatically. Vise versa when I delete
an employees records. Is this possible?
Thanks

With difficulty - but it's an incorrect design.

You should have an Employee table, with NOTHING in it about
inventories - just a unique EmployeeID and the employee's name and
other biographical information.

Your inventory tables should have an EmployeeID as a link to the
Employees table - and no other employee information.

John W. Vinson[MVP]