How to create a Microsoft Access One to Many Relationship
Posted on 2009-02-17
I know I'm being silly, but I am completely stumped! I have an Access database where I want to store manufacturers and their products. I have two tables, the first (tblManufacturer) has three fields: an autonumber field which is the record ID (and Primary Key), a field for Manufacturer name and a Lookup field for the products they produce. The second table (tblProduct) has three fields: an autonumber field which is the record ID (and Primary Key), a Lookup field which looks up the Manufacturer from the first table, and a product field.
What I want to do is look at a manufacturer in the Manufacturers table and click the lookup to see what products they produce. Currently, when I do this, I get a list of ALL products in the Products table, regardless of their manufacturer. I have tried everything I can think of and just about every combination of relationships, but I only ever get all records. At the moment, I have a one to many relationship set between the ID field in the Manufacturers table and the Manufacturer field in the Product table set to include only those records where both fields match.
An example is: Manufacturer ACME has a record ID of 6 - In the lookup in the Products table, the record equals 6. When I select the lookup in the Manufacturers table, I don't just see products relating to ACME, but I see all products, regardless of their manufacturer.