need primary keys with duplicates

[TechyTemp]

This is the situation....I am setting up a database for a warehouse
situation. Sales Orders are issued initially for orders which may include
several part numbers. When these orders are completed, they may be shipped
in part or in whole. This means that the same invoice number is used more
than once as well as the sales order number. Additionally, the part numbers
are not unique in that they may represent a unit or an individual part and
the numbers describe what's on the unit. They may ship the same part to
several customers.

The primary issue here is that there aren't any unique numbers to use as
primary keys AND maintain referential integrity. Sooooo....how do I arrange
for associated records to update when something, if anything, gets changed?
and Are there any other potential problems that might arise without
referential integrity? What should I do about them?

 

[KARL DEWEY]

the part numbers are not unique in that they may represent a unit or an
individual part and the numbers describe what's on the unit.
You need to fix this. How do you know if X123 is a Gramafich @ $55K or a
Dimefrix @ $2.50?

Try these tables. Set a one-to-many for each primary key to foreign key.

Sales ---
SaleID – autonumber – primary key
Saler – number – integer – foreign key from employee table
CustID – number – integer – foreign key from Customer table
SaleDate – DateTime
DueDate – DateTime
ShipType – text
Rmks - memo

SalePart ---
SalePartID – autonumber – primary key
SaleID - number – integer – foreign key from Sales table
PartNo - number – integer – foreign key from Parts table
QTY – number – integer
Price – number – decimal - currency
Meas – text (Ft, Yd, Ln, Lb, Gl, Qt, Pt, SqFt, SqYd, Rl, Bx, Ctn, etc)
Treatment – text (bubble wrap, paper, peanuts, dunnage, etc.

Invoice ---
InvoiceID – autonumber – primary key
SaleID - number – integer – foreign key from Sales table
TaxRate – number – decimal – currency
FrieghtRate - number – decimal – currency
Addr1 – text
Addr2 – text
City – text
State – Text
Zip – text
DeliveryContact – text – person
DelPhone – text
Rmks – memo

Ship –
ShipID – autonumber – primary key
Packer – number – integer – foreign key from employee table
PackDate - DateTime
Checker – number – integer – foreign key from employee table
CheckDate - DateTime

Shipment ---
ShipID – number – integer – foreign key from Ship table
ShipDate – DateTime
ShipType - text
InvoiceID – number – integer – foreign key from Invoice table
Dispatcher – number – integer – foreign key from employee table

 

[Jeff Boyce]

Are you saying that there are no unique identifiers associated with the
units (either parts or assemblages)? If so, consider letting Access create
a totally arbitrary, meaningless unique identifier (i.e., an Autonumber).
Of course, you'll still need to connect any/all parts that belong together
as the "unit". I have to assume that you have some way to uniquely identify
each part (?serial number, part number, ...?).

Regards

Jeff Boyce
Microsoft Office/Access MVP