Taking records from a table to fill another table with a form

[blynna]

Hello!

I have built this database for workshop managements.. I have this table called EDB (EMPLOYEE DATABASE) - this table will be updated by-weekly to keep the employees information current (people come and go within our organization), I would like to take the data from this table to fill the attendees table. Because not all employees from the EDB take workshops. So I need to be able to select for example, the employee number and to bring the other information from the edb into the Attendees table using a form.

Lets says I have these field from EDB
employeeid
firstname
lastname
positionno
title
jobclss

and I have the same fields in attendees along with other fields
employeeno
fname
lname
posno
postitle
jobclass
depaddress
extno

Can you help me with this, or do you have a sample database that I could use?

Lynne

 

[jfgambit]

Lynne:

What you are doing is storing duplicate data in the database. This is not recommended for Space and Normalization purposes. Since you already have the EmployeeID, First & Last Names, Position Number, Title and Job class all you need in the additonal table Is Employee Number (connected as a one-to-many to the Attendees table), depaddress and extno. You can then create a query that can prompt you for the Employee ID or create a Form that will allow you to select multiple Employee IDs via a listbox and add them to a report you need.

What is the additional table used for? Are you creating a report or a Form for data entry?


"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[GingerR]

in a relational database, you do not want to duplicate the same info in two different tables.
all you will want in the attendees table is EmployeeNo, because within queries, forms and reports, you can bring in the emp info from the EDB table by joining the two tables on the EmployeeNo. Case in Point: employee number 1 is Mary Jones. you put her in the Attendee table, along with her name and title. then she gets married and you change her name in the EDB table to Mary Jones. what about the Attendees table?

What you may wish to do is this:
have a table called Workshops. Here you will list unique info about each (this is an example):

WorkshopID
Classroom
Date
Teacher

then you will have a third table which builds a relationship between the two tables:
table name WkshpAttendees
WorkshopID
EmployeeNo

that's basically all that goes in there.

so the data in the table WkshpAttendees looks like this:
WorkshopID EmpNo
1 17
1 13
1 21
2 17
2 8
2 22

so emp # 17 is going to two workshops, and the others are only going to one.

this is called a "many-to-many relationship". you can look up details in any text book.