Link tables in access?

J

John

Hello.

I have a master Company table with two columns WorkerID and WorkerName. I
also have a lookup table named Worker with the fields WorkerID and Name.

What it should do is show in the Company table the workerid and the
ASSOCIATED workername, based on the relationship it finds in the lookup
table.

(i.e. workerid 78 is workername john jones, workerid 84 is workername bill
smith, etc.)

As it is, in my Company table, it shows in the WorkerID column 78 and 84. In
the workername column, it is blank.

How do I get the master table to recognize the relationships that exist in
the lookup table??


Thanks for help!

John
 
G

Guest

Hi John,
I also have a lookup table named Worker...

To be honest, you really should avoid using lookups at the table level. See
the 2nd Commandment here:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm

.... with the fields WorkerID and Name.

"Name" is a reserved word in Access and JET. You should avoid assigning
reserved words to anything that you assign a name to in Access. Also, in
general, it is better to break name data into two fields minimum: FirstName
and LastName.

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266


Lookups are defined on the many side (child) table to look up a value in the
one side (parent) table. What is the relationship between you Workers table
and your master Company table? While one worker can hold many jobs within a
company (1:M relationship), it is fairly rare for one worker to work for many
companies.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hello.

I have a master Company table with two columns WorkerID and WorkerName. I
also have a lookup table named Worker with the fields WorkerID and Name.

What it should do is show in the Company table the workerid and the
ASSOCIATED workername, based on the relationship it finds in the lookup
table.

(i.e. workerid 78 is workername john jones, workerid 84 is workername bill
smith, etc.)

As it is, in my Company table, it shows in the WorkerID column 78 and 84. In
the workername column, it is blank.

How do I get the master table to recognize the relationships that exist in
the lookup table??


Thanks for help!

John
 
J

John

Thanks all for the help and the reading.

What I would like is for the Worker's name to show up in my Company table
instead of (or in addition to) his workerid.

For instance in my company table, each row is a procedural step for whatever
procedure we are doing that day.

What I would like to do is look at my company table and see the name of the
guy that did step #1. As it is, all I can see is his workerid number (which
is too cryptic). (which worker is number 78? who is number 43? have to flip
to the worker table for that info, etc.)

The only place where I have which worker goes with which number is in my
Worker table (workername, workerid), not my company table. (job step number,
workerid, workername is blank? (This is what I am trying to fix), time
elapsed, page of reference book used, etc.)

I probably muddied this a bit, but hoepfully someone can see what I am
asking about.


I can't see how to get the worker table relationship of worker to workerid
to show up in my master table under the same column names.

Thanks for sticking with me!

John
 
J

John Vinson

Thanks all for the help and the reading.

What I would like is for the Worker's name to show up in my Company table
instead of (or in addition to) his workerid.

Well.. no. Actually, you don't.

You shouldn't CARE what's in the Table. Tables are for data storage;
you would ordinarily never look at them, or export them, or edit them.
The tables sit in the background, and you interact with them via
Queries (for combining data from one table with data from another);
you would look at or edit data in a table using a Form (generally
based on a query); you would produce printouts using a Report
(essentially always based on a query); you would export data to other
programs using a Query.
For instance in my company table, each row is a procedural step for whatever
procedure we are doing that day.

What I would like to do is look at my company table and see the name of the
guy that did step #1. As it is, all I can see is his workerid number (which
is too cryptic). (which worker is number 78? who is number 43? have to flip
to the worker table for that info, etc.)

Look at a Form based on the Company table; on that Form put a combo
box bound to the WorkerID but displaying that worker's name.
The only place where I have which worker goes with which number is in my
Worker table (workername, workerid), not my company table. (job step number,
workerid, workername is blank? (This is what I am trying to fix), time
elapsed, page of reference book used, etc.)

That's the ONLY place that the worker's name SHOULD be stored. That's
how relational databases are *designed to work* - they use the
"Grandmother's Pantry Principle" - a place (ONE place!) for
everything, everything in its place. If you need to see the
worker'sname in conjunction with data in the project table, *USE A
QUERY* linking those two tables; storing the name redundantly is
neither beneficial nor necessary!

-
John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top