Access Relationship Seminar

News User Comments History Notify Me

6/20/2012 1:34:26 AM
Access Relationship Seminar

In this seminar, you will learn just about everything there is to know about relationships in Microsoft Access. Setting up proper relationships between your tables is absolutely crucial to building a successful database. Bad table design, and bad relationships between those tables, is disasterous, and will cause you nothing but trouble down the line.

This seminar covers just about all of the different possible relationship scenarios that you will ever come across. We will build eight (8) different database projects so you can see how to set up good relationships between your tables. This seminar will teach you about all of the following types of relationships:

1. No relationships
2. One-to-Many
3. One-to-One
4. Many-to-Many
5. Self-Join One-to-Many
6. Self-Join Many-to-Many
7. Reverse Relationships
8. Multiple Relationships

We will build sample databases to track:

1. Children to Parents with their relation (one-to-many)
2. Which parents receive mail on their child's behalf
3. Student data with a secondary optional detail table (one-to-one)
4. Vendors to Products and vice versa (many-to-many)
5. Employees to Supervisors (self-join, one-to-many)
6. Complete family history with relationships (self-join, many-to-many)
7. Organizations to Members (whether companies, families, charities, etc.)

You can learn more about this seminar here: Access Relationship Seminar

Of course if you have any questions, post them here!

Permanent Link
Course Link: Access Relationship Seminar
Keywords: access relationships
Page Tag: whatsnew

Post Reply

Customers Addresses Jobs Comment from Mark @ Tue 5/7

Hi Richard, Thanks for your quick reply.

A JOB will have ONE AND ONLY ONE address. So I have one AddressID field in my JobT table.

I have a JobAddressSubF for showing and inputing new addresses in the JobF. The Sub form picks up the JobID ok, but I haven't found a way to get the AddressID from the subform to update onto the JobF.

I've tried a combo box on the main form using:

Forms!JobAddressSubF.Form!AddressID

To get it into the main form but haven't got it to work.

So I'm not sure where I'm going wrong here? Or should I scrap trying to get the AddressID over and just work with the JobID as this automatically comes up in the JobAddressSubF.

The other problem I'm having is when the Job address is the same as the customers home address. I have an "AsHomeAddress" option button, which when selected, I need the JobAddressSubF to automatically update to their home address and save the relationship with the JobT/AddressT.

I'm more than happy to have a paid session to get this resolved if needed. But I'm hoping it's something simple that I'm just not seeing yet?

Please can you help?

Reply from Richard Rost:

You should have 3 separate forms: CustomerF, AddressF, JobF. You would select an address from a list of addresses ON THE JOB FORM. You can use them as subforms if you want, but be sure to REQUERY the list if the addresses are changed otherwise they won't show up in the combo on the job form. Or just use separate forms and you won't have that problem. Enter the addresses on the address form. Close it. Open the Job form and pick it.

I wouldn't even bother storing a separate home address in the customer table. Put everything in the address table and just use your "AsHomeAddress" checkbox to indicate it.