Access

Resolved · Urgent Priority · Version 2010

Sun 14 Feb 2016, 06:37 Delegate Marian said...

Marian has attended:
Access Intermediate course

Access

How do you create a many to many relationship between two tables, using an existing table as the junction table. The many to many relationship is between the tblOrders and the table tblConsumer, you have to use the existing table tblAssistant as the junction table.

The tblAssistant has one primary key 'SalesAssitantID'. The other fields in this table are: FirstName, LastName, DOB

The tblConsumer has one primary key 'ConsumerID'. The other fields in this table are: Title, FirstName, LastName, CompanyName, Origin, ConsumerCredit, EmailAddress

The tblOrders has one primary key 'OrderID", The other fields in this table are: ConsumerID, ProductID, TotalQuantity, CourierCollectionDate, CourierShippingDate, SalesAssistantID, ShippingCompanyID, OrderPaid, OrderDate

Tue 16 Feb 2016, 09:08 Trainer Jens said...

RE: Access

HI Marian,

Thank you for the forum question.

To create a many to many relationship you have to have as you know a junction table. In the junction table you must have a foreign key from the two tables tblConsumer and tblOrders.

Add the fields ConsumerID and OrderID to the tblAssistant table and create a relationship from tblConsumer ConsumerID to tblAssistant ConsumerID and from tblOrders OrderID to tblAssistant OrderID.

But are you sure you want this?

If you have a many to many relationship between tblConsumer and tblOrders the consumers can have many orders but a order can relate to many consumers.

To me it more sounds like you need a one to many relationship. A consumer can have many orders but one order can only relate to one consumer.

I hope that I have been helpful.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 17 Feb 2016, 07:45 Delegate Marian said...

RE: Access

How do you add the ConsumerID and OrderID field to the tblOrders? When I tried to do this and set the above 2 fields as Primary Keys - when saving it comes up with a message of 'Index or Primary Key cannot contain a Null value'. I know there are empty fields in the tblOrders once I add these fields - there are more records in the other two tables. Is there a way at getting round this?

Wed 17 Feb 2016, 09:50 Edited on Wed 17 Feb 2016, 09:51 Trainer Jens said...

RE: Access

Hi Marian,

If you want to create a many to many relationship you are actually making two one to many relationships to the junction table. A one to many relationship must be between to related fields one primary key to a foreign key. Only one of the related fields can be a primary key. So the two new fields in the tblAssistant must be normal fields and not primary keys.

If a relationship is created between two primary keys the relationship is a one to one relationship. One record in the first table is only related to one record in the second table and one record in the second table is only related to one record in the first table.

I hope this can help you. If not let me know.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Access tip:

Changing MS Programs Text Size

A handy way to increase or decrease the size of text in Microsoft Word, Microsoft PowerPoint, Microsoft FrontPage, or Microsoft Publisher, first, select the text you want to resize.

Then, to increase the font size, press CTRL+SHIFT+>.

To decrease the font size, press CTRL+SHIFT+<.

View all Access hints and tips