Can't assign primary key

[Dale G]

I can't set the primary key. The DB is for ordering employee uniforms.

I’m trying to set the first primary key to tblEmployees. EmpNo.


When I try to set a field as the primary I receive this message.

(The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again).

The numbers are 5 digits and Unique. My tables are.



tblAnniversary. EmpNo, AnniversaryDate.

tblArticles. ArticleID, Article, SupplierID.


tblEmployees. EmpNo, LastName, FirstName.


tblSupplier. SupplierID, Supplier.

tblUniformTrans. OrderID, TransID, ArticleID, Size, Quantity, DateOrdered,
DateReceived, Status, SupplierID.

tblUniformOrders. OrderID, EmpNo.


I think that may be all I need?

I thought I would need to set EmpNo, from tblEmployees. ArticleID, from
tblArticles. SupplierID, from tblSupplier. OrderID, from tblUniformOrders.

Also I don’t know how to set the FK. Should there be a FK EmpNo in
tblAnniversary, and tblUniformOrders?

FK ArticleID in tblUnformTrans, FK OrderID in tblUniformTrans, FK
SupplierID, in tblUniformTrans?

Any info is appreciated,


I do have other tbls but I’m not sure if they are necessary. tblSize,
tblGender, tblShipping, tblDepartment, tblCompanyInfo, tblCategories.


Beetle did suggest a size and categories table so I’m still thinking about
those.

 

[Tom Wickerath]

Hi Dale,

I’m trying to set the first primary key to tblEmployees. EmpNo.
The numbers are 5 digits and Unique.

If you cannot set the Empno field in tblEmployees as a primary key, then you
most likely have two (or more) records with the same Empno value. There is a
Find Duplicates query that will help walk you through the process of creating
a query to find these duplicates. You didn't say which version of Access you
are using, but if it is Access 2000, 2002 or 2003, then you start the process
by clicking on the Queries tab in the database window, followed by clicking
on the New button. The fourth choice in the list, "Find Duplicates Query
Wizard", is the one that you want in this case. The wizard should guide you
to a successful result.

Also I don’t know how to set the FK. Should there be a FK EmpNo in
tblAnniversary, and tblUniformOrders?

In Access, you do not set a foreign key field. The field should be the same
data type as the primary key field. In addition, if the field is a Text data
type, the Field Size properties should be the same.

Note: The foreign key for an Autonumber data type must be a Number / Long
Integer. If using Access 2003 or lower, I recommend that you remove the
default value of zero for this field, since you likely will not have a
primary key value of zero in the parent table. Access 2007 does not include
zero as a default.

When creating your relationships, I highly recommend that you include the
option to Enforce Referential Integrity.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Dale G]

9-24-08 Me

Thank you Tom, You were right. I found 1 duplicate.

I’m using Access 2007.

Last night I was able to set the other keys.

I’m thinking of placing the Employees anniversary date on the tblEmployees
and deleting tblAnniversary.

Do you think that’s a good idea?

What are your thoughts about a size table?

Would it be better to just enter the data when I place the order?

Mens pant is a long list and I’m not sure how to set up fields for a size
table.


I would like to place an overdue display in the header of the
frmUniformOrders, something like the one for possible duplicate in the
Contacts db.

I think I would have to place a date ordered text box in the form and
another text box in the header that would remain invisible until the set time
between the two has elapsed.

Could you assist or lead me to a method (or link) to get that done?

Any info is appreciated.

 

[Tom Wickerath]

Hi Dale,

Please start a new thread with these additional questions--I don't have the
time right now to provide the followup. I recommend posting your design
questions in the Database Design newsgroup:

http://www.microsoft.com/office/com...=microsoft.public.access.tablesdbdesign&exp=1


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________