Trying to Create a Record in Two Tables

[Hairy64]

Hello All,

I am back again with what I hope is a simple question to which I can’t find the answer.

We have a database with multiple tables. [blue]Table-1[/Blue] contains the employee name and employee ID number. [Blue]Table-2[/Blue] contains a list of equipment assigned to the employee. Both tables have the field “EMPID”, which are linked (a relationship exists) where “Enforce Referential Integrity”, “Cascade Update Related Fields” and “Cascade Delete Related Records” are all checked. The relationship is one-to-one since there is only one inventory record for each employee.

When we add a new employee to the system, a form opens asking for the name, etc., as well as the EMPID. Clicking on a ‘save’ button then creates a new record for the employee in Table-1. How do I get a record into Table-2? The only thing it will have is the EMPID, but the record needs to be created for later forms and reports to work.

Thanks

 

[lespaul]

not making sense.....Table 2 contains a list of equipment assigned to the employee, yet each employee can only have one inventory record? Does each employee only get 1 thing? A computer or a phone? A monitor or a keyboard?

I would expect:

1. A new employee is hired and a record is inserted in Table 1
2. At some future point the employee has equipment assigned to them. When that equipment is assigned, the user MUST have selected which employee to assign it to prior to the "select equipment" form. Just get the employeeID from THAT process to insert into Table 2 with the equipment information.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Hairy64]

Leslie,

[Blue]Table-1[/Blue] contains the basic employee information.
[Blue]Table-2[/Blue] contains the EMPID and a list of all equipment that could be issued to the employee.

What is missing from [Blue]Table-2[/Blue] is the serial number of the item, or a 'True/False' to indicate if an item were issued.

Therefore only one inventory record needs to exist for each employee. There are forms that open where the information on the main form is from [Blue]Table-1[/Blue], while the sub-form is from [Blue]Table-2[/Blue]. The parent/child field is the EMPID.

Since the record does not exist in [Blue]Table-2[/Blue] we get an error and the form does not work.

Another facility is doing this and somehow creates the inventory record when the new employee is added. We can't figure out how to do it. Originally we thought it was via the one-to-one relationship, especiall with the 'Enforce Referential Integrity' checked.

Does this answer your question, Leslie?

Thank You.
Mike

 

[lespaul]

So you're saying that table 2 has for EACH employee EVERY inventory item and you want to update it to indicate which items the employee received?

First off, that's not a normalized solution (see the Fundamentals document for more on normalization). You should probably have something more along the lines of:

tblEmployee
EmployeeID
EmployeeName

tblInventory
InventoryID
InventoryName

tblEmployeeInventory
EmployeeInventoryID
EmployeeID (FK to tblEmployee)
InventoryID (FK to tblInventory)
IssueDate
ReturnDate
SerialNumber

That way you only track items that HAVE been issued.

If it's possible to change your table design, you should consider it.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[fneily]

You stated - “Enforce Referential Integrity”, “Cascade Update Related Fields” and “Cascade Delete Related Records” are all checked." Are you SURE you want the last two checked??? That means ANYONE can change a parent code and all the children will be updated. I don't think you want ANYONE to play around like that. Only check those options when YOU NEED them. You're defeating this low level security feature.