I'm having hard time defining relationship -help please

[Mike]

Hi,

I have been trying to create an Inventory and Transaction
database but having hard time relating my tables> I have
gone through the sample database in the Aceess help but I
couldn't relate it to what I'm trying to do.

I have a tblReceivalble which is serving as my Inventory,
this works fine, but when I try to relate it to my
Transaction table and the Invoice tables the relationship
which must be one-to-Many just doesn't work.

tblReceivable:
Index --AutoNumber
ProductID -- PrimeKey
ProductName
ProductDescription
Supplier
UnitsIn
UnitCost

tblTransaction:
Index -- PrimeKey
InvoiceNumber
JonNumber
Quantity (quantity sold)
UnitCost
Toatl

tblInvoice:
InvoiceNumber (code that generates a four digits Number
placed in the onclick propery of the add new record
command buton on my main form, frmInvoice)
As PrimeKey
CustomerName
Invoicedate

I'm having hardtime relating these tables. I have created
a form based on the tblInvoice and another form based on
tblTransaction and have put, frmTransaction as a subform
on the frmInvoice (Main Form), but when I try to add a
record, it says that I'm duplicating record in the
primkey or the index.

Please help defining a proper relationship between these
tables and any othet suggestion that makes my life easier.

Than you in advance for your help.

Mike

 

[GVaught]

Your tblReceivable and tblTransactions are not related. Yes you show the
field Index in both tables, but they are not related.
You show ProductID as the primary Key in tblReceivable. To relate that table
with tblTransaction you need to have ProductID in this table, which becomes
the foreign key. Also, Totals are not stored in the database; these are
calculated using a query or a textbox on a form or report. The only time you
would store a Total is when that Total will never get updated from further
transactions or you need it for historical purpose.

Before you make any design changes to your database, you need to understand
some concepts. These concepts will help you design your database where you
will not have further issues later. Look up in help or the Internet such
topics as: Normalization, Referential Integrity, Database Design Techniques
and Relationships. Get these concepts down first. Reading helps
understanding.

 

[Mike]

Thanks forf your help. I have already gone through the
Access 2000 Bible, but it seems that I need more
understanding. But, one thing I didn't understand was
that, I couldn't see hot I was creating duplicate values
in the prime key given the fact that, there is one
Invoice number for many items sold. Could you please
explain a bit of how where I might have gone wrong as far
as my formInvoice (Main Form) and my sunform based on
frmtransaction?

Regards,

Mike