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  

News      User Comments     History     Notify Me

2/5/2013 12:25:40 AM
Access 2010 Expert 1
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

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.

Permanent Link
Course Link: Access 2010 Expert 1
Keywords: access 2010 expert 1 relational relationships foreign key ad hoc query combo box
Page Tag: whatsnew
Post Reply

Access 2010 Expert 1 Comment from Alex @ Fri 3/27
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.
Show Just This Thread        Post Reply
Apply knowledge to own db Comment from Christy Osterkamp @ Tue 3/3
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.
Show Just This Thread        Post Reply
Excel into Access Comment from Christy Osterkamp @ Mon 3/2
I am trying to recreate an Excel spreadsheet into Access.  Is this specifically discussed in a particular lesson?  


Reply from Alex Hedley:

That would be Expert 20
Show Just This Thread        Post Reply
Flat File to Relational Database Comment from Angelika Gutenberger @ 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.

Show Just This Thread        Post Reply
Access to Excel Export Comment from Tom Green @ 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
Show Just This Thread        Post Reply
One PersonTable Comment from Richard Wilson @ 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.
Show Just This Thread        Post Reply
CustomerID is off by 3 Comment from Aly (Office Manager) @ 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?
Show Just This Thread        Post Reply
I am developing a database to keep track of employ Comment from Michelle Salas @ 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.
Show Just This Thread        Post Reply
AutoNumber Comment from Michael Goins @ 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.
Show Just This Thread        Post Reply
Combo Issue Comment from John Fass @ 11/19/2014
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.
Show Just This Thread        Post Reply
eliminate the LeadSource and Shipping fields Comment from Joe Beniacar @ 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.)

Show Just This Thread        Post Reply
Combo Column Choice Comment from Michael C. @ 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.
Show Just This Thread        Post Reply
Alternative EmployeeID Comment from Betti Schuler @ 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.
Show Just This Thread        Post Reply
Problem with a relational combo box Comment from Richard Wilson @ 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.

Show Just This Thread        Post Reply
Fullname Comment from Tiziano Fortin @ 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.
Show Just This Thread        Post Reply
Comment from Lee Laechelt @ 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.
Show Just This Thread        Post Reply
Hi Richard I was watching the call back video w Comment from Robert Rivera @ 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?


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.
Show Just This Thread        Post Reply
Just a questions about the drop down menu for the Comment from philip Ogden @ 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.
Show Just This Thread        Post Reply
Whats the difference of doing this with a combo Comment from Andr? Silva @ 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.
Show Just This Thread        Post Reply
Very Useful Comment from Ravi C @ 3/8/2014
This is really very useful for me.

Show Just This Thread        Post Reply
Comment from Stefano @ 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
Show Just This Thread        Post Reply
notes on the contact Comment from Sonia F @ 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.
Show Just This Thread        Post Reply
Access 2010 Expert 1 Comment from MICHAEL J @ 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?
Show Just This Thread        Post Reply
Comment from Christian Crosson @ 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
Show Just This Thread        Post Reply
Comment from William Allan @ 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?
Show Just This Thread        Post Reply
Comment from Felix Amoyaw @ 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
Show Just This Thread        Post Reply
VehicleID Comment from Brian @ 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.
Show Just This Thread        Post Reply
VehicleID Comment from Brian Merrick @ 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.
Show Just This Thread        Post Reply
notes on the contact Comment from Robert Garner @ 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.

Show Just This Thread        Post Reply
Autonumber Comment from Juan @ 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.
Show Just This Thread        Post Reply
Access Cash Register Comment from SYED KHURRAM @ 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.

Show Just This Thread        Post Reply
Sample Databases Comment from jay collins @ 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.
Show Just This Thread        Post Reply
Text IDs vs Autonumbers Comment from Tom Witchek @ 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.
Show Just This Thread        Post Reply
Autonumber Comment from Thomas Witchek @ 6/25/2013
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).
Show Just This Thread        Post Reply
Notes changed too Comment from Laurie J @ 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.
Show Just This Thread        Post Reply
search combo box 3rd option Comment from Dory Michell @ 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.


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).
Show Just This Thread        Post Reply
Great lecture Comment from Krishnan S @ 5/1/2013
Great lecture. I was able to understand better. Great job. Richard

Reply from Richard Rost:

Show Just This Thread        Post Reply
Last 20 Records with TOP Comment from David Thomas @ 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.
Show Just This Thread        Post Reply
How can I get a Memo filed in the combo box Comment from Martin Olson @ 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.

Show Just This Thread        Post Reply
Wont run on my Mac Comment from John Hughes @ 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.)
Show Just This Thread        Post Reply
Who better to learn from Comment from Liz @ 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. :)
Show Just This Thread        Post Reply
Wont run on my Mac Comment from Alex Hedley @ 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.[]

Have you used the Walkthrus:

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

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

(The Online Theatre should work fine.)

Show Just This Thread        Post Reply
Notes changed too Comment from Luli P @ 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.
Show Just This Thread        Post Reply
Wont run on my Mac Comment from Luli P @ 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?
Show Just This Thread        Post Reply
primary key multiple tables Comment from @ 2/27/2013
Thank you so much.   Really great lectures.
Show Just This Thread        Post Reply
multi table multi forms Comment from Martin Olbrycht @ 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.
Show Just This Thread        Post Reply
primary key multiple tables Comment from Anonymous @ 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.
Show Just This Thread        Post Reply
Comment from yifan zhang @ 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.
Show Just This Thread        Post Reply
Comment from Mitch Easton @ 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.  
Show Just This Thread        Post Reply
Comment from Munesh @ 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?
Show Just This Thread        Post Reply
Access 2002 SWITCHBOARD Comment from werner wallace @ 2/5/2013
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


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 7+5:
  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:

1/20/2015Access Advanced DateTime Seminar
1/19/2015Microsoft Access Expert 28
1/3/2015Access X28 and Advanced DateTime Seminar
1/2/2015Microsoft Access Expert 27
12/13/2014Learning Access VBA Programming
12/6/2014Custom Access Error Messages
11/27/2014Two New Access Handbooks
11/25/2014New Access Tip: Shell Function
11/24/2014Access Open Other Programs Seminar
11/24/2014Next Up: Date Time Functions in Access

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
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