ERROR #3704: Operation is not allowed when the object is closed. Microsoft Access 2010 Expert Level 1 Tutorial Relational Database Concepts, Foreign Keys, Query Joins, Combo Boxes
 

4/24/2017: You may see an "operation not allowed" error on the site. We're working on fixing the problem. Nothing seems to be affected, it's just annoying. Carry on. :)   [dismiss]
 
Access 2007-2013
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  
 
NEW Release: Access Advanced Level 4 and Level 5   dismiss
 
 

< Previous: Access Beginner 9

Next: Access Expert 2 >

Access Expert Level 1

Expert Microsoft Access Tutorial - 1 Hour, 18 Minutes
 
 
This Microsoft Access video tutorial picks up where Beginner Level 9 left off. This class covers fixing the contact history table that we started in the last class. I intentionally showed you the wrong way to build it last time. Now we'll build it correctly. You will learn:
 
  - Relational Database Concepts
  - Primary & Foreign Key Fields
  - Types of Relationships
  - Focus on One-to-Many Relationships
  - Ad Hoc Query Joins
  - Left Joins to Show All Records
  - Relational Combo Boxes
  - Fixing Lead Source & Shipping Combos
  - Select a Customer on the Contact Form

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.




 

Access 2010 Expert Level 1
Description: Access 2010 Expert Level 1
Versions: Recorded with Microsoft Access 2010
Works fine with Access 2013 and 2007
Pre-Requisites: Access 2010 Beginner Level 9
Running Time: 1 Hour, 18 Minutes
Cost: $19.99


This class picks up where Beginner Level 9 left off. In B9 I intentionally showed you the wrong way to build the contact table using a flat file database system, where all of the customer and contact information was stored in one table. In this class, we'll fix that problem by building a properly relational set of tables. We'll begin by learning about relational databases, what they are, and why they're important. We'll learn about primary and foreign key fields. We'll learn about the different types of database relationships: one-to-one, one-to-many, and many-to-many.

 

Next we'll set up our tables with the right relational fields. We'll add a CustomerID to our contact table, learn more about Foreign Keys, and fix the Lead Sources and Shipping Method tables.

 

Next we'll learn how to join tables together using Ad Hoc Query Relationships on the fly. We'll see how Access creates automatic join lines, how to delete a relationship, and how to create one manually if Access doesn't do it for us. We'll learn about different join types, including a LEFT JOIN which is handy to show all of the records from one of the related tables even if matching records don't exist in the other tables.

 

Finally we'll create relational combo boxes. This will allow us to pick a value from one table and store it in another. For example, we can pick a customer from our customer table and store that ID in our contact table - to set which customer the contact belongs to. We'll do something similar with our Lead Source and Shipping Method tables. You'll see how the combo box wizard behaves slightly different if you're using a query vs. a table.

 

This is the first class in the Access Expert series. If you want to take your database skills to the next level, this is the class for you. Understanding proper relational database construction is absolutely vital to the success of your projects. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access 2010 Expert Level 1

00. Intro (7:13)

01. Relational Database Concepts (17:06)
What is a Relational Database
Why are Relationships Important
Flat File Databases
Key Fields
Primary Key
Foreign Key
Types of Relationships
One-to-one Relationships
One-to-many Relationships
Many-to-one Relationships
Many-to-many Relationships
Junction Table
Normalization

02. Relational Tables (14:53)
Making our contact management relational
Inserting a CustomerID in ContactT
Removing duplicated fields
Setup a Foreign Key
Moving fields in table design
Fixing Lead Sources
Fixing Shipping Methods
03. Relational Queries (14:04)
Add CustomerT to ContactQ
Ad Hoc Query Relationships
Automatic Join Line
Delete Relationship
Manually Create Relationship
Asterisk from One Table
Join Missing Customers
Join Types
Left Join / Outer Join
Joined fields from both tables are equal
Show All Records from CustomerT
Only those records from LeadSourceT
Where the joined fields are equal
Fix ContactR Report

04. Relational Combo Boxes (19:34)
Add Combo Box to Contact Form
Combo Box Based on Table
Combo Box Based on Query
Wizard is Slightly Different
Fix Lead Source Combo
Fix Shipping Combo

05. Review (04:50)

 


 
Keywords: Relational Database Concepts, Foreign Keys, Query Joins, Combo Boxes, microsoft access tutorial, microsoft access 2010 tutorial, microsoft access 2010 training, flat file, key fields, primary key, one-to-one, one-to-many, many-to-one, many-to-many, relationships, junction table, normalization, ad hoc, automatic join lines, join types, left join, outer join, show all records, where the joined fields are equal, combo box, wizard
 
 

Student Interaction: Access 2010 Expert 1

Richard on 2/5/2013:  This is the first Microsoft Access video tutorial in the Expert series. It picks up where Beginner Level 9 left off. This class covers fixing the contact history table that we started in the last class. I intentionally showed you the wrong way to build it last time. Now we'll build it correctly. You will learn: - Relational Database Concepts - Primary & Foreign Key Fields - Types of Relationships - Focus on One-to-Many Relationships - Ad Hoc Query Joins - Left Joins to Show All Records - Relational Combo Boxes - Fixing Lead Source & Shipping Combos - Select a Customer on the Contact Form Click here for more information on Access 2010 Expert Level 1, including a course outline, sample videos, and more.
sam on 2/5/2013: i love it very much
werner wallace on 2/5/2013: I RUN ACCESS 02 ON OSXP AND CAN'T USE YOUR ACESS201. ANY HELP WOULD BE MUCH APPRECIATED.
WW

Munesh on 2/6/2013: Thank you for another excellent tutorial! I am very much looking forward to the upcoming videos.

When using the record selector, ContactF worked perfect for me. I was a bit concerned at 7:35 where you selected Barbara from the dropdown and CustomerID 10 appeared on the form with ContactID 33 and its related note. I saw this again at 13:55 when you selected Anna Picore CustomerID 5 who then appears with ContactID 32 which was previously assigned to CustomerID 8.

It made sense to me after you stated that the Contact Form will be a subform. Am I right to assume that ContactF will be a data-entry form where ContactID will be populated only after the selection of the customer?

Mitch Easton on 2/7/2013: Way to go Richard on the maiden voyage of the Expert series! Excellent job (no surprise there). Having worked my way through the Beginner series and a couple of Seminars, you are wonderfully consistent with your teaching style. You're a pleasure to listen to, and learn from. The nicest part is the actual practical usefulness of the knowledge you share. All I can say is Thank You, and I'm looking forward to your next lesson. Take care.

Reply from Richard Rost:

Thanks! I'm on the road this week but as soon as I back to the office I'll get Level 2 finished.

yifan zhang on 2/19/2013: Thanks, i learned a little more.
i am thinking another question. If there are many tables i need to fill the value in their fields. Can i make only one form which includes all fields in all my tables, then when i fill the fields of the form the values will be stored into the fields in multiple tables correspondingly?

Reply from Richard Rost:

Each form should be based on a single table or query. YES it's possible to reuse forms by changing some of their properties, but that's a lot more advanced. I cover it in my Developer (300+) classes.

Anonymous on 2/26/2013: I have a database with a primary key that identifies the patient uniquely. This number is used in 15 other tables with extensive data about the SAME patient. Each of these tables is I think a one to one relationship, but what do you call it when their are extensive tables with details about the same patient? Does the primary key act as a foreign key. When I enterred the same number again as a primary key I was told I had enterred duplicate information despite the fact that it was in a different table.

Reply from Richard Rost:

The PRIMARY key would in be whichever table you have your Autonumber set. If you're not using an Autonumber (which I don't recommend) then you could, in theory, have multiple primary keys... which may complicate your database.

Martin Olbrycht on 2/26/2013: Hi there,
Is it possible to set up a multi table-multi form rellationship?
What I am trying to achieve, is to set up a system, where I would have employee details/training records/drivers licence records/etc.
I would need all of those to be displayed on the different forms (to avoid clatter), idealy in the subsequent tabs.
So in other words, as a final result I would like to have a tab, where I can simply click between training records, drivers licences, etc. All of it would be based on number of tables, which would be connected in the background.
Is this something access is capable to build?

Reply from Richard Rost:

Yes. What you want are SUBFORMS, not tabs. I will be covering this in Access Expert 2. If you need to learn it NOW then pick up Access 202.

on 2/27/2013: Thank you so much. Really great lectures.
Luli P on 3/6/2013: Me too! Neither the player nor the manual download run on my mac. Thanks for another great class, though!!!

Reply from Richard Rost:

Not sure what the problem is. I don't OFFICIALLY support Macs as I don't have ANY Apple products myself. However I'll refer this to my resident Mac expert: Alex. Any ideas?

Luli P on 3/6/2013: When you created the CustomerID combo box, ran it and played around changing the values on the combo box to show how the values on the ID number changed too....shouldn't the Notes have changed as well with each new value selected, given there was already a relationship created on the ContactT? thanks!!!

Reply from Richard Rost:

Nope. The CustomerID combo box and text box are both bound to the same field: the CustomerID field on the Contact form. The Notes are their own separate field and are not controlled by the combo box. You're just changing WHO this contact record is for.

Luli on 3/6/2013: Got it. Thanks!
Alex Hedley on 3/6/2013: Which player won't work?

The Amicron Player won't work as it is an .exe which doesn't run on Macs as it's Windows based.
You can install a program called Wine which will allow you too though.[http://www.winehq.org/]

Have you used the Walkthrus:
http://www.599cd.com/help/walkthru/ManualDownload/

http://www.599cd.com/help/walkthru/other/iPad.asp

Won't Work:
Download One Self-Extracting File

Should work:
Download Just the Video Files Manually

Make sure it is the MP4 and not WMV, unless you have Flip4Mac http://www.telestream.net/flip4mac/overview.htm

Now with the files downloaded you can play them in QuickTime etc.

(The Online Theatre should work fine.)

Alex

Liz on 3/7/2013: Not taken the Expert 1 class yet but wanted to agree with Mitch Easton here...Who better to learn from, Rich has the patience that so many teachers miss. Thank you Rich from all who will make as many of your classes as possible.

Reply from Richard Rost:

Ah... golly gee... you guys are swell. :)

Shiva on 4/13/2013: Thank You! Very Awesome Videos!!
John Hughes on 4/14/2013: With reference to the person complaining about "it" not running on his Mac, note that Access is a Windows program. (By the way, I have a MacBook and am running both "Access 2010" and "Excel 2010" on Windows 7 via VMware-Fusion. It has generally been problem-free.)
Martin Olson on 4/25/2013: How can I get a Memo filed in the combo box?

Reply from Richard Rost:

You want to put data stored in a memo field into a combo box? You can't. You'll have to convert to a text field (short text) first. You could do it with a query, and just grab the LEFT(X,255) characters and then use that to fill the combo box.


David Thomas on 4/27/2013: Thanks for your help and guidance. I have set up database for 700 member POA. I am now working on the golf course details. In doing so I am prepairng a golf handicap system. All numbers are in place. Handicap is based on the best 10 of the last 20 scores. I have all that data established in database. My problem is I must determine what the last 20 scores of each individual. So, I must do this by date and I can find no function to return the last 20 dates. Please help. When I solve this problem I can use the Top values to determine the best 10 scores. Thanks again for your great tutorials and especially your teaching methods. Dave Thomas

Reply from Richard Rost:

You can also use the TOP feature to find the last 20 scores. Just create one query where you find the TOP 20 values based on DATE. That will be the most recent 20 scores. Then feed those results into ANOTHER query where you use the TOP value on the SCORE instead.

Krishnan S on 5/1/2013: Great lecture. I was able to understand better. Great job. Richard

Reply from Richard Rost:

Thanks.

Dory Michell on 6/10/2013: When creating a combo box. the 3rd option which displays a list of values to choose from does not appear..
Specifically.."Find a record on my form based on the value I selected in my combo box"..

I have ACCESS 2007.
Any suggestions to resolve this.

thanks.

Reply from Richard Rost:

Make sure your form is based on a TABLE OR QUERY and not an SQL statement (check the Record Source property).

Laurie J on 6/10/2013: Just to clarify, I had the same question. If Anna had a note in the notes field, why isn't her information showing when you click on her name? I would not like getting Joe's comments mistakenly "linked" with Anna's record.

Reply from Richard Rost:

You're only selecting the customer ID for the current record. You're not changing any other fields. Notes are not affected by changing the customer ID.

Thomas Witchek on 6/25/2013: Richard,
In your examples for shipping and lead source you add an autonumber ID field to be used as a key field. Why not just leave the one field in the table and use that field as the linked field. It is already unique. The up side of this is that you will know the real value of the field no matter which table you are looking at instead of seeing a number that means nothing until you create the relationship.

Reply from Richard Rost:

Are you suggesting removing the LeadSourceID, for example? Then you're left with just a text value. You don't want to form relationships on a text value. If you want to change, say "Word of Mouth" to say "Referral" later, then you have to change it in all of the related tables too (which, yes, you can do with a Cascade Update, but it's just considered bad database practice).

Tom Witchek on 6/28/2013: I have used text values in key fields for years without any issues. Seeing the value rather than a number is very helpful at times. Can you be more specific why it is bad database practice. Thanks for taking the time to answer my questions.

Reply from Richard Rost:

If you have a current text ID field and it's working for you, that's fine. No NEED to change it. It's just considered good database practice to use Autonumbers for IDs because the system generates them and you don't have to worry about what they are. The primary/foreign key fields are literally ONLY for forming relationships. If you want to add a second "code" or "number" field that you manage yourself, that's perfectly fine. If you want to add text codes for shorthand so you can quickly enter in products, that's OK too.

jay collins on 6/30/2013: where do I find the db being used in this seminar. I have been to your web site without success

Jay Collins

Reply from Richard Rost:

You can find them on the Student Databases page. If you search the web site for "student" or "sample" or "databases" or look in the links at the bottom of the page (under the "customers" heading), you'll see it there.

SYED KHURRAM on 7/10/2013: can we connect a cash register till with a access form? I mean if we print the form can a till open at the same time?

Reply from Richard Rost:

I haven't worked with POS equipment in a long time, but the manufacturer of the hardware will generally provide software drivers to utilize their equipment. You just have to interface with it. One cash drawer that I worked with several years ago had a USB interface and an ActiveX control you could use in your VBA apps or Access to open/close the drawer.


on 7/15/2013: Thanks Richard.
Syed

Juan on 8/5/2013: I was thinking the same as Thomas, and is not remove the LeadsourceID but to use the text value as indexed no duplicates, anyway there is not going to be another UPS, there is going to be only one so leave that field as text, is there any problem doing it this way? thank you for your valuable help, this is a great course.

Reply from Richard Rost:

Again, it's just considered good database design to use Autonumbers for IDs in your tables. Do you HAVE to? Nope. You can use text fields if you want. Autonumbers generally tend to cause fewer errors and save space in databases in the long run.

Robert Garner on 9/5/2013: What needs to be done for the notes on the contact form? Since the customer id is actually being changed in the contact table with the combo box the note field is linked to the new customer but is the note for the original customer.

Reply from Richard Rost:

Robert, I don't understand the question. The notes on the contact form are bound to the CONTACT record in the contact table. It's not the same as the note field on the CUSTOMER form.


Brian Merrick on 9/28/2013: I am having a problem. I have created a VehicleT with the vehicleID and Vehicle Number of the vehicle. I have a activityT with the combo box for the vehicle number, but when i try to create the combo box, it doesn't give me the VehicleID from the VehicleT. What am I doing wrong?

Reply from Richard Rost:

Brian, without seeing your database, it's impossible for me to tell you what's wrong. What do you mean by "it doesn't give me the VehicleID?" Are you saying it's not showing up in the Combo Box Wizard? I need more details from you.

Brian on 9/29/2013: The vehicleID does not show up in the wizard combo box. the vehicleId is in the vehicle table, but I am using the transaction table as my record source in my form. The vehicleID is not in the transaction table.

Reply from Richard Rost:

Brian, I don't understand what the problem is from your description. Since you're not a student of mine, I don't know how much Access you know, but I cover creating relational combo boxes like this in my Access Expert 1 class. It sounds like you just aren't putting the pieces together properly, and I really can't explain it all here.

Felix Amoyaw on 10/23/2013: I am creating a supplier database to gather insight into my company's spending volume on various component parts from hundreds of suppliers. Each item part is assigned a commodity category (about 25 categories) such as plastics, oils and packaging. There are over 1000 parts. In setting up my tables should I include commodity category in the items parts table or create a separate table. Thanks. FA
William Allan on 12/13/2013: A problem:
I currently have a single contacts database for membership it contains about 2400 records. But now management would like to track a history of dues paid by membership. I made another table to record requested information. The problem is how to relate the new table to the old one. If used together no records show up.

Would doing the following solve my problem?
Can I copy my single table MEMBERS and rename it to DUES.
Taking the DUES table, can I delete all fields but the notes field
Add desired new fields
Add a new DuesID
Make it the primary key
Take the MemberID change the data type to NUMBER make it a foreign key
Then relate the two tables in the RELATIONSHIPS module
Will the tables work together?

Christian Crosson on 2/3/2014: two issues #1 I created a query and placed it inside of another query and ran it, it worked fine, but when I added in some other tables the data duplicated over and over in datasheet view
MICHAEL J on 2/3/2014: I have created a combo box taking the data from a table. The table is sorted Alphabetically,how do I sort the combo box alphabetically?
Sonia F on 2/10/2014: I seem to be having the same problem as Robert Garner (9/5/2013), but I didn't understand the answer. When I choose a customer using the combo box in the Contact Form(Access Expert 1-4; 19:34)the notes don't change with the customer. I notice they don't in your video either. But if I go down to the records at the bottom of the page and change the customer from there, the notes come up correctly for each customer. If I choose a customer from the combo box, the last message in the memo box is still there; I have to delete it before starting to put new notes in.
Stefano on 2/14/2014: This is my first time using Access. I have been following your videos and have been learning a lot but I need some personal advice.

I want to build a database for a survey that contains about 110 questions (some multiple-choice, some open-ended and some references to websites). About 350 people have responded to the survey and it is now my job to organize it in a database. The survey is devided into 7 sections, each section containing several subsections. The database that I will be creating for this survey will be for my eyes only. No one else will have access to the survey.

How would you organize a survey like this in Access? How would you organize your tables and how would you organize the relations between the tables? Will I need to create forms and queries?

Please help

Ravi C on 3/8/2014: This is really very useful for me.

Thanks,

Jeff Robson on 3/28/2014: Very Nice
Andr? Silva on 5/11/2014: What's the difference of doing this with a combo box or using the lookup & relationship option on the field types?

Reply from Richard Rost:

If you're referring to the Lookup table field, I addressed those in Beginner 8 and I'll talk a lot more about them again in Expert 7. They're not good to use for several reasons, mostly because they DE-normalize your database. They're a "crutch" that the Access development team through in there so that novice users could still have lookups without REALLY learning how to create multi-table lookups. When you get to VBA programming they become a nightmare. Again, I explain ALL of the details in Access Expert 7.

philip Ogden on 6/1/2014: Just a questions about the drop down menu for the customer ContactF (13:44) is nice and small at the moment surely if you've got 100 or 1000 customers this going to get exceedingly long?


Reply from Richard Rost:

Yes, it can get pretty long depending on how many customers you have. Remember, if you start typing in the first couple letters of the customer's name, the combo box will jump there.

Robert Rivera on 6/14/2014: Hi Richard, I was watching the "call back" video where you set up dates and times to call customers back. Is there a way to have the time of the call pop up on the screen to remind you that you have a call to make?

Thanks
Rob

Reply from Richard Rost:

Yes, it's possible, but you'd need a form running in the background with an OnTimer Event running to check for events every X seconds. Definitely possible. I cover timer events in a couple of different seminars.

Lee Laechelt on 7/9/2014: Richard -- at 19:10 in the Expert 1, Lesson 4, the ContactF displays Anna Picore with the Notes from Joe (Joe wants a new PC). I followed your directions and my Form looks the same as yours, but there must be an error in the information you provided. Please advise.
Tiziano Fortin on 8/18/2014: Hi there.
The main table of my database has the following structure: LastName (Text); FirstName (Text); Address (Text); City (Text); Phone (Text); ... and so on.
Is a good idea to put the field FullName (=Trim[(FirstName)+&" "+&(LastName)] as primary key?
How can I do it without rewriting by hand the contents of this new field?
Regards. FT

Reply from Richard Rost:

No. In fact, you don't want FullName in your TABLE at all. You want this as a CALCULATED QUERY FIELD.

Richard Wilson on 8/24/2014: Problem with a relational combo box. I am trying to make a "FullName" in a query witn a prefix (Mr, Mr & Mrs, Dr, etc) FNames and LName. I have been able to do combo boxes before with no problem. Now they are showing up with the number. I set the field size to 0 but no dice. I am storing the result of the combo box in "Prefix". I do not have PrefixID field, but I have tried that also. Suggestions?

Reply from Richard Rost:

You should make a QUERY with FullName in it. Do your concatenation there. That query should output two values: CustomerID and FullName. Now use that to make your combo box.


Betti Schuler on 10/16/2014: If I have an employee table and each employee already has a unique ID # - is it necessary to use an autonumber in my employee table? Can't I just use their existing ID # as my primary key?

Reply from Alexander Hedley:

You can use this instead.

Michael C. on 10/23/2014: Hello, I have a question. I'm not sure I understand how Access knows which value to store when a multiple column combo box is used? For example, in video 4 of the Expert level 1 class, the columns in our combo box on the Contact form are Customer ID, First Name, Last Name and Company. We hide the Customer ID - primary key field. We select a customer name using the combo box, but the ID is what gets saved to the Contact table - even though we never explicitly told Access to do this. How does Access know to save the ID rather than the name? This doesn't seem as straight-forward as when a query (rather than a table) is the source for a combo box. Thanks in advance for your help! M.

Reply from Alexander Hedley:

Hi Michael,
By Default a Combo's column choice is always the 1st Field, so you have your ID as the first field, usually hidden, then your display fields next.
Check the 'Bound Column' Property in the 'Data' tab.
You can use the .Column(#) changing the # to the column number you want. Try a MsgBox and change the # to see what value is shown.

Joe Beniacar on 11/2/2014: Hi Richard,
Could you please explain a little bit more why we shouldn't eliminate the LeadSource and Shipping fields from the MANY CustomerT (in the One-to-Many Relationship of LeadSourceT to CustomerT), but we are eliminating the FirstName to Phone number fields in the MANY ContactT (in the One-to-Many Relationship of CustomerT to ContactT)? Shouldn't we also eliminate those fields in the CustomerT, since they are redundant data? (whether they are good data or not?) Thanks!

Reply from Richard Rost:

It's been a while since I recorded this, but if memory serves, LeadSourceT was just a helper table that contains a list of possible lead sources. You would store that once in the customer table.

Generally, you want to prevent duplicate data in your database, however there are some exceptions as I will cover in later classes. For example, you generally want to store the customer's address only ONCE in the customer table, HOWEVER, there is the exception where you might want to store the address on EACH order he places, so you know where THAT order was shipped. Historical data is one of the exceptions, and I cover more in future lessons (for example, performance increases, etc.)


John Fass on 11/19/2014: Richard,
After watching Access 2010 Expert 1 video on Relational Combo Boxes I added a combo box using the combo box wizard.I selected the values from table option. I moved 2 fields over from the Available fields to the Selected fields side in the wizard.One field was the RoomTypeID the other was the RoomType. I chose hide the key column option. I stored that value in the RoomTypeID field.When I try to type a value that is not in the combo box list Access will not allow that. I went to design view and opened the property sheet for that combo box. I changed the LimitToList option to no. A pop up message states that "Microsoft Access can't set the LimitToList property to no right now. The first visible column which is determined by column width property isn't equal to the bound column. Adjust the column width property first then set the LimitToList property." I have adjusted the width and I the LimitToList property did change to no however when I changed to form view only a number appeared in the combo box not the text. Hope you can help.

Michael Goins on 11/29/2014: Hey Richard, if I'm creating a database where the patient acct number is the unique identifier. Would you then recommend an Autonumber AND the unique patient acct number and what would be the advantage of having both?

Reply from Alex Hedley:

An AutoNumber is handy for setting up relationships and is more efficient for relationship joins as its a number and not text.
You could keep both. Have your unique number for searching on and the AutoNumber for the relationships.

Michelle Salas on 12/8/2014: I am developing a database to keep track of employee safety trainings. Each employee will have multiple safety trainings on a specific date. Will I need to set this up as a one-to-many or a many-to-many relationship? I will want to search by employee (to see what training they have had), by safety training (to see who took each training), and by year (too see what training was done that year). I think that the training 'events' should be organized in a table as a log of training, like the contacts in your example. However, will that be the best way to set my database up to search by Employee, Training, and Year? It seems that this may be a many-to-many relationship since multiple employees will have taken the same trainings.

Reply from Richard Rost:

What you have here is a MANY-TO-MANY relationship. You have a table of employees, a table listing all of the available training classes, and then a junction table to track which employees took what classes on which dates. I cover this kind of relationship in detail in my Access Relationships Seminar.

Aly (Office Manager) on 12/10/2014: Ok so the issue i am having is that customer id #'s in my my customer table have some how stopped matching up when i fill out a new form. Its exactly 3 numbers off. Any suggestions as how to fix this so i can properly move forward with relational tables?

Reply from Alex Hedley:

Which Form are you filling out?

Richard Wilson on 12/13/2014: Richard, I've been working on what will be a very complicated database and it has dawned on me that the same people can appear in several different tables, in this case student, teacher, supporter. I can explain why but it is immaterial and since this covers a long expanse of time. Would it be better for structural purposes to put all the people in one table with with a id field that could designate which of several groups they might belong to? With years and other differentiating factors being supplied? This would have to be a self join table in many instances. Hope this is clear.

Reply from Alex Hedley:

This would make sense if you aren't wanting duplicates.

Tom Green on 2/15/2015: Brilliant Richard. You've taken me to the point where I can pretty much attempt the task I initially wanted to do when I set out on your courses. I plan to continue to learn more from you but I'm missing one thing and I was hoping you could point me to one of your courses with the answer. I need to pull the data from Access to an excel template. This will have filter criteria declared in the excel spreadsheet. So let say starting in cell C10 of a spreadsheet I would like to import data from a table/query in Access, filtered by values declared in other cells within the excel spreadsheet. If you have a video that covers this I'd love to jump straight to it to complete by immediate goal. Cheers

Reply from Alex Hedley:

I don't think these cover exactly what you need but they'd be a good starting point

You can link an Access Table in Excel and create a PivotTable which you could add your Filters too

Expert Level 19

Excel Upgrage to 2007
18. Get External Data (9:49)
From Access Database

Angelika Gutenberger on 2/21/2015: I have a customer who has all of his data entered in on an excel spreadsheet. Is there an efficient way to get the information from a flat database to a relational one. I keep trying to copy and past multiple column but all the columns end up into one cell,and I can't transfer a whole record at once because all of the information is on different tables. I was going to try and set up a form that would make this easier....What would you do?

Reply from Alex Hedley:

Highlight the table in excel.
Open Access, click in the Navigation Pane and Paste (Ctrl+V) and it will turn that into a Table.

You can then use the Table Analyzer - Tools, Analyze, Tables to split the flat file
MS Article 2002/2003/2007
It's not perfect but does a good job.

The better way is doing it yourself like shown in Expert Level 1. This shows how to make a Flat File into a relational db.



Christy Osterkamp on 3/2/2015: I am trying to recreate an Excel spreadsheet into Access. Is this specifically discussed in a particular lesson?

Thanks

Reply from Alex Hedley:

That would be Expert 20

Christy Osterkamp on 3/3/2015: Richard, I have been watching Expert 1 over and over and just not getting it. Instead of customers, contacts, and vehicles, we have fields (literal), which are owned by farms, which are owned by growers, which reside in a state. I have created separate tables for all of them, but when I try to build my relationships they multiply exponentially (maybe not the right term). Any advice?

Reply from Alex Hedley:

Try building them up a query at a time.
Join your first two tables to get your result.
Then create a new query with a table joined to a query and so forth.

Alex on 3/27/2015: I've emailed customer support but was hoping someone could lend a hand before I pull my hair out. This tutorial was extremely helpful but I just can't accomplish what I'm aiming for.

I have multiple tables to include a junction table; employees, their training dates, & their due dates. I want to create a form for the end user that uses a combo box to select an individual & searches & displays all his/her info in datasheet view. Creating a command button to get ALL of the employees' info was easy enough but this is proving to be a pain. Thanks for any help

Reply from Alex Hedley:

Just an FYI Customer Support isn't for Technical Questions, this is what the Forum and TechHelp are for.

You will want to create a Query with a CRITERIA. This is where you can say SELECT * FROM InfoT WHERE EmployeeID = x
Your x is the value from your ComboBox.

See this Tip for Parameter Query.

See this Tip for using a value from a Form.

Mohammed Ghulam on 4/1/2015: Hi Richard,

how do you minimize your table and work with two table in the same time? because I have only the close button when I view my form,table, or quire.. I checked in the property sheet and it says min/max are enable.

thanks

thanks

Reply from Alex Hedley:

In Access Options | Database Settings do you have your tabs set as Overlapping Windows or Tabbed Documents?

Alejandro Rivas on 4/12/2015: Mr. Rost,

Thanks Alex Hedley btw. This may seem like a silly question but what's the purpose of building individual tables if you can just build a junction table with everything in it? When building a form with all the fields you want displayed, shouldn't you add the fields from the junction table instead of individually from each table to avoid type mismatches? That's why I'm asking about the purpose of building individual tables.

Reply from Alex Hedley:

A Junction Table is usually a one that links two tables together using IDs.
You can then use a Query to join these Tables together.
Once you have the Query you can then use that as the source of your Form.
The individual Tables are better to keep your database normalised.

Rachel Mychajluk on 4/23/2015: Hi there. This might be very obvious, but I'm noticing that I need a password to open the associated database online. Is that listed somewhere?

Thank you!

Reply from Alex Hedley:

The db password matches the Course password which you can find in the My Course link in your account.

Walk Thru

Jacqueline Wood on 5/13/2015: I am following step by step on this Advanced Level I. I somehow messed up on my Contact Form - the customer ID is stuck on one customer. How do I fix the problem so that as I change the customer the Customer ID changes also.

Reply from Alex Hedley:

Have you set the control source to be a number?

Try removing the field and re-adding it to the form.

Rachel S on 6/29/2015: First, I have to say that, after having watched all of the Beginner Tutorials, I am very pleased with the quality of instruction I am receiving. Thank you! When I created a combo box with several columns, I did not adjust them all wide enough. Is there a way to go back into the combo box and resize my columns?

Reply from Richard Rost:

You'll have to open up the properties for that combo box in design mode and manually adjust the COLUMN WIDTHS property.


Rachel Scott on 7/2/2015: Great! Thank you! :-)
michael spencer on 7/15/2015: How can view my tables on eon top of the other so I do not have to tab between them. I would like he same view you have at 6:30

Reply from Alex Hedley:

Overlapping Windows vs Tabbed Documents
This is showing in B2
File | Options | Current Database

michael spencer on 8/3/2015: Hi Richard

I have a customer ID as a primary key. I then use that number in my other tables to identify who the order went to . When I run my query it shows the customer id instead of their first and last name. Whenever I set up an auto number (for example 1 is paid and 2 is unpaid, the auto number is always displayed in my queries even though I have the text names in my combo boxes??

Reply from Alex Hedley:

You just need to make a Query where you join the Table with the Description to the IDs.

Aaron Ross on 8/28/2015: I downloaded the database but it is requiring a password to open it. Where do I find that?

Reply from Alex Hedley:

The Database Password is the same as the Course password.
Just go to your account and find it under My Courses

Or see this WalkThru.

Aaron R on 8/30/2015: Thank you.
I was able to open it, but fr some reason, when I try to save it (or open up another file), the screen flickers a lot and ultimately does not let me save or open. Any ideas?

Reply from Alex Hedley:

Try a Compact and Repair

Richard Bray on 9/27/2015: I created a form using a Query and I cant enter anything in the form??

Reply from Alex Hedley:

If you open the Query is there a new record at the bottom?
It might be that your Query is too complex so it isn't updatable.

Spencer Farr on 9/29/2015: Am building a data base to track work orders. have just over 100 entries in my main table. just finished this lesson and fixed Ts, Qs, Rs, and i thought Fs. everything looked right however when i go to input new data in my F it tells me " The value cannot be added to this new row until the row has been committed. Commit the row first, and then try adding the value." get that message for all fields. I can still add data directly to my table and it doesn't show up on my form. as far as i can tell my record source is still the correct table.

Reply from Alex Hedley:

Are the controls bound to the correct Fields?
One option is to delete and recreate the Form.

Spencer A Farr on 10/2/2015: Designed a new query with all the related fields, rebuilt my former based on that. Deleted the old form and it works good. One question though, once related and normalized my data base my order got out of wack on my primary table. It runs in blocks about 4. 43-70,23-42, 1-22,and 71-106. What would make it down that. It cured itself for a few minutes then went back. Not a big deal but annoying.

Reply from Alex Hedley:

Try a compact and repair.
Are you ordering it by something other than your AutoNumber?

Teri Mangiameli on 11/16/2015: There seems to be a problem at 13:54 of Expert 1 Video 4. When Richard chooses Anna from the new combo box, her Customer ID changes appropriately, but the note does not. What am I missing? I try to build this exactly following the video, and at the same spot I have a problem. I try to choose a different customer from the combo box and Access gives me the error, "Control cannot be edited; its bound to Auto Number field CustomerID. So I'm stuck. And this is the main thing I need to do in my own database.

Reply from Alex Hedley:

The Form holds information about the Contact.
The CustomerID is being changed but that wouldn't change the value of the Note Field.

What is the Record Source of your Form is it ContactT?

Teri Mangiameli on 11/23/2015: I figured out my relational combo box problem. Please disregard previous email.
Carlos Espinosa on 11/28/2015: When trying to do a relational combo box, and am looking "to store that value in this field" Access doesn't show the CustomerID field. ??

Reply from Alex Hedley:

What is it showing instead?
Are you setting the bound column?

Carlos Espinosa on 11/28/2015: Previous question regarding relational combo box 12:56
Carlos Espinosa on 11/28/2015: Can't set up the relational combo box.
@ 6:24 Access doesn't show the CustomerID field of the Contact table.

Reply from Alex Hedley:

Is it in your RecordSource or Query?

Carlos Espinosa on 11/29/2015: Can't set up the relational combo box.
@ 6:24 Access doesn't show the CustomerID field of the Contact table.

Carlos E on 12/2/2015: Record Source

Reply from Alex Hedley:

What fields are showing?

Carlos E on 12/2/2015: It shows the other fields of the table omitting the CustomerID field
Carlos E on 12/2/2015: Bound column?

Reply from Alex Hedley:

This is the setting to tell Access which column if you have multiple columns in your Combo to bind to the value in the Table you wish to store the choice in.

Joe Oliveira on 12/9/2015: Hello Richard, First I'll mention that your courses are great, very visual, and I need that, but as easy as it looks, I seem to be lost on the combo boxes. I'm following your lessons to the T, then I go into the Dbase I"m trying to make, I have a RoomLocT where I'm getting the info from and attempting to creat an order form with combo boxes. All combo boxes beep, if I hold the cursor on them I see a message on the bottom left saying "the recordset is not updateable", can you please tell me what I could be doing wrong?

Reply from Alex Hedley:

This is usually due to the fact there are too many joins in your Query, or there is a complex calculation in one of the Fields,
Is your Record Source made up of these?

Joe on 12/13/2015: thanks for reply, but have to ask what do you mean to many joins? and no I haven't created any calculations, yet. following the videos examples of wrong way of creating and then right way, right having created a table for drop down options in combo boxes. wrong way (not all the time) was a combo box with a list, it was working that way, but when I created a table for certain drop downs, now I get that beep message. As the ex. table I mentioned RoomLocT, I created the ID field, created the foreign key on the order table, linked both tables together, deleted the old combo box and created the new one following Richards vid Expert 1 vid 3 and 4. it shows the drop down choices but can't choose anything.
I'm totally lost now hahaha

Reply from Alex Hedley:

Try adding 1 Field at a time to your Query, see if the recordset is updatable until it isn't they you'll know which Field is causing the problem

Joe Oliveira on 12/16/2015: okay, thanks but as a newbie let me start by saying not sure what you mean to many joins in a query? or, is that what I should be working with, Queries instead of a tables?
at this point I do not have any calculations.

Reply from Alex Hedley:

When you create a Query you can join many tables together, this is covered in the lesson.
A Query is just a way of getting information from one or more Tables in your database.

Joe on 12/21/2015: that's what I thought you meant.
I deleted the form and created it again. The combo boxes are working
thanks

Sheryl Reese on 1/20/2016: Hi Rick. I've watched the courses from the Beginner Level 1 up to the Access Expert 2010 and I like how easy it is to follow the concepts and steps. I've worked with Access before but using this as a refresher.

My question relates to planning/setting up my database and tables.

I have an enormous excel spreadsheet of product types and the cost if we make it, we buy it from our partner, or we buy it from a third party. We were updating this information in excel but I thought Access would be quicker and easier and we can run queries from this. I'm struggling even more on figuring out how to break the information into tables that would be the most effective for what we need. Initially I thought I needed a product table and then a product cost table for each source but it seems that perhaps I should have a product table (with no cost) and then a cost table for each of the sources. The cost tables would be the ones updated as prices change. Your feedback is appreciated.

Thanks
Sheryl R

Reply from Alex Hedley:

Sounds good to me, then you have a historic list of what the costs were

David Williams on 2/9/2016: Hi Rick,

I have a massive spreadsheet that I am trying to convert over to ACCESS. I have taken every column from the spreadsheet and created a different table for each. There are about 14 different tables. Each table only having one piece of information in them. The spreadsheet has the following different information: Company assigned number, Tool Manufacture, Tool Model Number, Tool Serial Number, Tool Description, Date Purchased, Employee assigned, When Assigned, When Turned In, etc. With converting this spreadsheet it has left me with duplicate Manufactures and duplicate employees, etc. What is the best way to fix all of these duplicates?


Reply from Alex Hedley:

Expert 18 shows the "Find Duplicates Query"

Jim Peters on 2/16/2016: I have a Unit table with each of our locations and a vendor order history table for each vendor. Each vendor has a customer or ship to number for each of our locations (units)and I have stored the vendor's customer number in my Unit table. Can I use that vendor customer number in my Unit table instead of my primary key (UnitID) as the primary key? And relate it to the customer (Ship to) in my vendor table as the foreign key?

Reply from Alex Hedley:

You can, or you could just join the multiple tables with a Query to get the info you need, or use DLOOKUP.

John Hanbury on 2/16/2016: Im trying to set up a database whereby i have several people working for one company. i want to select a worker and relate him/her to a particular company, this is over several companies do i need a junction table i am only as far as expert 1 am i being too ambitious


Reply from Alex Hedley:

Are they only going to belong to 1 company? What do you mean over several?
You could just have a CompanyID in the WorkerT and put in the CompanyID into that

Relationships Seminar

Marcus Mueller on 3/7/2016: Hi Richard,
Excellent teaching. Having a problem with relational combo boxes. I have created 2 different forms that use data from 2 different tables. Both combo boxes have numbers in the drop down lists. With one of the boxes I can start typing a number and the box will jump to that number, I hit return and move on. The other box gives the error "The text you entered isn't in the list" when I hit return. This really slows down data entry because I then have to scroll down over 100 numbers to pick the one I need. What have I done wrong. When using text combo boxes from either table the work fine. Thanks for you help. Result is the same if I use Return or Tab.

Reply from Alex Hedley:

Have you changed any of the Data properties?

Marcus Mueller on 3/11/2016: I suspect that I have a data difference in the tables but haven't been able to find it. Do you have some suggested fields were I can start looking for the problem? Thanks.

Reply from Alex Hedley:

Are there distinct values in the one that isn't working?

MS Article

Marcus Mueller on 3/12/2016: Both fields in question are numeric, long integer. As far as I can tell they are set up the same in both tables. All the form combo boxes work on one form based on their respective tables. Combo boxes based on the other table will not allow selection by typing at the prompt. It gives the previous text error and I am required to pick from the drop down list. The table is question works fine in reports and queries. I only have a problem with combo boxes. Thanks for you time.
Marcus Mueller on 3/12/2016: Hi Rex. I solved the problem by exporting all the data in the table that was giving me trouble to an excel spread sheet, created a new table & imported the data back into the new table from the excel spread sheet. The problem went away. Does this solution give you any idea what may have been wrong? Thanks for you help.
Virginia Mergl on 3/25/2016: Hi Richard, I really enjoy your lessons. I am trying to make a kennel booking system. I have a customer table, pet table and booking table. They should be linked by Customer Surname and Post Code. On my pet table instead of repeating surname and post code I made a relationship and it works find however when I add new customer details on my customer form it does not appear on my combobox on the pet form. I hope I you understand what I mean. Thank you

Reply from Alex Hedley:

What is the combo on your Pet Form based on, is this a Query of Customers.
Is the Pet Form open when you add a new Customer? If so you will either need to close and re-open the form or send a Me.Requery to the combo to tell it it needs to update.

Virginia M on 3/26/2016: Dear Alex, thank you for your prompt reply. The combo box is based on the Customer Table. It does work when I close and reopen. I do not know yet how to make a Me.Requery yet, I am on the beginning of the expert lessons, I am sure I will learn it on the next lessons. Thank you again
Tim Chavez on 4/13/2016: Good afternoon. Time Index 11:00 you mentioned you were going to cover how to do an update query if tables were set up wrong. I'm trying to update my tables to reflect the foreign key instead of the data itself. Thanks!

Reply from Alex Hedley:

There are Seminars on SQL.

Alexandra George on 4/26/2016: I have a many-to-many relationship set up with a join table: I have ConsultantT and VendorT which are linked in ConsultantXVendorT (with fields ID, ConsultantID, and VendorID). I used the relationship window to set up explicit one-to-many relationships between ConsultantT and ConsultantXVendorT as well as VendorT and ConsultantXVendorT. Now, I am building a form for a user to enter Consultant data, which is based on ConsultantT. In order to make a combobox where the user can select the VendorName from an existing record in VendorT for a new consultant, how should I pull in the VendorID//store the data that is input? I tried to use the wizard to build a combobox but since there is no VendorID field in my ConsultantT, I got a bit lost.

Reply from Alex Hedley:

If you've got it as a subform on ConsultantF you can just add as many Vendors as you need and have the ConsultantID take from Forms!ConsultantF!ConsultantID.

Sarah Dalling on 6/2/2016: Hi! I'm trying this process and when I run the query none of my data shows up? I have entered test data as you've shown and have gone through the steps, but the query only shows the column heading and no other data? Expert 1, Lesson 3, around 5:07. Thanks in advance!

Reply from Alex Hedley:

Does the sample db show data for you?
Are you sure you have related data so the joins are correct.

 

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

 

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