access+training - Table Relationships

The UK's most regular instructor-led training courses.
Training information: access+training · Access training · Microsoft Access Training uk
See also · microsoft access training · access vba courses · access database course

Resolved · Low Priority · Version Standard

Table Relationships

Emma has attended:
Access Intermediate course
Access Advanced course

by - delegate Emma [4 posts] (2007 Oct 11 Thu, 22:08) Reply

Why do I have the same information repeated in my "courses" table?

I have created a "members/courses" table in order to set up a many to many relationship.

When in the "courses" or "member/course" table, I click on the plus sign, it reveals the members on the related course then there is another plus sign, then another and another etc etc - I must need to change something in the design or relationship set up?

Version	Date	Location	Places available	Book	Next place rate:
Access VBA 2 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003	2008 Dec 4 Thu + 5 Fri	Southwark	7	book now	£345	£399
2003	2009 Jan 8 Thu + 9 Fri	Southwark	7	book now	£345	£399
2003	2009 Feb 5 Thu + 6 Fri	Southwark	8	book now	£275	£399
2003	2009 Mar 5 Thu + 6 Fri	Southwark	7	book now	£345	£399
2003	2009 Apr 8 Wed + 9 Thu	Southwark	8	book now	£275	£399
2003	2009 May 7 Thu + 8 Fri	Southwark	8	book now	£275	£399
Full Schedule: See all 12 Access VBA course dates. Bookings currently available until 5th November 2009.

RE: Table Relationships

by - trainer David gold contributer [428 posts] (2007 Oct 16 Tue, 13:43) Reply

Hi Emma

Firstly it is better not to have many to many relationships. You you use one to many or many to one.

The problem you are experiencing is most likely related to the many to many relationship, or the is an error in the sql/query.

If you have a many to many relationship you should introduce a third table and make the relationships one to many.

Hope this helps
David

· How Reorganising Your Data Can Take Your Business to the Next Level
· Microsoft Access, an Efficient Database Tool
· Convert a Paper Trail to a DBMS
· When Is Microsoft Access Not The Best Choice For Your Business?
· Why a Microsoft Access Course Could Change the Way You Do Business

Access tip:

Relationship maps

It is important to create proper relationships between tables. You can use Access to help check your designs and evaluate them by printing them in graphical format to help with design purposes

Click on the Tools menu, click Relationships.
On the File menu, click Print Relationships. View or print as required

View all Access hints and tips