Prim Key Problem

[Mike]

Hi,

This is a follow up to my earlier posting. Steve has been
working with me on this but please feel free to answer as
I can use any help/recommendation as I can get.

I have created two tables: tblInvoice, with InvliceNumber
as my Prime Key, CustomerName, and InvliceDate.

The second table: tblTransaction, with many feilds such
as, Part Name, Part Number, Quantity sold, UnitPrice,
Total Cost, JobNumber, and the InvoiceNumber, with Index
(AutoNumber as the Prime Key).

Now I have created forms based on each table and Have
placed, frmTransaction as a subform on the frmInvoice. I
get this error that duplicate value in the prime key. I
removed the autonumber in frmTransaction and made the
InvoiceNumber the prime key for this table but I still
get the same error. I I go ahead with ouy full refrencial
Integrity, it allows me but I think the relationship
between the two must have the full refrencial Integrity
checked. Can someone please help me efining the
relationship and the prime keys for these two tables?

There will be many PartNumber in one Invoice.
, i.e. many product might me sold in one invlice.

Thank you in advance for your help.

Regards,

Mike

 

[John Vinson]

Now I have created forms based on each table and Have
placed, frmTransaction as a subform on the frmInvoice. I
get this error that duplicate value in the prime key. I
removed the autonumber in frmTransaction and made the
InvoiceNumber the prime key for this table but I still
get the same error.

InvoiceNumber CANNOT POSSIBLY be the primary key for the Transactions
table. By definition, the Primary Key is unique - that is, you would
be allowed one and only one transaction for each InvoiceNumber.

InvoiceNumber should be a *nonunique* foreign key in tblTransactions;
there should be a *different* field to serve as the Primary Key
(perhaps an Autonumber).

I I go ahead with ouy full refrencial
Integrity, it allows me but I think the relationship
between the two must have the full refrencial Integrity
checked. Can someone please help me efining the
relationship and the prime keys for these two tables?

There will be many PartNumber in one Invoice.
, i.e. many product might me sold in one invlice.

Won't you need a third table here - tblParts, with PartNumber as the
Primary Key?

 

[GVaught]

If you have the sample database called Northwind, it may be a good database
to study.

 

[Mike]

I have another table called tblOrders which is our
INventory or receivable. That table has partNumber,
PartName, PartDescription, UnitsIn, UnitPrice and two
calculated feilds to total the price on any orders we
recive. Can this table serve me as the Parts tables with
the PartNumber or an AutoNumber as the Prim key?

How can I make the InvoiceNumber to be a foreign key in
table Transaction?

Thank you in advance for your help.

Regards,

Mike

 

[John Vinson]

I have another table called tblOrders which is our
INventory or receivable. That table has partNumber,
PartName, PartDescription, UnitsIn, UnitPrice and two
calculated feilds to total the price on any orders we
recive. Can this table serve me as the Parts tables with
the PartNumber or an AutoNumber as the Prim key?

tblOrders? Is the name indicative of anything? One would think that
tblOrders would contain Orders - not parts. Is there only one record
for each PartNumber in this table? If so, then yes - if PartNumber is
made the Primary Key (if it's short, stable, and unique you don't need
an Autonumber).

How can I make the InvoiceNumber to be a foreign key in
table Transaction?

By creating a field of the same datatype as the InvoiceNumber in the
Invoices table (Long Integer if that's an autonumber) and using the
Relationships window to create a relationship between the fields.

As suggested elsethread, you really may want to look at the Northwind
sample database - which contains tables for Products, Orders,
Customers, and pretty much all the things you have in your database.