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 2    dismiss
 
 

< Previous: Access Expert 2

Next: Access Expert 4 >

Access Expert Level 3

Expert Microsoft Access Tutorial - 1 Hour, 26 Minutes
 
 
This Microsoft Access video tutorial picks up where Expert Level 2 left off. This class teaches you the basics of SQL (the language behind all of the queries in your database), form footer totals, subforms, and lots more. You will learn:
 
  - SQL Basics
  - Future Callbacks for Customers
  - Default Value to Tomorrow at 9 AM
  - Creating Subforms
  - Link Master & Child Fields
  - Using SQL in Form Record Source
  - Form Footer Totals
  - Sum, Avg, Max, Min, Count
  - Modifying Embedded Macros

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 3
Description: Access 2010 Expert Level 3
Versions: Recorded with Microsoft Access 2010
Works fine with Access 2013 and 2007
Pre-Requisites: Access 2010 Expert Level 2
Running Time: 1 Hour, 26 Minutes
Cost: $19.99


This class picks up where Expert Level 2 left off. We'll begin by learning about SQL (Structured Query Language) which is the code behind all of the queries, forms, and combo boxes in your database. You'll learn about the basic components of an SQL statement. You don't have to learn how to write your own SQL at this point, but I want to teach you enough so you can modify the SQL that Access provides for you. It adds a lot of power and flexibility to your database.

 

In the last two classes, we built a Contacts form to track the previous contact history with our customers. In today's class we'll modify it a bit so we can track callbacks for those customers. Need to call Joe Smith tomorrow at 9am? We'll put that into the callback form and tomorrow you can quickly generate a list of everyone you need to call. It's a way to reuse a single table with similar data for multiple purposes.

 

Next we'll learn about something that's VERY powerful in Access: the subform. This is a way for you to show related records inside another form. For example, wouldn't it be nice if you could see the last couple of contacts for a customer right on the customer form without having to open another form up? That's what a subform does for you.

 

Finally, we'll learn how to calculate totals in a form footer. We'll create a products form, a product category form, embed products as a subform, and then create some footer totals to show inventory levels.

 

 

This is the third 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 subforms and form footer totals are important tools 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 3

00. Introduction (7:38)

01. SQL Primer (11:39)
Combo Box Row Source
SELECT FROM
ORDER BY
WHERE
Change Combo Sort
Change Combo Field Order
Record Source for Form

02. Callbacks (19:26)
Modify ContactT
Default Value for Dates
Today's Date
Tomorrow's Date
Tomorrow at Noon
Tomorrow at 9 AM
Modify Contact Form
Custom Date Format
ddd mm/dd/yyyy hh:nn am/pm
Callback Query
Sort by Date
Callback Form
Add button to Main Menu
Add customer name to list
Button to open Customer Form
03. Contact Subform (16:27)
What is a Subform
Master and Sub Forms
Parent and Child Forms
Copy and modify Contact Subform
SQL Statement as Record Source
Sort by Date Reverse Order
SELECT ORDER BY DESC
Subform Object Control
Click and Drag to create Subform
Subform Object Properties
Subform Form Properties
Access Autoform with Subform
Link Master Fields
Link Child Fields

04. Form Footer Totals (23:59)
Product Category Table
Product Table
ProductSubformF
ProductCategoryF
Aggregate Functions
SUM AVG MAX MIN COUNT
Modify Embedded Macro
Change Form Name in Macro
Moving to Access 2013

05. Review (6:41)

 


 
Keywords: SQL, Subforms, Form Footer Totals, microsoft access tutorial, microsoft access 2010 tutorial, microsoft access 2010 training, select from, order by, where, combo, custom format, master, child, parent, sub form, order by desc, link master fields, link child fields, embedded macro, aggregate functions, sum, avg, max, min, count
 
 

Student Interaction: Access 2010 Expert 3

Richard on 4/5/2013:  This is the third Microsoft Access video tutorial in the Expert series. It picks up where Expert Level 2 left off. This class is 1 hour, 26 minutes long and teaches you the basics of SQL (the language behind all of the queries in your database), form footer totals, subforms, and lots more You will learn... - SQL Basics - Future Callbacks for Customers - Default Value to Tomorrow at 9 AM - Creating Subforms - Link Master & Child Fields - Using SQL in Form Record Source - Form Footer Totals - Sum, Avg, Max, Min, Count - Modifying Embedded Macros Click here for more information on Access 2010 Expert Level 3, including a course outline, sample videos, and more.
Jennifer Barry on 4/7/2013: Excellent lesson and the one I have been waiting for. Can't wait for the rest

Reply from Richard Rost:

Thanks. X4 should be out later this week.

Richard Rost on 4/9/2013: As Alex has pointed out to me, the SQL statement I show in the video slide for this lesson is technically invalid. The ORDER BY clause needs to be AFTER the WHERE clause. Oops. I didn't catch this as I was recording the video. Nevertheless, the purpose of this lesson isn't to teach you how to write SQL statements. I only want you to be aware of SQL, what it is, and how to modify little bits of SQL that you might find in your database. You don't need to worry about it at this point.
Jeremy on 4/11/2013: Is there an aggregate function for finding the difference?

Reply from Richard Rost:

No. You can't really take the difference of a group of numbers, because if I give you 4,8,1 how do you know which one is first? 4-8-1 is different from 1-8-4. If you give me some context as to what you're trying to do, perhaps I can help you come up with a solution.

Nathan Ross on 4/11/2013: Thank you Richard for the Expert Series, really enjoying the series... Look forward to Level 4...
Nathan

Reply from Richard Rost:

I'm glad you approve. Expert 4 should be out very soon. I'm trying to stick to my one-a-week promise. I could probably do 2 a week, but I don't want to overwhelm people. I know if I make them too fast, people start dropping out because they can't keep up. This lets me work on a seminar project between regular classes.

David Moro on 4/14/2013: Love the site. I have been using access a long time, but just as an intermediate user. The speed at which you cover is just right. Keep up the good work! I look forward to enhancing my skillset with your videos and I want to use VBA as an integrated feature to programming in and around forms, queries and reports!

Reply from Richard Rost:

Thanks, David.

Jeremy on 4/18/2013: Thanks Richard! I am just trying to calculate a simple change in temperature...Temperature 1 minus Temperature 2 = Change in Temperature...Our Delta T values would be both positive and negative numbers...Would this be done in the AfterUpdate Event?

Reply from Richard Rost:

If T1 and T2 are fields in the same record, you could just use a calculated query field. No need for an event at all.

BEN CHUA on 5/10/2013: Hello there,

Video 2 AC 2010 Expert 3 4:23
Question?
How Can you inherit a Date from a data form?
example, you have continuous form that you fill in two dates; DateA and DateB, NewRecord* 45, the DateA always change but DateB - would like to show from Record 44 which is the previous record. How can I grab that date? so i dont have to enter it coz im lazy LOL!


Reply from Richard Rost:

You'll need to use DMAX to find the largest (most recent) date in the table.

Michelle C. on 5/17/2013: Hi, Rick. First I'd like to say that I love your tutorials.

I have an intermittent problem (not sure if this is the right forum for this). As I was working on the ProductSubformF, my formatting tools (font, color, etc.) on both the Home tab and the DB Tools tab went inactive (I'm working in ACC2007). This has happened before. I think they should be active and can't figure out why they're not. Any ideas?

Thanks.

Reply from Richard Rost:

I constantly hear from people that have problems with Access 2007. I strongly recommend that you upgrade to Access 2013. I really can't tell what this problem is from your description. I would recommend rebooting your system to see if that fixes it. If not, make sure you have the latest service pack for 2007 installed. If that doesn't work, see if the problem happens in a different database file. This is one of those things that's difficult to troubleshoot remotely.

Lone Vistoft on 5/23/2013: Now I have the problem again. When I insert my ContactSubform in CustemorF come all the contacts in the Subformen, even if it were only the contacts that related to the customer I have on in CustemorF. What is the error?
I am using an Danish version of Access, but writes in English. Can you help me get the Danish version to English?
I also have a second problem. I do not have the small spots (dashes only) in the Form Design.


Reply from Richard Rost:

You need to make sure the LINK MASTER FIELDS and LINK CHILD FIELDS properties are set to the correct fields for the relationship to form between the parent and subform.

As far as the other thing, see this post about the GRID DOTS.

Lone Vistoft on 5/25/2013: As I see it, is the Link Master Fields and Link Child Fields properties set to correct.
Where can I send Word files with print screen so you can see my properties.


Reply from Richard Rost:

You can submit files like this via my TechHelp page.

Patricia Clark on 6/10/2013: How long will I have access to these courses?

Reply from Richard Rost:

There's no expiration date. Once you buy them, you can watch them as many times as you'd like, for as long as you like. The only restriction is that they're sold on a PER-USER basis, so no sharing, please.

Mark Bishop on 6/25/2013: Can you do a count for yes no fields? I am tracking attendance for a group and would like to see the total number of people present in the footer total.

Reply from Richard Rost:

Sure, just use an AGGREGATE QUERY.

Joni Moore on 8/3/2013: At about 5:30 in this video, you made a comment about your son complaining that you repeat stuff in your videos and how important it is to understand relationships. Ironically, I didn't get how you related the two tables. Were you able to do that just by copy/paste of the field "ProductCategoryID"? or did a relationship get created when Access recognized the two names were the same when you later dropped the subform into the parent form?

Reply from Richard Rost:

You can create an EXPLICIT relationship between two tables in the Relationships Window.

If you don't, you will get what's called an AD HOC relationship which Access will create whenever you make a query or drop a subform into a form PROVIDED your fields are named the same, as you observed.

Brian Merrick on 9/5/2013: I have a field that I want to calculate. I want to add up the total miles for a company vehicle in a month, how do i perform this function? Can i do a sum of the miles to get the total of the miles of that vehicle?

Reply from Richard Rost:

You can create Form Footer Totals or use the DSUM function.

John Borrelli on 9/19/2013: I've completed through expert 5 and I dont recall seeing where we can set the default value in a form to "new record." I want our employees to enter new customers into the data base only. I realize they could use the new record select but can we not set the screen to open to the new record?
Brian Merrick on 9/19/2013: In this Video, you put in the Product Category number in the ProdutT. What if you have a large database, would you do that for every field?

Reply from Richard Rost:

What do you mean "every field?" You'd need to specify a product category ID for each product that you want to have in a category.

Jacob Swinney on 10/30/2013: I apologize. I set up my Open button for the customer and it populates with the first person in the list...
Ronan Hogan on 12/26/2013: Hi Richard,
I was looking back through some of the earlier Expert lessons (1-3) and trying to fit what I'd learned to a project I've started. I was looking at the course project from the point of view of the user (possibly locked out of the navagation pane) and limited by design to using the MainMenu form to move about the database. I may be missing something but it appears to me to be impossible to add a new customer without manually opening the CustomerF. Is this correct and by design or a little something you missed? Either which way is there a handy way to present, with the click of a button, a blank CustomerF for the user to add a new customer?

Love the classes and I'm looking forward to some of the upcoming seminars.

Regards,
Ronan

Reply from Richard Rost:

You're correct. At this point you need to open the customer form and click on the NEW button. Same problem with orders. I'm going to show you how to make an "add new customer" and "add new order" button soon. This will allow you to NOT have to open those forms first.

DilipKumar T on 12/27/2013: hi.,! Sir.,!
Thanks for Ur Nice tutorial..! I would like to know how do I use IF function for particular field so that result will display automatically for that field Like COUNT .SUM function .! cuz I am building Database using If(score>=52,"HP";( score>=45,"Pass")), Sir if possible show me the example ..! IF function! Thanks

Reply from Richard Rost:

In Access, it's called the IIF FUNCTION and it's covered in Access Expert Level 8. Keep learning. You'll get there soon.

Jim Ogier on 1/20/2014: I would like to be able to be able to populate a callback form with the contact name, as shown in lesson expert 3, but I want to have the ability to go to the callback form from more than one form. I can get it to work if I just use one form but I need the ability to access the callback form from multiple forms.

Jim O

Junias Disin on 2/23/2014: Is there a way on how to filter subform bound combobox from the main form unbound combobox?
Patrick Verbist on 3/1/2014: Why is there a considerable space between the last line in the product subform and the subtotals? In design view, I can clearly see that the 'details' section is nicely minimised and that the subtotals are nicely at the top of the footer section, so where does this extra space come from?
Brian Merrick on 3/29/2014: I have a three fields in my Transactions Table. I want to keep track of the vehicle mileage. I have a Start Miles, End Miles and Total Miles. I want to add the total miles by week, but when i perform that function it gives me an error message,"That the operation is to complex for the function". I get this message when i try to sum the total miles.

Reply from Richard Rost:

Well, how are you performing the calculation? Can't help to fix it if I can't see what you're doing. :)

Brian M on 4/8/2014: For the Daily Mileage, I put the calculations in the TranactionT as: [EndMiles]-[StartMiles] in the Daily miles field. It does the calucations for the Daily MIles.
I need it for the Total Miles for the week. How would i calculate that miles including the dates?

Reply from Richard Rost:

Aggregate query based on the week number.


Reply from Richard Rost:

Aggregate query based on the week number.

Joni Moore on 5/6/2014: Richard... I created a form so I could track all the Sales Commissions that had been paid and the ones that still needed to be paid. I used a Sum Control Source in the Form Footer as you instructed in Video 4 to get the column totals (Total Contracts Amount, Total Expected Gross Profit, Total Actual Gross Profit and Total Commissions Paid). Everything seemed to be working fine last week. Now this week, I open up the form and these fields are not working anymore. All the fields within each of the records are fine, but I just can't get anything in these footer totals. And it's not even giving an error or anything. They're just blank. I checked to make sure they were brought to the front, in case the background was accidentally brought in front of them, but that didn't do it. I checked to make sure the Visible property was set to Yes. What other things could I check to make sure these show up again?
Joni M on 5/7/2014: Richard... I did some research to see if anyone else in the MS Access world had experienced the same thing and one person suggested we simply reboot our computer. I couldn't believe it, but it really was that simple. All my totals are now showing again.
:-)

Reply from Richard Rost:

When in doubt, the first thing you should always try is to reboot. Next thing is to make sure you have the most up-to-date version of the software. Third, remove and reinstall said software.

Robert Rivera on 6/18/2014: expert 3 number 2....how do we set a time to have a reminder trigger the msg box to show its time to make the call?

Reply from Richard Rost:

You would need a TIMER event.

Katheryn Hartig on 8/21/2014: Time Index 15:58: When you added the command button to open the customer form, why did you have to add customerid and make it invisible? couldn't the command button pull from the customid in the form footer?

Reply from Richard Rost:

Yes, I suppose you could pull the value from the Combo Box that's also bound to CustomerID. Good catch.

Stefanie m on 8/23/2014: Richard, I created the callback form just like you showed on 2010 Expert 3. However the form does not allows me to enter a new record. What did I do wrong? Please help. I love your classes by the way.

Reply from Richard Rost:

Sounds like the query under your form is too complex. Did you JOIN two tables together incorrectly?

Stefanie M on 8/26/2014: Yes, I did related my Customer table to my Contact table in a query as you showed. (CustomerID field is linking both tables). And I did again just now but still have the same issue. I know the problem is the query, because when a change the data source of my CallbackF to ContactT the issue disappear. The only thing that looks different from yours, is that the tables (on the query) are joining in a one to many relationship.
Brian Farley on 8/28/2014: Its Reinforcement, not Reputation. Its perfect the way you come at it from different angels and quickly rehash past lessons.

Keep up the great work!

Lynne Finol on 9/11/2014: Hello Richard, While trying to place the open customer button on the CallbackF
The command button wizard section where you "Select the fields and then click the <-> button does not show anything in the CallBackF box.
Any suggestions as to what might be missing? The corresponding point on the video is 16:54.

Robert Stockey on 10/20/2014: How do I arrange for the "New" Record to be at the top of my list in the subform?

Reply from Alexander Hedley:

Robert, if you are talking about the Navigation Buttons along the bottom, these can't be moved.
Instead you could create your own buttons and add the functionality you want.

Lucia Mariothova on 12/7/2014: Dear Richard, I've got problem with "nonQuering" I'm working on 2013version and when I typed Record source as you did it didn't work and showed me some warnings. What to do with it?

Reply from Alex Hedley:

What is the exact error message?

motty green on 1/11/2015: hello,..
in number 2 we made a call back check box and default date, how we do, its should only be entered the date when the box is checked

thank you i really appreciate your wonderful lessons

Reply from Alex Hedley:

You could use an AfterUpdate Event on the checkbox which sets the Call Back Date to =Date()

James Lewis on 1/17/2015: I downloaded the expert level 2 data base from the student database website and couldn't open it because it requires a password. What's the password?

Thanks

Reply from Alex Hedley:

When you're logged in if you go to the My Courses page it is listed in the table there.

Nour Raslan on 2/4/2015: Hi Richard ,,thank you very much ,,you are doing great job ,,
when I run the forms it fill all the screen ,,how can I limet it to the siz of the form only ,,i set Fit To Screen to No ,,and still the same ,,thank you

Reply from Alex Hedley:

This is shown in B2

It's a setting in Options, Overlapping Windows or Tabbed Documents, change it to OW and that should fix it.

Craig Brown on 2/17/2015: Rick:
I like the repetition. Because I "wear many hats", I can't always get to the lessons an often as I would like. The repetition helps me to learn & maintain some continuity. Thanks for all you do.

Richard Schrader on 2/23/2015: Hi I m using Access 2013 and I m stuck in Expert 3 Lesson 4. At 6:34 seconds in I m trying to drag the Fields into the product form and I get the circle with a line through it. I can right click on the fields and add to view but when I do this for ProductCategoryID It wants me to specify a relationship. I do this by selecting ProductCategoryID and setting it for One record in ProductCategoryT relates to many records in ProductT. This sets up a field called ProductCategoryID_ProductCategoryT. At this point I tried to setup a relationship between the two tables but could not figure out how to do it. I continue on with the lesson but I m unable to drag the sub form into the ProductCategoryF. Help!

Reply from Alex Hedley:

In design view I normally drag the subform I want from the navigation pane into the form.
Access is usually smart enough to link on the same field names, if not go to the Property Sheet and in Link Master/Child Fields add them in there.

Richard S on 2/23/2015: Hi- I could not get this to work. I had to manually add the sub form from the toolbox.
Michael Larsen on 3/8/2015: Hi Alex
I have the same problem as Lone.
I'm using a Danish Access 2013.
The expression SELECT * FROM ContactT ORDER BY DateTime DESC does not work.
Access tells me that I need to put a "=" in front of the expression. When I do that I'm told to but parenthesis around the expression, but that doesn't work either.
I also tried getting the field from a seperate query that sorts by date.
That doesn't work either.
How would one write this SQL in Access 2013?

Sincerely Michael

Reply from Alex Hedley:

Hi Michael,
Are you writing this in a Query Builder SQL View?
This looks correct, try building the Query graphically and see if that works, you can then change it to SQL View to see what Access has done

Michael L on 3/11/2015: Hi Alex
Thanks for your mail.
I'm not sure I know by "building the query graphcally".
I've mad a query that does the sorting of the contact dates descending.
I then copy that SQL-code and paste it into the source of the dates field of the Contact sub form.
That doesn't work either.
My SQL looks like this:
SELECT KontaktT.KontaktID, KontaktT.KundeID, KontaktT.Kontaktdato
FROM KontaktT
ORDER BY KontaktT.Kontaktdato DESC

I realize that it's difficult for you to answer something as detailed as this, and if you dont have an answer it's OK with me :-)

Sincerely
Michael



Reply from Alex Hedley:

You can build a Query using the Design View where you pull in the table then select the fields you want etc.

Are you setting it on the Forms Record Source or in the actual TextBox?
Set it as the Form and drag on the Field, it will then show the value from the Query.
Or you would need a DLOOKUP for a specific Field

Michael L on 3/11/2015: Hi again Alex

I know now what I did wrong. You dont have to spend any more time on my question.
I had been writing the sort-SQL in the source of the date field itself, instead of the source of the whole table as I should have.
Sorry to have wasted your time :-)

Michael

Reply from Alex Hedley:

No problem, glad you figured it out and thanks for letting us know.

Chris Needham on 3/25/2015: Hi Richard. I'm in the customer form with the contact sub form. I see my multiply contacts, but when I open show all contacts, only the last contact appears.

Reply from Alex Hedley:

Which query have you linked to that Form
Is it getting a Criteria passed to it?

PIERRE F BOIVIN on 3/26/2015: The major difference between Access 2010 and 2013 is the removal of the pivot table and chart from the 2013 version.

Could you please talk about the ways to get these features dealt with in another way so the work can still get done.

Regards,

Pierre

Reply from Alex Hedley:

Link your Table/Query in Excel and then use the PivotTable feature in there.
You can set up macros/template that has it ready and just refresh when new data has been added to the db.

Chris Needham on 3/26/2015: My contact form, record source is contact. I know they are there but when I stretched the notes field at the bottom, I have to tab to see other contacts.

Reply from Alex Hedley:

Is this a Continuous Form?

Angelika Gutenberger on 6/15/2015: No it is a single form

Reply from Alex Hedley:

Well it will only show 1 record at a time, you would need it to be continuous to show more.

Candyce on 7/28/2015: Richard - Thank you for all of these lessons. I have learned so much. I often go back to review various lessons and find that I pick up information that I had 'lost' or missed. The convenience of having a Course List is just the ticket.
Richard Bray on 8/9/2015: Seeing the Totals in a form footer raises this question in my mind. Can (=Sums of other fields be added together?

Reply from Alex Hedley:

If you give your Total Textbox a name you can then use that in a Formula.

Richard Bray on 8/20/2015: How can I add together Sum= values in a report footer?

Reply from Alex Hedley:

You can rename your Textboxes in the footer then just add them together in the Control Source of another textbox.
=txt1+txt2 etc

Richard Bray on 8/24/2015: If I put a button in a form to return to the Main menu, the Form I was in remains in the tab. Can I get rid of that tab without doing it direct through the x?

Reply from Alex Hedley:

Me.Close

Joe Beniacar on 9/29/2015: Hi Richard.
Thank you for your helpful classes.
Why it is a bad idea for users to have access to the Tables, if they can make the same updates on the forms?
I appreciate the clarification.

Reply from Alex Hedley:

Because you can do a lot of damage to the data via a Table and you don't want users to have that ability.
With Forms you can restrict them to only doing certain things.

Joe Beniacar on 9/29/2015: Hi Richard, I just have a few questions about this lesson:
1)In the ProductT, shouldn't the Quantity field refer to "the Quantity that costs the UnitPrice" (rather than "the Quantity in Inventory") - or, at least, in addition to that QtyOnHand field (for Calculated fields later)?
2)Do you generally make the Master (ONE) Form a Single Form, and the Child (MANY) Form the Continuous Form?
3)In your Form Footer Totals (Tip), you say that the Total Text Box should be "unbound" (that makes sense...) But if we copy the QtyOnHand field from the Details section (whose control is the QtyOnHand field from the ProductT) - shouldn't that make the SUM(that) still a "bound" field - or at least create that new SUM field with just 1 value in the ProductT?

Reply from Alex Hedley:

It's been a while since I've looked at this lesson so may need some fellow students to help out here:

1) You'll want to know how many you have left

2) Depending on what data you want to show but that sounds correct

3) Binding a control makes that value save to the underlying record source (i.e. Table) so if you want to show a Total for display purposes that isn't being stored in a Table then you wouldn't have it bound.

Joe Beniacar on 1/23/2016: Can each ID field show up at most 2x in a database as the P.K. of 1 Table, and the F.K. of another?

Reply from Alex Hedley:

You can have a Foreign Key in as many Tables as you require. You could have many extra tables that hang off CustomerT or need a CustomerID relating to them.

Kevin Gill on 3/7/2016: In lesson Access 2010 Expert 3 part 2 Callbacks. You instruct how to create a button to open customer. How would/do you create a button if the callback is a new contact/client. I understand the theory, and have attempted to SQL it (no joy) and create a button for new record, but still not what I'm wanting. I prefer the button to open the new customer form so that I can enter in this new contact.

Reply from Alex Hedley:

So you want a button on the Main Menu to open the Customer Form in New Record mode?
DoCmd.OpenForm "CustomerF", , , , acFormAdd

Elizabeth Roche on 3/15/2016: Praise 1st: Love this series of tutorials. I know they've been around a while, but I'm new and they are perfect for the new Access programmer.
Now for my Question:

It is odd to me that I can create a sub form (child) to a parent and not need to have the linking field ie. the ProductID and the Primary Key field ProductCategoryID somewhere in the form. Ie. how does Access handling the insert of these records without that information? Thanks. Elizabeth

Reply from Alex Hedley:

If you have the fields in the record source and have the master/child fields have auto linked access handles the rest.

Alexandra George on 3/29/2016: First of all, you are an awesome teacher--I started off knowing nothing about Access and I have learned so much already! I came back to this video to review form footer totals--I am trying to use the average and count aggregate functions. I have a report that displays RateIn, RateOut, and Margin (which is a calculated field) for all of my active consultants, along with FirstName, LastName, and Status. I tried to create a text box for the average margin in my form footer by copy pasting the Margin text box and adjusting the control source to =Avg([Margin]), as well as a count for number of active consultants by doing the same thing only changing control source to =Count([Margin]). When I look in print preview, these fields come up as #Error. Have I done something wrong? Or does it have something to do with either the Margin field being a calculated field or these fields being stored as currency? Thank you for your wisdom!

Reply from Alex Hedley:

Are these controls in the Groupings of the Report?

Alexandra George on 4/5/2016: Ok, I see. This report is grouped (by Project), but I had the count and avg fields in the page footer before, and now I've moved them to the report footer. Now they work as expected!
 

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