98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Access Training and help » Access
Access
Resolved · Urgent Priority · Version 2010
Sun 14 Feb 2016, 06:37Delegate 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:08Trainer 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:45Delegate 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:51Trainer 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 information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access tip:Changing MS Programs Text SizeA 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. |
