Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
 
 
Access 2010 Expert 1
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   8 years ago

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.

Multi Value Combo Boxes Upload Images   Link 
John Schulte 
12 days ago
Is there a way to pick multiple values of a dropdown list. As an example; A customers preference is to ship UPS first and them USPS, but I'd like that to show in the same field with just a comma between them. Read More...
Alex Hedley
12 days ago
How about a MultiSelect ListBox Access Developer 15
Kevin Robertson
12 days ago
If you need your list of values in a textbox separated by commas for display purposes,  you could try something like this:

    Private Sub btnAdd_Click()

        Dim S As String
    
        S = ""
    
        S = ComboBox.Column(1)
        TextBox = TextBox & ", " & S
        If Left(TextBox, 2) = ", " Then
            TextBox = Right(TextBox, Len(TextBox) - 2)
        End If
    
    End Sub

Also, since you are a Gold Member you have access to  the Code Vault. Check out Richard's DLookUpPlus function.
Add a Reply
Enter Parameter Value from AE1 Upload Images   Link 
Lisa Messer 
2 months ago
This is something that has been driving me nuts in our own Database. In following along in Access Expert 1 (3rd video), with using the BlankDatabase I deleted some stuff, but it isn't exactly what you have in this video. But I did get  "Enter Parameter Value" when I open the query OrderInvoiceQ and the Parameter Value is "Forms!OrderF!OrderID". I opened the query and there was no data in there. So I opened the OrderF form and the first one was Order ID 1 with and order date for Training Customer Richard Rost. There were 3 records, Second one is James T Kirk who ordered Starship Parts and 3rd was Richard Rost again who ordered a New PC. Ok so why was the query empty? So I closed out the query and reopened it and when I did, the order that I was on in the forms, showed up in the query, also didn't get the Parameter error. So I went to my own Database and the Parameter Value refers to a form but I am trying to open a form. So when I go to the form that it is referring to, open it and then open my form, I don't get that error. My form gives me all the info that I am needing, I just don't know how to fix it from giving me and the other users, that error.
Lisa Messer
2 months ago
Ha! I found the answer on why it has to be open in order to give a value. Although on our DB it doesn't need to be open to give data in the report. I didn't create the query  or the form, it just drives me nuts that it is happening.
Scott Axton
2 months ago
Lisa -
99.99% of the time you spelled something wrong.  
See the Enter Parameter Value video.
If you still have issues please let us know.  Also, take a screen shot and post it using the link in the upper right of you original post of your query and any other info you think might help us.
Add a Reply
What happens when Upload Images   Link 
Julie Hunt 
4 months ago
I understand the concept of relationships much better than I did but... I will probably get may answer in Level 2 but I will ask now just in case.  I have educational events/conferences that happen annually and each year the attendees are different, there may be 10% or so that are repeats.  I usually have from 150 - 300+ attendees, I want to offer transcripts to my attendees for their educational credits SO what happens when there are a) numerous attendees and b) HELP!!
Adam Schwanz
4 months ago
Maybe I'm not following what you're asking, but it looks like you want to assign multiple attendees to conferences, and perhaps multiple conferences to attendees. What you would need for that is a junction table.

https://599cd.com/blog/display-article.asp?ID=1585

If that's not what you're asking for, could you elaborate a little more? Thanks
Julie Hunt
4 months ago
Yes you are correct.  That is one of the relationships I want to develop and I also want to be able to report on a single participant over a course of 6 years, which is the accreditation term for ACCME (Accredited Council for Continuing Medical Education), who want their accredited providers to  "Describe the mechanism your organization uses to record and verify physician participation for six years from the date of your CME activities". As I researched Access I began to believe that I could develop a database to do that.  I have lots of ideas buzzing around in my head but I am not sure if my theory has a practical application benefit, obviously due to my lack of expertise.  To date I have used Excel tables to record participation but to create a report is laborious and somewhat frustrating.
Add a Reply
key question Upload Images   Link 
Wendy Summers 
13 months ago
Yes, In your shipping table example you have a primary key associating information regarding the key. When I run a report it pulls in the key but not the information associated with the key. I want to see the information not the key...
Richard Rost
13 months ago
Did you join the two tables together in a query and then use that query to create your report? If not, you won't see the details from the 2nd table.
Wendy Summers
13 months ago
Thank you!
Add a Reply
Primary key question Upload Images   Link 
Wendy Summers 
14 months ago
Why is it possible to pull a name (text) but only a shipping key? Is there a way to have the text pulled into a customer form?
Richard Rost
14 months ago
Hi Wendy. I'm sorry but I don't understand your question. Can you please elaborate?
Add a Reply
Duplicate Records Upload Images   Link 
Wendy Summers 
14 months ago
In your car example, what if you had two customers buying the same name and year of car? Does that then duplicate the info in the car table?
Richard Rost
14 months ago
The example I gave is oversimplified for class, but yes, the year, make, and model would be the same, but the foreign key (CustomerID) would be different allowing you to know which customer purchased which car. In a real-world database you would also have something to uniquely identify the specific car, like a VIN or other serial number. Plus make would have it's own table (Ford, Chevy, Audi, etc.) and so would model (Taurus, Focus, F150, etc.) and those would just be IDs too. But again, I was keeping it simple for class to teach you relationships. Does this answer your question?
Wendy Summers
14 months ago
Yes, thank you.
Add a Reply
Sample Data Upload Images   Link 
Sarah Dalling 
5 years ago
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! Read More...
Add a Reply
Relationship Upload Images   Link 
Alexandra George 
5 years ago
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. Read More...
Add a Reply
Good afternoon Time Index 11 00 you mentioned you Upload Images   Link 
Tim Chavez 
5 years ago
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! Read More...
Add a Reply
Missing Customers Upload Images   Link 
Virginia M 
5 years ago
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
Add a Reply

Show Older Comments...
View in Table Format

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

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

10/16/2021Relink Tables
10/16/2021Access Developer 34
10/16/2021Access Developer 34 Lessons
10/16/2021Access Developer 34
10/10/2021Copy Text from Field
10/10/2021Access for Free
10/9/2021Birthdays
10/8/2021Label Resize
10/2/2021Criticisms of Access
10/1/2021Access Developer 32
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access 2010 expert 1 relational relationships foreign key ad hoc query combo box  Page Tag: whatsnew  PermaLink