Query 8 Tables no Primary Key

[MonkeyKunkel]

Can someone tell me how to query 8 tables with a common, not primary
key, field of Emp#?
My tables cant have a primary key because of duplicates. If someone has
an idea how I can fix my problem I would be grateful. Example of 1
table and data. The tables all have the smae number of fields but
different names.

Table Design:

Table: Cutting
Field 1: Emp#
Field 2: Cable type
Field 3: Date Trained
Field 4: Skill Level

Example of table data:

Table: Cutting
EMP Cable Date Skill Level

12345, yellow, 5/5/06, beginner
12345, blue, 5/7/06, beginner
11111, yellow, 5/5/06, skilled
12121, red, 5/5/06, beginner

 

[Duane Hookom]

"fix my problem"... You didn't state your problem. A WAG is that you have a
number of tables with the same basic structure. Another WAG is that Emp#
should be a primary key in an employee table and foreign key in other
tables. Another WAG is that you can add an autonumber field to create a
primary key in any table.

 

[MonkeyKunkel]

Can foreign keys have duplicate data? When I create an autonumber for
all the records and then I query by employee number it is matching the
wrong data. My original problem was I was revieving multiple
combinations of each record.

 

[Duane Hookom]

Foreign keys generally have duplicate values within a table. Your Employee
numbers would occur many times in your cutting table.

I think you have issues with creating multiple similar tables. Another issue
is expecting that you can place all tables together and get anything useful
out. It is probably impossible. We would know for sure if you provided a few
samples records from a few tables and showed us how you would expect them to
appear in a query datasheet view.

For instance, assuming I have a table of family members with one record per
person with a primary key of PersonID. Then I have individual tables for
socks, shirts, pants, and hats each with a field PersonID that identifies
who owns the item of clothing. There is no way to query this without showing
multiple instances of some items of clothing. I could easily create a report
based on the Person table with subreports for each individual clothing
table. The better solution might be to create a single table of
ClothingItems with a field for ClothingType (socks, shirt, pants, hat).

I don't know if this is your situation since you haven't provide much
information on what you are attempting to do.