Access relationships

1
votes

I have database with many tables. Couple ones from database are related to other one.

Arrangement of tables:

ID (automatic number with primary key), ID_sup (number), supplier (text), ...

Related table:

ID (number with primary key), supplier (text), address (text), ...

So in tables are the same ID_sup with ID, supplier with supplier. Tables contains only few suppliers from the table Related table.

I have tried to connect it with Enforce Referential Integrity + Cascade Updated Related fields but it doesn't work.

I have tried to change data types and primary keys as well but I have always these error messages:

  1. Not found any unique index for linked primary table field.
  2. Relationship must be on the same number of fields with the same data types. (Error 3368)
2
ms-accessforeign-keysrelational-databaseprimary-key

2 Answers

1
votes

It sounds to me like you want to have the Supplier's name duplicated (or "denormalized") in more than one table and have a Relationship automatically update the name in one table if it is changed in the other. However, Relationships don't (usually) work like that.

Instead, you keep the Supplier's name (and other Supplier-specific details) in just the [Suppliers] table, and then refer to the Supplier in other tables by the SupplierID only. This is called "normalizing the data".

Now, if you want to have the Supplier's name appear in a query (or report, etc.) about [Orders] then you JOIN the [Orders] table with the [Suppliers] table in your query, something like this:

SELECT Orders.OrderID, Orders.OrderDate, Suppliers.SupplierName
FROM Orders INNER JOIN Suppliers ON Orders.SupplierID = Suppliers.SupplierID

You can still use a Relationship to enforce Referential Integrity (i.e., make sure that every Order refers to a valid Supplier), but you do that with just the SupplierID and nothing else.

0
votes

You want to create a one-to-many relationship from Table2.ID to Table1.ID_sup. In the MS Access "Edit Relationships" window, Table2.ID is on the left, and Table1.ID_sup is on the right. MS Access uses an infinity symbol to represent the "many" side of the relationship.