Automatically fill fields in one table from aother

[Guest]

I have two tables. One with employee information and one with general
information. I want to enter an employee id in the general table and have it
automatically fill in the name of the employee from the employee table. I
did the autolookup query which gives me the info, but won't actually put it
in the field in the general table. I don't want a sub table. I'm dealing
with over 6000 employees.

What am I missing? This is driving me nuts.

 

[Rick B]

You don't put the data in the table. That would be redundant.

You are missing the whole point of a relational database. You create
multiple tables with like data in each table. For example...

Table1
EmployeeNumber
EmployeeFirstName
EmployeeLastName
Employee Address
EmployeeHomePhone
etc.


Table2
EmployeeNumber
HireDate
DepartmentCode
PayCode
Supervisor
TerminationDate

Table3
DepartmentCode
DepartmentName
BuildingLocation


In Table2, you would not repeat the employee name. You just link table 2 to
table 1. In table 2 you would not need the department name, you would
simply relate the two tables.



I think, in reading your post, that the biggest issue is that you are
working in the Tables. Tables are simply devices used to store the data.
They are not user interfaces. You should be doing your viewing, adding,
editting, reporting in Forms and Reports (an maybe queries in a few cases).
Forms and reports allow you to view the related data like you want, set
edits, require data, prompt for entries, set defaults, use drop-downs and
searches, etc.

Rick B

 

[Guest]

Thanks for the quick response. I do understand the relational database. I
have lots of them. But for this particular one, I want it to fill in the
fields on this table. I can get the information from the autolookup query
but it seems like an extra step. I need to see who the employee is when I
enter the empl#, since the empl# is coming from an outside source that isn't
always reliable.

 

[Rick B]

When you enter the Employee number in your form, why can't you simply have
the name fill in from the other table? Again, there is no need to store
this along with the record in a second table, but you can certainly display
it after tour cursor exits the employee# field in your form.

I still don't understand why you'd want to store it a second time though.
What happens if that person gets married or divorced? You want to change it
in two tables?

Assuming your answer is yes, then I guess you would need to include the name
in your new table, still perform the exact same look up and display, but add
the additional step of having your code copy the name into the bound field
on your form. In short, you;d do everything the proper way, but you'd add
one more line to your code that would paste the name into your bound field.


Rick B