Relationship in database

Relationship used in database has played a key role in manipulating data more efficiently. Database is a set of data organized in table format with relations. A database is made up of many tables. Each table would contain multiple fields. You opt to set primary key (no duplicated value or duplicated).  Those with primary key with no duplication defined  would act like "mother or father" Eg. sales header. In contrast, sales detail  would act like "children" where it allow duplication Eg, 1 sale could has many items  : sales header many sales detail

Sales Header - Saleno (Primary key) ,customerno,taxno,  (no duplication for sale no)

Sales Detail - Auto-no , saleno(foreign key) , item, price, qty, discount, (duplication for sale no.)

Defining relationship between tables is a good practice where you can make it either 1 to 1 or 1 to many or even many to many.  In below diagram, it shows 1 to many and many to many relationship.

With these relationships applied, we can enforce referential integrity. Referential integrity is used to update or delete related records simultaneously. Eg, Once the item master code (primary key of tbl_itemmaster)  is changed, the related table "itemledger" 's itemcode (foreign key) will be updated automatically. What If you delete that itemmaster code in tbl_itemmaster, its records stored in tbl_itemledger will be deleted too. This would enhance the data integrity of the database.

Try constructing your database in this manner, it would ease your days and save more times from working on VBA code on the forms.