Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / May 2006

Tip: Looking for answers? Try searching our database.

Query 8 Tables no Primary Key

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
MonkeyKunkel@hotmail.com - 12 May 2006 14:51 GMT
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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.