Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 

News      User Comments     History     Notify Me

6/20/2012 1:34:26 AM
Access Relationship Seminar
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

 
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

Comment from Kris Cunningham @ 11/10/2016
I was starting to normalize my tables and think about the relationships among them. I have a Node table and a DesignStatus (DS) table that I know is a one to many from the DS table to the Node table but am wondering, just out of curiosity, if it is a one to one from the Node table to the DS table.

Someone told me I m defining the relationship incorrectly b/c I ask myself if  a  node has only one or one to many DS records versus asking myself if  each  node has only one or one to many DS records. I don t understand the difference, but I see that it is important b/c his way means that each node (many) can only have one DS (one), which changes the relationship from Node table to DS table to a many to one relationship. 1) Which is the right relationship, one to one or one to many from the Node tbl to the DS tbl, and 2) which is the right question to ask yourself when trying to define a relationship?

P.S. A node is used to deliver internet service to a neighborhood and I want to record a node s status at any one time only, so each node can be of one status only. It will start out  Not Complete  and then change to  Complete  as the project moves forward, which I will change using a combo box, but I will not be creating multiple DS records for that node.
Show Just This Thread        Post Reply
Unique Ids Comment from Kris Cunningham @ 10/31/2016
One other question please: When creating the combo box for the MemberID in the subform - it was based on the Member query and I noticed the dialog box that helps you add the combo box asked 'Choose a field that uniquely IDs the row. Which column in your combo box contains the value you want to store or use in your database?' Then the next screen asks, 'When you select a value in your combo box, what do you want MS Access to do?' And you chose, 'Store that value in this field: Member ID'. Aren't those 2 questions asking the same thing? If not, can you describe in detail what the difference is? Sorry. Thank you, again!


Reply from Alex Hedley:

Say you have a Query that brings in Tables that contain the same value in a Field, i.e. name you wouldn't want to store that as your ID field as how would Access know which Record to choose if it is a duplicate, you need to pick a unique value like an ID or a composite key.
Once you have that you still have a choice as to whether to store that value in your Table. You could choose another.
Show Just This Thread        Post Reply
Lesson 16 Review Comment from Kris Cunningham @ 10/31/2016
. In the beginning of the Lesson 16 Review you said, to mention if you would like to see a seminar on how to fix a flat file and build relationships. I would like to see it, especially how you normalize various examples of tables. And would like to see even a mini seminar or however long it would take with more examples of normalization other than what you showed in an earlier lesson. I m having trouble translating the examples I see with how to normalize my data, which pertains to the telecommunications industry. I'll add this suggestion to the survey. Thank you!


Reply from Alex Hedley:

Expert 22 briefly covers this.
Show Just This Thread        Post Reply
Relationships Comment from Kris Cunningham @ 10/31/2016
I need to make sure I understand, please: ~5.35 in Access Relationship Seminar Lesson 15 - So, you had both OrgID and MemberID fields in Address table b/c you knew ahead of time you were going to need them on *separate* occasions, e.g., the OrgID to be the Child Link in the AddressSubF to the OrgID in the OrgF and then the MemberID to the be Child Link in the AddressSubF to the MemberID in the MemberF for when you did an example of putting subforms into the MemberF, is that right?  B/c when you started adding those 2 ID fields to the Address table, I immediately thought you were making it a junction table in order to have a many to many relationship with the Org AND the Member at the same time, but you said at ~20:43 that you were setting up a one to many relationship between the AddressSubF and the OrgF and then the AddressSubF and the MemberF separately, right? Also, at first, I didn t see why you could not add an AddressID to the OrgXMemberJT (the junction table) and make the Address a many to many between the Org AND the Member b/c theoretically that could happen. In other words, I wanted to filter the addresses to just show those shared by both the Org AND the member, but when I tested it and added an Address combo box to the OrgXMemberSubF, next to the MemberID combo box, the values of the address did not match the members, only the organization. 1) I m getting so confused now, so why did the Address combo box make a relationship with the Org and not the Members in the combo box next to it? All of the ID fields, OrgID, MemberID, and AddressID are in the junction table, OrgXMemberJT. (I based the Address combo on the Address table, pulled over the AddressID and Street fields and stored the value in the AddressID.) 2) To filter like I m suggesting, would there need to be criteria in a query that would be the basis for both combo box fields, MemberID and AddressID? If so, would you just say in the Member ID criteria =AddressID and in the AddressID field criteria under the Or row put = MemberID? I don t think you would want to do this but just for argument s sake. Thank you, again.


Reply from Alex Hedley:

Maybe writing down the values you want to see on paper would be a good way to visualise, then transfer it to the db once you have a grasp.

Sometimes it's hard to look at a table of just numbers, 1 option is to create a Query that joins the Tables to their respective IDs then pull in a Name Field to see what you have.

From that you can take the row you would expect to see and see what filters you would need to apply.
Remember AND across OR down, it's likely you'd want AND across if you are wanting to filter for specific Orgs etc.

Show Just This Thread        Post Reply
Comment from Kris Cunningham @ 10/27/2016
**CORRECTION TO MY QUESTION** I ended by saying, 'So, in that way, it's that quite the same, correct?'. What I meant to say was, 'So, in that way, it's NOT quite the same, correct?'
Show Just This Thread        Post Reply
Lesson 15 Comment from Kris Cunningham @ 10/27/2016
Hello. I am on Lesson 15 of this seminar and I think I finally (after several weeks of off and on studying/questioning) understand how I need to create my database. I do want to make sure I understand, though: I read that many to many relationships are just two one to many relationships joined by a junction table, but that isn t quite right, is it? For example, a one to many relationship would be A can have zero to many of B, but B could only have zero to one of A, correct? Like I read a street can have zero to many houses, but a house belongs to (well, not zero but) only one street. But a many to many says,  A can have zero to many of B AND B can have zero to many of A, correct? Like a room can be booked by zero to many guest, and a guest can book zero to many rooms. So, in that way, it s that quite the same, correct? Am I on the right track?


Reply from Alex Hedley:

Sounds good to me.
Show Just This Thread        Post Reply
Recurring Error Comment from Jack Tracy @ 7/26/2016
Thanks for this lesson, it is really helpful when it works.  I'm experience problems with it continuing to work, however.  

I've set up this code exactly as described and it works great.  Then I save, close and reopen the next day and attempting to run again I get bug errors.  I redo the entire process in a brand new set of forms, and again works great.  Next day, bug (error 459, pointing to the default input of the "personID" from the underlying form for the reverse relationship), which then leads to a bug in looking up the opposite ID as well.  

I'm not sure what the problem is, but I'm not making any edits to the forms or code in question, it simply seems to just stop working over time and I'm not sure why.

Best,
Jack


Reply from Alex Hedley:

If you compile the code in the VBA editor do you get any errors?
Might be worth a compact and repair.
Have you tried deleting and recreating the form?
Show Just This Thread        Post Reply
DLookUp Comment from Richard W @ 6/19/2016
The information is already in a subform.  Rick said it is possible to put a subform in a subform but is a problem.  Further I was trying to learn DLookup since he talks about its power.  But if I have to use a query and a sub-subform, I guess I need to know where I can learn to do that!  Thanks for the answer.  I am trying to use the DLookup approach since I have, in fact, three groups of people with many to many relationships and I am  trying to standardize the way the information on all of them is stored . . . following Rick's advice that "if you have a second telephone number (or email or whatever) you should put it in a separate table" (paraphrased, not quoted).


Reply from Alex Hedley:

Subform
Tip
Show Just This Thread        Post Reply
DLookUp Comment from Richard Wilson @ 6/16/2016
At 5:11 you talk about using a DLookUp for a specific type of address or phone number.  I need to know how to do that.  I built my original forms based upon tables with (for example) OfficePhone, CellPhone etc as separate fields.  I have now gone back and put all the phone numbers in one table with a TypeT containing a TypeID and such things as Office, Cell, Fax, Home etc.  Same sort of thing for email, address, Title (this is a DB for teachers in a school where one may be on two or more faculties and be Professor in one and Artist in Residence on another).  Anyway, my DLookup for such things as phone numbers no longer works.  I need to specify which is Office and which is Cell, etc.  I have tried to use a Where condition using an "AND" clause where an ID = Combo ID AND TypeID = 1 (I know I have not done the right syntax here) but I get a "mismatch" error in the second part of the AND clause.  I do not understand that since TypeID is a number and "1" certainly is an number.  I do NOT put "" around 1 but Access does.  Long question, I know, but it is one I am wrestling with right now.  Further, since this deals with teachers in schools, I also need to narrow the result to a specific school where the student is enrolled.  That may be beyond the scope of this class, but it is my problem.  Especially since I have gone back and redone my tables to make phone numbers (for the teachers, at least: thinking about doing it for the Students/contestants as well as the people in the town where the competition is held!!; same for addresses, emails, etc).  Anyway, I need to do what you said at 5:11.  Excellent Seminar.  I have listened to it many times.  It takes repeated hearing for me to learn it.  But after two years, I am beginning to grasp some of it!!


Reply from Alex Hedley:

Why not just create a Query that uses the CustomerID to filter on (with the others) and have it as SubForm.
Show Just This Thread        Post Reply
Different prices Comment from Matthew Pattison @ 6/7/2016
Question for example 3 btn mouse $8

the 45 days later the price changes to $12

how do you handle that without messing up the previous orders?
lesson 8


Reply from Alex Hedley:

You'd need to have a price against an item in the OrderT.
I'm sure this is covered, or it is in another course.
You pick a product from a dropdown, and you copy the values from the combo.
productCombo.Column(1) to the Order Field.
This can then be updated if necessary and is independent to the ProductT.

(Access 302)
Show Just This Thread        Post Reply
Grouped Controls Comment from Daniel S @ 6/6/2016
Hi Richard, I really have enjoyed everyone of your courses. Thanks for doing such a good job explaining a difficult subject. I am in the relationship seminar having trouble with moving things around on the form. Such as resizing, and relocating the ID fields. I am in design view but it acts like I'm in Layout. I am using Access 2016.
Thanks


Reply from Alex Hedley:

Form Design
Arrange
Control Layout - Remove

Access groups the controls if you made the form automatically.
Show Just This Thread        Post Reply
Price in Junction Comment from Emad Al-Baghli @ 11/21/2015
Hello, Richard, Why did you put Price in Junction table? Price is related to the product, so why do you desice to put it in Junction table instead of Product table?


Reply from Alex Hedley:

This will allow you to add different prices for the same product.
Show Just This Thread        Post Reply
I m really enjoying this Access course it s been Comment from Fernando Solano @ 7/21/2015
I'm really enjoying this Access course, it's been over 10 years since i worked with Access, and your lessons are very enjoyable to get myself familiar with this!! i'll will be buying more lessons soon!


Reply from Alex Hedley:

Great to hear it :)
Show Just This Thread        Post Reply
Relative Subform Comment from Carolyn Cwik @ 5/27/2015
I understand the Relative Subform and was able to do it to a tee.  However, it would be nice to add a relative "on the fly" but am having problems with trying to do so.  Is it because of the combining of last name and first name.  I have gone over and over the on not in list event but haven't seem to get it to work.  Any help would be appreciated.  Thanks.   Carolyn


Reply from Alex Hedley:

What errors are you getting?
Show Just This Thread        Post Reply
Relationships Comment from Ramona Woitas @ 3/7/2015
Hi Richard! I am trying to get my head around this again. lol.

Relationship Seminar, Lesson 15, at 6:22 you said you have members which is a many to many relationship. I take this to mean that multiple members can belong to multiple organizations.  Right?

Then you said that addresses is a one to many relationship, from either an organization or a member.  This is where I am confused.  A member can have multiple addresses, and an org can have multiple addresses. But the address is only listed in the table once. And the address can only be used by either the member "OR" the organization. Is this right? Do I finally get it?


Reply from Alex Hedley:

Maybe this MS article with the following example may help:

One-to-many relationships

A one-to-many relationship is the most common kind of relationship. In this kind of relationship, a row in table A can have many matching rows in table B. But a row in table B can have only one matching row in table A. For example, the "Publishers" and "Titles" tables have a one-to-many relationship. That is, each publisher produces many titles. But each title comes from only one publisher.

Many-to-many relationships

In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table that is called a junction table. The primary key of the junction table consists of the foreign keys from both table A and table B. For example, the "Authors" table and the "Titles" table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the "TitleAuthors" table.
Show Just This Thread        Post Reply
As with all of the courses I ve taken so far Rich Comment from Frank Fish @ 11/28/2014
As with all of the courses I've taken so far Richard seminar is very helpful in rounding out my knowledge base. My comment/request is: Can you add a feature to your classes that allows for a "bookmark" or place keeper? I sometimes have to interrupt my studies and it might be a day or so before I am able to return, a place keeper would be helpful. Thanks. FWFII


Reply from Alex Hedley:

Frank in the bottom left pane in the Theatre there is a Settings section, this has a Last value which saves your last video viewed.
Show Just This Thread        Post Reply
Show Extra Student Info Comment from Frank Fish @ 10/27/2014
Hi Richard; index 10:55 (approx)although I have 3 records in the StudentF; when I move to StudentID 2 or 3 and then select the "Show Extra Student Info" button I get "#Error" in the StudentID box on the StudentInfoF. (I'm using Access 2013) however; if I place information in the StudentInfoT for the second or third student the information shows up when I press the button. is this just the difference in Access versions or did I miss a step?


Reply from Richard Rost:

This should work regardless of what Access version you have. Have you tried closing and reopening the form?
Show Just This Thread        Post Reply
Relationships Comment from Christophe Smit @ 10/16/2014
I'm trying to set up the relationships for a procycling database on one hand and a soccer table on the other hand; as an excercice. In the procycling DB a rider can switch teams (team x season 2014, team y season 2015). Teams can exsist in season 2014 but not anymore in season 2015.
In soccer DB it is more complex as a player can change teams during the same season. I tried different ways but without result. How should my relationships look like in both DB's?


Reply from Alexander Hedley:

Hi Christophe,
If we convert this to the lessons Rich has taught you could have an Employee and a Company.
The Employee could have a CompanyID that is a Foreign Key to the CompanyT.
You could then have a combo that lists all the Companies and choose one for the Employee.
You could then change this if the Employee was to move Companies.

To show only certain Companies you could use a WHERE clause and filter it given some criteria.
Show Just This Thread        Post Reply
Comment from Julie Martin-Beaulieu @ 7/19/2014
You can disregard my last question.  I just read another student's comments about not using the design form and now I'm back on track.  Thanks!
Show Just This Thread        Post Reply
Comment from Julie Martin-Beaulieu @ 7/19/2014
Hi.  I am dropping the junction table into my "parent form" but it continues to show all of the data.  What might I be doing wrong?  I am following your video step by step so I'm not sure what the problem is.

Thank you for your help.
Show Just This Thread        Post Reply
Comment from Julie Martin-Beaulieu @ 7/18/2014
Hi.  I am making a database to store interventions and student support services for students.  I think I need a one to many table so that I can identify the type of interventions (there are different reading and math programs) each student receives.  Some students will receive multiple interventions.  My question is:  Do I need an intervention ID like you have a parent one?  I wouldn't have a unique intervention for each student. I would imagine identifying either reading or math and then having the intervention type be the "many" part to the relationship.  I'm just not sure if I need a unique ID for that table.  Thanks for your help.
Show Just This Thread        Post Reply
My last post Comment from Ramona W @ 7/15/2014
Can someone please tell me what the relationships should be regarding the tables noted in my original post. I would be extremely grateful!
Show Just This Thread        Post Reply
Please setup that video on how to fix a flat datab Comment from Mark Gray @ 7/15/2014
Please setup that video on how to fix a flat database that would be VERY helpful!


Reply from Richard Rost:

Mark, I covered that in Access Expert 14.
Show Just This Thread        Post Reply
Richard I could not stand Access before this you h Comment from Mark Gray @ 7/12/2014
Richard I could not stand Access before this you have turned my opinion completely opposite I now cannot stay away from it! Thanks, you are a true inspiration!

Mark


Reply from Richard Rost:

Thanks
Show Just This Thread        Post Reply
Comment from Hilton Turner @ 7/11/2014
When making a "Self-Join Many-to-Many junction table, four ID's are created (see video at 4:40 thru 5:06). The junction table "ID", the "PersonID", the "RelativeID" and the "RelativeTypeID". The junction table "ID" identifies elements within the junction table. The "PersonID" identifies elements within the Person table. The "RelativeTypeID" identifies elements within the RelativeType table. The "RelativeID" does not identify elements in a separate table; it identifies elements in the Person table. Therefore, both the "PersonID" and the "RelativeID" identifies elements in the Person table. I can understand How the "PersonID" is related to the Person table - there is a common name, but I do not understand how the "RelativeID" is related to the Person table - the names are not common. What magical SQL dust causes the "RelativeID" to identify elements only in the Person table and not the RelativeType table???
Show Just This Thread        Post Reply
Comment from Ramona Woitas @ 7/6/2014
Ok I give up!  I am wasting my weekends trying to figure this out and I can't seem to make it work.

I have tried various ways to add ref integrity to my database and I got some of it working but then other parts didn't.

I am trying to set up a database just like this lesson with OrgF having Members, address, contact notes, phone number sub forms.  Same idea for MemberF.

I had it working so that if I deleted a member or org the junctionT information deleted as well.  However the address and contact note information was not deleted.

Then I found the video where chris was asking about cascade deletes where 3 people had the same address and tried to incorporate that too.  When I tried to add an org, I got a message come up regarding integrity.

Obviously I am seriously screwing up the relationships in my db and I need to know how to do it right!  I have:
- OrgT (OrgID, AddressID, PhoneID, ContactID)
- OrgXPersonT (ID, OrgID, PersonID)
- PersonT (PersonID, AddressID, ContactID, PhoneID)
- AddressT (AddressID, OrgID, PersonID)
- ContactT (ContactID, OrgID, PersonID)
- PhoneT (PhoneID, OrgID, PersonID)

I tried to do left joins - type 2 for the OrgT, PersonT to the junctionT and it worked.  

But when I tried to follow the video for address, phone, and contact notes, I couldn't add the address etc information to OrgF.  I had left joins for AddressT, PhoneT, ContactT to OrgT and PersonT with joint type 1.

Can you please tell me what my relationships should like and what join type to use for each as well?
Show Just This Thread        Post Reply
Cascade Delete JunctionT Comment from Ramona W @ 6/30/2014
No I didn't.  You didn't do it in the seminar so I thought the junction tables kind of did it for you.

The reason I thought this is because when I went to add ref integrity, there was something already in there that I never put there, and have no idea where it came from.

ie) 3 tables - MSysNavPane
  a-Group Categories
  b-Group
  c-Group to objects

Anyway, apparently I should have added referential integrity.  So would it be:
  OrgT, MemberT, OrgXMemberT

OrgT (left join option 2) to OrgXMemberT - Referential Integrity - Cascade Delete?

AND

MemberT (left join option 2) to OrgXMemberT - Referential Integrity - Cascade Delete?

I wouldn't have to worry about address and contact tables would I?



Reply from Richard Rost:

You would need referential integrity with cascade deletes set up if you want to delete the junction table record when a member record (or an org record) is deleted.

Show Just This Thread        Post Reply
Cascade Delete JunctionT Comment from Ramona W @ 6/29/2014
Hi Rick!  I have another question and unfortunately not as educated as Chris, so I am scared to mess with my DB too much.

With regards to Lesson 14 & 15 of seminar - Org & Members.  I deleted a member from the memberF, which also did the deletion to the memberT.  However on the OrgXMemberSubF (now shows a blank space), the MemberXOrgSubF (still shows the org), and the OrgXMemberT (Still contains all of the data).

I have already tried several things, none to which have worked. I went to add DB relationships for the OrgT, MemberT, and the OrgXMemberT - but apparently have not picked the right choices and I am now really confused.

I also built a DB for my books - AuthorT, BookT, SeriesT, and BookXAuthorT - Unfortunately the same thing happened.  (Of course an AUTHOR can have more than 1 book & create more than 1 series - A BOOK can have more than 1 author & it can belong to more than 1 series - A SERIES can have more than 1 author & has more than 1 book - Which is why I used a JunctionT)

ie) Series A, has Book 1, 2, 3
ie) Series B, has book 4, 5, 6 which are also the same books as in Series A, Book 1, 2, 3.

Can you please help?  Thank you in advance!

P.S.  I really loved this seminar especially lesson 14 & 15.  I love that you can have multiple relationships and these sub forms that shows you the information pertaining to only that person.  I ABSOLUTELY LOVE IT!

IE) Have PersonF (with specific data like DOB) and then sub forms that show the organizations, Other groups, Address, Phone numbers, Supervisors, Contact Notes etc.  Then Have OrgF (with specific website and email) and then sub forms showing the people, Address, Phone numbers, and contact notes!!!



Reply from Richard Rost:

Did you have referential integrity set up?
Show Just This Thread        Post Reply
tables for tracking books Comment from Ramona Woitas @ 6/23/2014
What would be the best way to setup a personal book library.  Would I use junction tables or have tables for author, book, series with relationships

I only want the following information:
Author:
- Name, Alias, Website, Notes

Books:
- Title, Release Date, Finished, Notes
- Novel or Series
- Book # (1 of 1 or 1 of 3)
- Total books in series (3)
- Date Read - Notes


Reply from Richard Rost:

I'd have BookT and AuthorT as the main tables.

You wouldn't need a junction table for these two UNLESS you want to be able to put multiple authors (many-to-many) on a single book (which does happen). For just one author, you'd need an AuthorID field in your BookT (one-to-many).

SeriesT could be another table and you'd need a SeriesID in your BookT table. Here you wouldn't need a junction table unless a book can be part of multiple series - which I don't think ever happens.

Total books in series would be a COUNT() or DCOUNT()

Date read? If you want to track when YOU read a particular book, you could just put this in your book table. If you want to track multiple people reading it, you'd need a UserT (or ReaderT) and then - you guessed it - a junction table.

Show Just This Thread        Post Reply
Access Database relationship Comment from Falakiko Funaki @ 6/3/2014
Hi Richard, I enjoy watching your videos and have learned a lot. I am studying at the Auckland University of Technology in New Zealand. I have a project to build an access database with some tables Staff, papers they teach, software packages they use for teaching and also access device for the whether if they login from home or from the University. They are four tables and they all many to many relationship. I have a problem relating them to each other. Can you help me out please? It would be very much appreciated. Thank you


Reply from Richard Rost:

It's impossible for me to tell you what's wrong with your database without seeing it. I cover this stuff in depth in my RELATIONSHIP SEMINAR, or you can send it to one of my TECHS to look at.
Show Just This Thread        Post Reply
Access Relationship Seminar Comment from Jim U @ 5/14/2014
I have been reading the threads I cannot see anyone with the same difficulty I keep ending up with an indeterminate relationship. I am not sure how or why?
Show Just This Thread        Post Reply
Access Relationship Seminar Comment from Wilma W @ 5/3/2014
I am thinking that with this way of adding relatives, that if a family has three kids the phone number and whatever other data I choose to record for the parent table would have to be reentered for each child. Am I thinking correctly?
Show Just This Thread        Post Reply
Access Relationship Seminar Comment from Caryn mellom @ 4/20/2014
I was able to figure it out.  For some reason the forms were pointing to the wrong tables.
Show Just This Thread        Post Reply
Access Relationship Seminar Comment from Caryn M @ 4/13/2014
Richard,
I have followed your steps in video 11 to create a many to many relationship for a genealogy database.  When I open the first individual, I can add relations for that person.  When I move to the next individual, the relations don't update for the new person.  If I look in my junction table, the individualID is 0 but the relativeID is populated.  I've gone through the steps multiple times and I'm not sure why it keeps doing this.  Do you have any ideas?  Thanks
Caryn


Reply from Richard Rost:

I'd really have to see your database to tell you what's wrong, but if your ID is showing up as 0 that means it's not getting the default value properly.
Show Just This Thread        Post Reply
can you later adjust the length Comment from vannak @ 1/28/2014
04:23-04:31.  can you later adjust the length of the column at a later time once in the Form Menu?
Show Just This Thread        Post Reply
Comment from Vannak Hou @ 1/26/2014
Is there a way to go back to the Combo Box to "resize" the field such as the LastName or the FirstName?  Lets say I made the LastName's field size a bit too small after I input data.
Show Just This Thread        Post Reply
Comment from Vannak Hou @ 1/25/2014
Lol, Richard (timer 0710-0712), funny, I remembered back those days, then when Memory was too costly.  I think that time Toshiba or other company in Japan was ruin due to an earth quake.  Yep!
Show Just This Thread        Post Reply
Comment from Vannak Hou @ 1/25/2014
Why can't I use an alisa ParentName:[LastName]&","&[FirstName] for the ParentT (get rid of the FirstName, LastName of ParentT's Column)?  I tried but I'm getting a message "The specified '[FirstName]' could refere to more than one table listed in the FROM clause of your SQL statement; even though I selected StudentT in the Table.  All I am trying to do is Concatenate the fields together as one. This on 03:54 on the video timer.  My database built a bit different using City, State, ZipCode as a separate field.  But it works!
Show Just This Thread        Post Reply
What tables to create Comment from Julie Martin-Beaulieu @ 9/22/2013
Hi Richard,

First of all, I need to tell you that I find these seminars and your tutorials to be very helpful.  I am understanding the different components and purposes for creating a database in a way that makes sense to me.  So thank you.

Regarding relationships and when to use them; I am creating a database to store information on my students.  I think I want one database with three different tables.  One for students who are in the "early intervention" process, those who are receiving "interventions" and those who are identified as "special education".  Within each table I am documenting meetings, student progress, and history of support services provided.  Would this be one database with all of this information stored in it, or should I actually build three databases?  I want to be able to see which students went through our early intervention process, who ended up qualifying for Special Ed and what interventions they went through.  So I think it would be all in one.  Is that right?

Thanks,
Julie


Reply from Richard Rost:

I like to keep all of one THING in the same table. So you should have a STUDENTS table (StudentT). In that table, you can differentiate between "early intervention," "intervention," and "special ed" with different classifications (a single field). No need to put these students in three different tables.

Meetings, progress, history of support services provided... these all go in different tables... just like how I break out CONTACT HISTORY from CUSTOMERS in my Access Expert series.

You don't need three different databases. The only way you'd need to make separate database files is you're reaching the 2 GB limit on database size (which I doubt). You may also want different databases if different USERS need to have access to the different student info and you don't want to go through setting up security in the database itself. You could make 3 different database files with different passwords.

Show Just This Thread        Post Reply
student state ID Comment from Julie Martin-Beaulieu @ 9/22/2013
I work for a school and all of my student records are associated with their student state ID.  It seems to me that I would want to have the releationahip between tables exist with that ID not the key ID.  Does that sound reasonable?  Or will I create prolems by not using the primary key/auto number?


Reply from Richard Rost:

As I've said in my videos many times, I would still use an Autonumber ID for the relationships in the database. If you want to have that Student State ID saved in the student's record, that's perfectly fine too. You can search or sort on it whenever you want. Autonumbers are just much better and easier for relationships. Nothing wrong with having two fields.
Show Just This Thread        Post Reply
Form Subform Comment from Ronald Pero @ 9/5/2013
Please disregard my questions from earlier today.  I realized that the first problems I had was that I had accidentally deleted the customerid field on my subform.  My second question was wondering why the customerid
field was not saving in the parent form and thus the appointment table but I now can understand that I was trying to make the form/subform relationship work backwards which apparently does not work.  

I do still need to figure out how to make this work.  So what I need to have happen is to be able to enter the
customer data into the subform and then somehow update the customerid field back in paren form.  Based on the reverse
relationship video it looks like I can use the doCmd.RunSQL "Insert Into" command to insert this field back into the
appointment table.  Is this possible?  and if so, how can I choose a specific record in which to insert the field value? In other words is there any kind of where condition that can be specified when using  the Insert Into command?


Reply from Richard Rost:

You really can't do this backwards. You could develop a different form where you can enter in both sets of data and then use some code to populate both, but you can't enter data into a subform until the parent has data.
Show Just This Thread        Post Reply
Form Subform Comment from Ronald Pero @ 9/5/2013
I have created a subform where I will enter customer name & info as part of a main appointment form. My customer table seems to work fine if I manually create a new customer but when I try to create a new customer by entering data into the fields in the subform first I get the error message: You tried to assign the Null value to a variable that is not a variant data type. which seems to be related to the autonumber field in the customer table and then no matter what I do the form wants to move on to the
next new customer and nothing gets saved in the customerID field in the appointment table which  contains
the appointments. When I open the customer table the new customer is there and the autonumber field works fine but I can't figure out why it will not enter the customerid into the appointment table.  Do you have any suggestions?


Reply from Richard Rost:

You have to have data in the parent form before you can add any data to the subform.
Show Just This Thread        Post Reply
Report Questions Comment from Martie Jacobs @ 9/5/2013
Reports:
1. I have added a field which displays in the print view of the report only if there is a value in that field - which is exactly what I want, but I don't know how this happened and therefore cannot do the same with the other fields.
2. Format: Make whole word Capitals - cannot find this setting.
Plse help!
Many thanks. Martie


Reply from Richard Rost:

1. I'd need to see your report to tell you for sure. Look for some programming in the BUILD EVENT for the SECTION. Something like:

If IsNull(MyField) Then MyField.Visible = FALSE

2. UCASE(MyField)


Show Just This Thread        Post Reply
Deletions of related tables Comment from William B @ 9/3/2013
I tried to delete a record (after using the self join many to many and reverse lookup relationships), and I get an error saying the record cannot be deleted because it is related to one or more tables.  Is there a way to do this without the user having access to the person with relationships tables?

Thanks a lot.  Really have learned a lot from the seminars.

William


Reply from Richard Rost:

If you have referential integrity enforced then you can't delete that record if it has related records. This is a FEATURE of referential integrity. You can either turn it off or force the user to delete the related records first.
Show Just This Thread        Post Reply
Reverse Relationships Comment from William B @ 8/28/2013
Can you do reverse relationships in a form and subform, as you have done in this seminar?  I was just wondering if it worked the same way, or if there would be some different SQL or VBA required for it to run correctly when that specific form is open?

Love the seminars by the way!


Reply from Richard Rost:

I don't see why not. It may be tricky though. A reverse relationship is really just a fancy one-to-one relationship, so you wouldn't need a subform.
Show Just This Thread        Post Reply
Orders for reselling Comment from Jim Upton @ 8/1/2013
Thank you for your reply. I been having trouble getting my head around a relationship problem. In replying and explaining I have cleared up my dilemma.

Did you know if your in a table and you want to repeat the same data as the line above Ctrl key + @key repeats it for you Many Thanks

Jim Upton


Reply from Richard Rost:

I did not know that. Thank you for sharing. There are a MILLION shortcut keys in the Office apps, and to try and memorize them all, one would go insane. :)
Show Just This Thread        Post Reply
Orders for reselling Comment from Jim Upton @ 7/30/2013
Hi Richard

I have enjoy this seminar. I understand the one to many relationship. On the products and vendors many to many if you then want to add orders for reselling would this go in the Junction Table as well?


Reply from Richard Rost:

What do you mean by "orders for reselling?"

You would but information in the junction table specifically related to THIS PRODUCT FOR THIS VENDOR.
Show Just This Thread        Post Reply
Change SubDataSheet Comment from Adam Wray @ 7/16/2013
Wondering how to change between subdata sheets that are shown when you click 'plus' if you have more than one one-to-many relationship.  Also, I deleted one relationship, but still can't access the one I'm looking for.


Reply from Richard Rost:

Go into design mode, table properties, subdatasheet name.
Show Just This Thread        Post Reply
Customers Addresses Jobs Comment from Mark @ 5/7/2013
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.
Show Just This Thread        Post Reply
Customers Addresses Jobs Comment from Mark Bee @ 5/4/2013
I am having problems with understanding how to have one address table for my customers, that relate to the customers jobs, their invoices and quotes etc... I can have 1 customers with many jobs related to one address, then 1 customer with many address and many jobs. I've been unable this far to work out how to get this to work with out running into duplicating the same address in the address table.

I've tried to make work a junction table to work between the customers, jobs, address, quotes and invoices but end up confused. Even going through this relationship seminar twice I'm unable to see how the examples relate to what I'm trying to achieve.

Can anyone help?


Reply from Richard Rost:

I would have 3 tables: customers, addresses, jobs.

AddressT has CustomerID as a foreign key
JobT also has CustomerID as a foreign key

Now, assuming a JOB will have ONE AND ONLY ONE address, you could just have one AddressID field as a foreign key field in your JobT table.

If a job can have MULTIPLE addresses, then you need to set up a FOURTH table as a junction table which would look like:

AddressesForJobsT
ID: AutoNumber
JobID: Foreign Key to JobT
AddressID: Foreign Key to AddressT
Details...
Show Just This Thread        Post Reply
your the Best Comment from David Robinson @ 5/3/2013
Richard
All I can say your the Best, your courses and the best I have ever seen, easy to follow and you explain everything, I just need more time to get to all the courses I havew purchased and I will be purchasing more.


Thank you very much


Reply from Richard Rost:

Thanks, David. :)
Show Just This Thread        Post Reply
Parent Form Comment from PETER @ 4/23/2013
Please see my last comment - I just realized that rather than have Access build the parent form, I had built it myself using Form Design (as suggested by you in many other video's). When I let Access build the parent form for me, the subform behaved as expected - only showing subform values relevant to the item shown on the parent. What would I have done wrong in building the parent form from scratch compared to what Access does itself?


Reply from Richard Rost:

You can build the parent form from scratch. Just make sure you set up the link fields properties. Access SHOULD do that for you if your fields are named correctly (the same) on each of the forms.
Show Just This Thread        Post Reply
subform filtering Comment from PETER @ 4/23/2013
At minute 2:30 on the video the subform automatically shows only subform contents that match the VendorID on the parent form. That filtering doesn't seem to be occurring when I try to do it on one of my own subforms ... Is there something I missed on the video?


Reply from Richard Rost:

Access should have automatically set up a relationship between your main form and sub form. It's the LINK MASTER FIELD and LINK CHILD FIELD properties in the subform.
Show Just This Thread        Post Reply
I set up a table with a unique pt number which was Comment from Anonymous @ 2/26/2013
I set up a table with a unique pt number which was indexed as no duplicates and made it the Primary key. I used the unique number in  a 2nd form and made it the primary key, no duplicates, and was told I had broken the integrity rules as this duplicated values?? Relationships lesson 7 time 0259


Reply from Richard Rost:

Don't make it a primary key in your second table.
Show Just This Thread        Post Reply
Comment from Jennifer @ 2/25/2013
I have asked this somewhere else:   what if you have a lot of info about a single student / patient. Eg a unique pt ID but with up to 15 differing tables containing results or clinical findings. Each of these 15 is I think?  a 1 to 1 relationships, but there are 15 of them??? What do you call this and how does it  work.
Show Just This Thread        Post Reply
Multiple copies Comment from Roderick Barbour @ 12/2/2012
I have been questioning the concept that Richard has of making so many copies of each database as we went through the lessons, until I just hit a key or a combination of keys and just lost my whole database. Im an old IT guy and there was always a way of getting a file or document back. But with Access, once its gone it is really gone. WOW!! Great lesson.


Reply from Richard Rost:

There is a method to my madness. :)
Show Just This Thread        Post Reply
Combo Box Populate Another Field Comment from Tom @ 10/12/2012
Richard- I would like to use my choice in a combo box to populate another field without user intervention.  The combo box pulls from a table that contains catalog items. Each individual item also contains a number that corresponds with a product type (stored in a product type table)  Any thoughts?


Reply from Richard Rost:

Use an AfterUpdate event?
Show Just This Thread        Post Reply
Access Addiction Comment from   @ 7/17/2012
Brilliant AGAIN, Rick.
Couldn't honestly say anything was unknown to me but the real value in these seminars is that you show how to bring it all together.
Actually, I just realised that the one-to-one was a new idea from the point of view of security.
Well done, Rick, keep 'em coming to feed my Access adiction :o)
Regards
Chris


Reply from Richard Rost:

Ha ha. Thanks, Chris. I'm working on more crack for you right now. :)
Show Just This Thread        Post Reply
Access Relationship Seminar Comment from Kevin Robertson @ 7/16/2012
Hi Richard,

Completely loved this seminar - learned lots I didn't know.  Keep up the good work!


Reply from Richard Rost:

Thanks, Kevin.
Show Just This Thread        Post Reply
Comment from  Bruce @ 7/16/2012
Video 13 covers a lot of ground. I am grateful that I have had the opportunity of viewing this critical seminar.


Reply from Richard Rost:

Glad you're enjoying it.
Show Just This Thread        Post Reply
Cascade Delete Comment from Richard R @ 7/12/2012
Chris that's a very good question. See this tip video for my reply. :)
Show Just This Thread        Post Reply
Cascade Delete Comment from Chris @ 7/11/2012
My earlier question was asking: When does the data in a cascading table get deleted when there are several other records pointing to the same cascading record?

As an example - Three people (three records) in PersonT each refer to the same address data located within table AddressT. There exists a global relationship between these two tables using Foreign Key AddressID in the PersonT table and the Primary Key (AddressID) in the AddressT table. In each of the PersonT records, they each refer to the same address record with AddressT.

Will the AddressT record remain in the AddressT table until all three records have been deleted from PersonT?



(as a side note - yes I could try this; but I was asking so that others could also get an answer to this question)
Show Just This Thread        Post Reply
Cascade Delete Comment from  Chris @ 7/11/2012
Not to belabour this and just to ensure I understand. On the cascade deletes, if another record uses the same cascaded data, the common records are not deleted. It is only when there is one remaining record that uses this cascaded record that it is removed from the database. Is this correct? (ref: 15:00)


Reply from Richard Rost:

Cascade deletes come in handy if you delete a PARENT and want all of its CHILD records to be automatically deleted. I don't know what you mean about "common records." Can you explain with an example?
Show Just This Thread        Post Reply
enjoyed the relationship seminar Comment from Jim Upton @ 7/6/2012
Hi Richard

Readlly enjoy this relationship seminar. I will need to watch it again but thats me at being slow getting my head around it.

I would be interested in expantion on any of those comments that you made and adding payments into this and making reports as a address book


Reply from Richard Rost:

Thanks, Jim.
Show Just This Thread        Post Reply
Comment from will powers @ 6/29/2012
is there a way besides going into the relative typeT table like in the video to make the opposite ID?  I want to make a database for our parade in town.  the idea would be to fill up a float with people and show floats that aren't full yet and floats that are full. then use the reverse to show what people went on what float in the past.  

- Will


Reply from Richard Rost:

Well, the floats that are full/not full would just be a simple DCOUNT (or a form footer total) of the number of child records.

Making a query to show who was on which float in the past should be a simple matter too.
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
 
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
 
  Your Name:  Required
  Your Email:  NOT Public
  Subject:
  Comments:

 

Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 7+3:
  
  Notify me when the News is updated.
  Remember Me for my next comments
  
 
 
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard

 

 

You may want to read these articles from the 599CD News:

3/23/2018Microsoft Access Developer 7
2/22/2018Microsoft Access Developer 6
2/22/2018Access Assistance Available
2/21/2018Putting Access Databases Online
2/18/2018Microsoft Access Developer 5
2/10/2018Microsoft Access Developer 4
2/1/2018Access Tip: Salary History
7/19/2017Access Developer 2 and 3 Released
7/19/2017Microsoft Access Developer 3
7/19/2017Microsoft Access Developer 2
 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP