Another relationship Question

[TaiChi56]

I cannot seem to figure this out. I do not know why this is such a hard concept to figure out. I am trying to make a gun inventory. Here are my tables.

Table1
Sold_Price
Sale_Date
Tax_Amount
Who_Sold

Table2
Purchase_Price
Received_Date
Who_Bought

Table3
Maunfacturer
Model_ID
Type_weapon
Caliber_ID
Serial_Number
Supplier

Table4
Remarks
Disposition

Table5
4473_info

My question is how do I make them relate? I am going to make a pop up form that will have all of these boxes the person can input the info. Do I put a serial number field in every table. But that would be redundant, correct? The main thing here is that the person would have to be able to search by serial number mainly and it has to relate to the 4473_Info, which is what a person fills out when they buy a weapon. I am stuck. Thank you.

 

[TaiChi56]

Can anyone help with this one please. Thank you.

 

[ScottGem]

Well first it would help if you used meaningful names for your tables. The main problem with your tables is you don't have any key fields, either primary or foreign. Each table should have a primary key (PK) that uniquely identifies the record. That value is then used as a foreign key (FK) in any related tables.

Your Table3 looks almost allright. Lets call it tblGuns and set it up like this:

tblGuns
GunsID (PK autonumber)
MaunfacturerID (FK)
Model_ID (FK)
Type_weaponID (FK)
Caliber
Serial_Number
SupplierID (FK)

This means you will need tables for Manufactrers, Models, Types and Suppliers. On your guns form you would use comboboxes to select those values.

I would then have ONE transactions table, like this:
tblTransactions
TransactionID (PK Autonumber)
GunID (FK)
TransactionType (Sold/Purchased)
TransactionDate
TransactionPrice
PersonID (FK)

The PersonID would relate to a people table that lists all persons you either buy or sell with. So that means you also need a table for them.