Question

Locked

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.

Thanks
CK

This conversation is currently closed to new comments.

2 total posts (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.

HtHs

Back to IT Employment Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums