Inserting values into 2 tables

By chad.klunk ·
Hello All,

I am trying to find a way to insert values into 2 separate tables.

I would like to make a small application that will help me to keep track of employees and training documents so that I can quickly see which employees need to complete required trainig forms.

I have 3 tables so far (employees, documents, and empdocs). I'm using the empdocs table to connect the employees to the documents. My problem is, that if down the road I add either a new employee or a new training document, I would like to create some code that would automatically update the tables

So for example if the employee table has:
1 Joe Smith
2 John Doe

and the documents table has:
1 Training Form 1
2 Training Form 2

then empdocs would have:
1 1
1 2
2 1
2 2

so what I want to do is, if I add document "3" to the documents table:
1 Trainnig Form1
2 Training Form2
3 Training Form3

I want to automatically end up with this in the empdocs table

1 1
1 2
1 3
2 1
2 2
2 3

If anyone has any ideas I would greatly appreciate them. I'm fairly new to VB coding so step by step instructions would be very nice of you.


This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
Thread display: Collapse - | Expand +

All Answers

Collapse -

Far from sure it's a good idea, but

by Tony Hopkinson In reply to Inserting values into 2 t ...

if you put an insert trigger on documents
then in there it's just something like

insert into empdocs
Select inserted.docno,employees.empno
From inseted,employees

You'd do something similar, if you added a new employee. Updating and deleting are issues.

Personally I'd do this with stored procedures, or at a pinch application code.

Your other problem is you've created empdocs on the basis that some documents aren't reelavent to some employees. I see nothing in your design to cope with that, and it will change things...

I'd consider having an employee group.
Put employees in a group , assign documents as relevant to groups and ditch empdocs.


Back to IT Employment Forum
1 total post (Page 1 of 1)  

Related Discussions

Related Forums