access database courses - Relationships between tables

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

Resolved · Low Priority · Version Standard

Relationships between tables

Rachel has attended:
Access Introduction course

by - delegate Rachel [1 post] (2007 Jun 4 Mon, 14:21) Reply

How do the relationships created between tables relate to the queries that we may then run on the tables?

Version	Date	Location	Places available	Book	Next place rate:
Access Intermediate 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003	2008 Nov 25 Tue	Bloomsbury	4	book now	£207	£215
2002/XP	2008 Nov 26 Wed	Tooting	0	FULL
2003	2008 Dec 2 Tue	Southwark	5	book now	£198	£215
2007	2008 Dec 5 Fri	Bloomsbury	4	book now	£224	£224
2003	2008 Dec 9 Tue	Bloomsbury	0	FULL
2002/XP	2008 Dec 11 Thu	Tooting	0	FULL
Full Schedule: See all 55 Access Intermediate course dates. Bookings currently available until 24th November 2009.

RE: Relationships between tables

by - trainer Tracy platinum contributer [586 posts] (2007 Jun 4 Mon, 16:13) Reply

Hi Rachel

Thanks for the question.

The key to relationships is a common field in two tables, i.e. the primary key. Access uses a system of rules called referential integrity to make sure relationships between records in related tables are valid, and that you also don't accidentally delete or change related data.

It will also depend on the type of relationship you choose one-to-one, one-to-many or many to many. Using the primary key and referential integrity Access can combine the results of two tables and return the answers to your query

Tracy

· Microsoft Access as a Business's Database Management System
· Learn about Subqueries in Access Courses
· Material covered on a Microsoft Access Course
· Microsoft Access Training Unleashes the Power of the Pivot Table
· Why go on a Microsoft Access Training Course?

Access tip:

Deleting duplicate records from a table

You cannot delete records tables where there duplicate records. A way around this is to create a new table which wont hold the duplicates. and then deleting the old table.

1. Use a make-table query based on this table only. IMPORTANT - Ensure that you include all of the fields from your original table or you may loose data.

2. Open the query's property sheet by using VIEW, QUERY, PROPERTIES, and set the Unique Values property to Yes

3. Because you have selected the Unique Values to Yes when you run the query, Access creates a new table without duplicate records. You can now delete the old table and rename the new one.

View all Access hints and tips