Tables and Reports

[Maria]

So I have a lot of tables. One with employee trainings, one with
contact information, one with department information, etc. All of them
are related to employee names, which contains the primary key. I'm
trying to put information from many of these tables onto a single
report, but when I do, it says it is not possible because one or more
tables aren't related to one another...but they are...through the
employee names, which is the primary key. I am very new to Access, so
I may be misunderstanding relationships and what is and is not allowed.
Can anyone shed some light on this? Thanks in advance.

 

[Jeff Boyce]

Maria

First, even though YOU know the tables are related via the "employee names",
have you told Access about this? You'd use the Relationships window to draw
the connection(s).

Second, I'm not clear if you are using an EmployeeID, some other unique
identifier, or the actual 'employee name' as your primary key in the
Employee table. If the latter, how do you tell two John Smith's apart?

Third (related to the first), do you have a corresponding "foreign key"
field(s) in each of your related tables? This is how you'd show Access
which way to connect the tables.

Fourth, in your query, are you joining the tables together? On what/which
field(s)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Maria]

I have relationships drawn. The relationships window actually
resembles a spider, with the table of employee names in the middle and
all related tables surrounding it. In order to make the relationships,
I simply dragged the primary key (from 'Employee Names') to the 'names'
field in the table I wished to relate. Did I do this correctly? It
was my understanding that this method would keep the primary key the
'names' field in the 'Employee Names' table and the 'names' field in
each related table would be a foreign key.

The plan for duplicate names is, if it were to occur, to add a middle
initial or entire middle name. It may seem short-sighted, but it's the
most straightforward way for right now.

I have not used a query to join the fields in the different tables
together. I have tried, but have gotten the same error message as I do
for Reports.

 

[Jeff Boyce]

Maria

I'm not there, I can't see what you're looking at. I have no idea what you
mean by the "names" fields.

What field/fields are you using as your primary key? Do you have
corresponding field(s) in each of the related tables? Are they the same
data type?

When you dragged the primary key from EmployeeNames to the other tables, did
you drag to the other tables' "Foreign Key" fields, or are the "names"
fields in the other table the Primary Keys in those other tables?

More info, please...

Jeff Boyce
Microsoft Office/Access MVP

 

[Maria]

My apologies for not being clearer. I have a table with only employee
names(entitled "EmployeeNames"), and those fields (LastName and
FirstName) are the primary keys. Other tables in this database have
LastName and FirstName fields, containing the same information. In the
relationships window, I have dragged the LastName and FirstName fields
from "EmployeeNames" onto those same fields in every other table.
According to my understanding, this is the way to make the LastName and
FirstName fields in other tables foreign keys. In this way, I have
made many one-to-many relationships, all relating the "EmployeeNames"
table to every other table. Is this the right way to go about doing
this?

 

[Jeff Boyce]

Maria

As I understand it, a (child) table (i.e., in a "many" relationship to a
parent) requires a Primary Key field AND Foreign Key field(s). Otherwise,
if the LastName & FirstName are the Primary Key in the second table, you
have a 1-1 relationship (you have to have unique instances of Primary Key
value(s)).

By the way, using the combination of LastName & FirstName to be a unique row
identifier (in EmployeeNames) is very risky. If you ever hire more than one
John Smith, your data structure fails. If you add "Smith, John" and "Smith,
Jon", is this a mistake or two different people?

Regards

Jeff Boyce
Microsoft Office/Access MVP