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 - 12 May 2006 18:12 GMT
"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.

Signature
Duane Hookom
MS Access MVP
> Can someone tell me how to query 8 tables with a common, not primary
> key, field of Emp#?
[quoted text clipped - 20 lines]
> 11111, yellow, 5/5/06, skilled
> 12121, red, 5/5/06, beginner
MonkeyKunkel@hotmail.com - 13 May 2006 19:19 GMT
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 - 13 May 2006 23:54 GMT
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.

Signature
Duane Hookom
MS Access MVP
> 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.