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

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?

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.
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.

Thanks,
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
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).
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.

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).
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:

Thanks.
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.[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
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
I RUN ACCESS 02 ON OSXP AND CAN'T USE YOUR ACESS201. ANY HELP WOULD BE MUCH APPRECIATED.
WW
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 5+1:
  
  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:

10/20/2014Access Tip: Switch Function
10/20/2014SWFL Access Developers Meetup Group
10/17/2014Access: Four Employees Per Event
10/14/2014Putting Access Databases on the Web
9/21/2014Microsoft Access Expert 25
9/20/2014Access 25 Just About Finished
8/2/2014What Access Topics Do YOU Want?
7/16/2014Microsoft Access Expert 24
7/15/2014Access 24 Finished
7/14/2014Windows 8.1 Delayed for Access 24
 

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