Access 2007-2016
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 Developer Level 3   dismiss
 
 

< Previous: Access Expert 7

Next: Access Expert 9 >

Access Expert Level 8

Expert Microsoft Access Tutorial - 1 Hour, 55 Minutes
 
 
This Microsoft Access video tutorial picks up where Expert Level 7 left off. In this class we will begin building our Order Entry System. We'll start out by learning how to create Calculated Query Fields. We'll calculate an extended price and sales tax for our order items. We'll learn how to properly Round values to avoid fractional penny errors. We'll build an Order Form and Order Details Form (for line items), and lots more. Topics include:
 
  - Order Entry System
  - Order Form and Details Subform
  - Calculated Query Fields
  - Figuring Sales Tax if Taxable
  - IIF Function (If/Then/Else)
  - Proper Rounding of Values
  - Bankers Rounding
  - Nesting Functions
  -
Final Product and Tax Totals

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 Expert Level 8
Description: Access Expert Level 8
Versions: Recorded with Access 2013. Also use with 2007 and 2010.
Pre-Requisites: Access Expert Level 7 strongly recommended
Running Time: 1 Hour, 55 Minutes
Cost: $24.99


This class picks up where Expert Level 7 left off. We will start by reviewing my solution to the homework assignment you were given in the last class. You were to design a form with a many-to-many relationship showing all of the products sold by a particular vendor (the reverse of the form we designed in the last class).

 

Next, you will design an Order Details table to store information on all of the line items on an order. This will allow you to have an unlimited number of items on each order. We'll create a query to pull in the related details from the products table (price, name, etc.)

 

Next we will learn about Calculated Query Fields. You'll learn how to multiple the unit price and the quantity purchased to determine the total amount to charge for each line item. We'll review all of the math operators, learn about integer division, modulus, the order of operations, logical constants, boolean values, and lots more.

 

Once we know how to calculate values in queries, we can determine the amount of sales tax that needs to be paid. However, we also need to take into consideration whether or not each item is taxable (computer parts are, a gallon of milk is not). So we'll learn about the IIF Function, which is a way to have Access make IF-THEN-ELSE decisions inside a query. We'll also learn about the ROUND function, bankers rounding, nesting functions, and more.

 

Now we can create our Order Details form to allow the user to enter in products and begin to build an order. We'll create a combo box so the user can pick a product. The unit price will be automatically displayed (from the product table). We'll set up relationships between the customer and order tables (so orders will NOT be deleted if a customer is) and between the order and order details table (so the line items ARE deleted if an order is). This is a good example for referential integrity and cascade deletes that we learned in earlier classes.

 

Finally, we'll put everything together into an Order Form. We'll design the form, add a combo box to pick a customer, and make it so that our combo box will display the customer's company name if it exists, otherwise display the contact's first and last names (another example of the IIF function). We'll add a button so we can get to the order form FROM the customer form. We'll calculate totals for everything on the bottom of the form (product total, sales tax total, and a grand order total).

 

This is the eighth class in the Access Expert series. If you are interested in learning how to build an order-entry system, then this is the class where we start developing its foundation. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access Expert Level 8

00. Intro (8:22)

01. Products to Vendors (24:01)
Homework Review
Button to Open Product Form
VendorF Form
VendorProductSubF
Button to Open Vendor from Product
Button to Open Product from Vendor
Give Names to VendorCombo, ProductCombo
Query to add UnitPrice to Junction
VendorXProductWithProfitQ
Add Price and Profit to Vendor SubF

02. Order Details Table (11:42)
Create OrderDetailT
Delete AmountDue from OrderT
Possibility of breaking stuff!
Create OrderDetailQ
Pull in data from ProductT

03. Calculated Query Fields (12:29)
Calculate ExtPrice
String Concatenation Reviewed
Assignment Operators
* / + - \ ^ MOD
Integer Division
Modulus
Order of Operations
PEMDAS
Comparison Operators
< > <= >= <> =
AND OR NOT XOR
-1 or 0 for True False
Boolean Values
Format Property in Query Column
Format True/False
Format Currency
04. Sales Tax (16:32)
IF THEN Statements
IIF Function
Function Review
SUM AVG MAX MIN COUNT
DATE NOW TRIM
SalesTax IIF Function
ROUND Function
Rounding Numbers
Bankers Rounding
Traditional v Bankers Rounding
Nested Functions
Nesting Functions
Fractional Penny Problem
INT Function Round Down

05. Order Detail Subform (14:44)
Create OrderDetailF Subform
Product Combo Box
Lock UnitPrice Field
Order Relationships
Customers to Orders
Do NOT Cascade Delete
Ordsrs to Details
DO Cascade Delete

06. Order Form (22:18)
Create OrderF
Customer Combo Box
Show Name if Company Null
Is Null vs IsNull()
Add OrderDetailSubF
Auto Default CustomerID
Auto Default IsTaxable
Form Footer Calculations
Sum of ExtPrice
Sum of SalesTax
Grand Total

07. Review (5:06)

 


 
Keywords: Order Entry Form, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, vendors, products, order details, line items, integer division, modulus, if/then statement, IIF function, calculate sales tax, rounding, round function, bankers rounding, nested functions, int function, order form, order details form, totals
 
 

Student Interaction: Microsoft Access Expert 8

Richard on 6/30/2013:  Microsoft Access Expert Level 8 is 1 hour, 55 minutes long and focuses beginning to build our Order Entry System. We'll start out by learning how to create Calculated Query Fields. We'll calculate an extended price and sales tax for our order items. We'll learn how to properly Round values to avoid fractional penny errors. We'll build an Order Form and Order Details Form (for line items), and lots more. Topics include: - Order Entry System - Order Form and Details Subform - Calculated Query Fields - Figuring Sales Tax if Taxable - IIF Function (If/Then/Else) - Proper Rounding of Values - Bankers Rounding - Nesting Functions - Final Product and Tax Totals Click here for more information on Access Expert Level 8, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 7.
James Gray on 7/1/2013: Richard,

Perhaps in one of your advanced classes you can deal with the topic of units of measure as it relates to how you stock, order and price inventory. I am dealing with those issues now in my ambulance database. We use items typically in each units but must purchase in case or odd lot quantities. As you know, that affects how we purchase, receive and stock our supplies. Very interesting and complex issue but fun to figure out how I make that an automated process. I would appreceiate any advice you might have on that topic. Thanks for your help as always.

Alex Hedley on 7/1/2013: PEMDAS - I was taught this as BODMAS.

B Brackets first
O Orders (ie Powers and Square Roots, etc.)
DM Division and Multiplication (left-to-right)
AS Addition and Subtraction (left-to-right)


Reply from Richard Rost:

Whatever works for ya. :)

Bruce Reynolds on 7/2/2013: Could you use the Single data type in place of the Double data type for the Quantity field? If so, what's the difference?

Reply from Richard Rost:

Yes, you could. Single offers less precision and a smaller range of values but saves a some space. I always say if you're not sure, stick with Double. Unless space (size of the database) is a concert, go with Double. If you start running into the 2 GB file size limit, then you may want to consider scaling back to Single. I don't remember the exact sizes off the top of my head (I covered them in Access Beginner 3, Lesson 1) but you can Google them (or watch that lesson).

Bruce Reynolds on 7/11/2013: Does Access have a product function like Excel? For example, could you enter the formula ExtPrice: Product(Price, Quantity) in this example?

Reply from Richard Rost:

Not that I'm aware of. You would have to write a custom function to do it.

Bruce Reynolds on 8/5/2013: What is the equivalent of bankers rounding in Excel?


Reply from Richard Rost:

Excel, unfortunately, doesn't support Bankers Rounding. See this Microsoft article for details.

Bruce Reynolds on 8/5/2013: You should set up an Access Seminar that does nothing but go over all of the functions available in Access (or at least the 100 most used functions). This would be cool.

Reply from Richard Rost:

I plan on doing this in the Expert series videos coming up, much like I did for Excel in it's Expert series.

Bruce Reynolds on 8/5/2013: Changing price will affect current orders! Affect, not effect. Affect is a verb, and effect is a noun.

Reply from Richard Rost:

You are correct, sir. Did I type it wrong somewhere? Sometimes I get going so fast and I forget to proof read. Other times I use voice dictation software and it messes up (sure, blame the voice rec!)

Jim P on 9/6/2013: I am having trouble adding totals in the form footer. I have tried multiple times and keep getting #Error in the text box. What am I missing? I have checked text box size, names and formatting.

Reply from Richard Rost:

Jim, without seeing your formula, I can't tell you what's wrong. What do you have in the Control Source, EXACTLY?

Elaine on 9/13/2013: Richard, tell your son that our heads are not as young as his. I for one love the repetition, the junction table finally clicked, and I have been struggling with understanding it for a while. Even when I did the 2003 courses. Thanks a little repetition never hurt anyone.

Reply from Richard Rost:

Will do. :)

Jim P on 9/14/2013: I figured it out... I was trying to add a sum of a calculated form control to the form footer. The problem corrected itself when I moved the calculation to the query. Funny thing was, it also goofed up all the other form footer totals I had built. Thanks for the reply. I have learned so much from your courses, wish I had found these about 8 years ago.
Jim Pigeon on 9/16/2013: Richard, I am trying to calculate the following 3 Query fields --

BaseRate/lb: Round(IIf(([FlockSalesT]![baserate]+[npvdiff])>[FlockSalesT]![bottomrate],([FlockSalesT]![baserate]+[npvdiff]),([FlockSalesT]![bottomrate]+[FlockSalesT]![BaseRateAdj])),4) Fixed Format Result = 0.0524


BasePay: [baserate/lb]*[goodlbs] Currency Format Result = $62,011.84



BasePayFt: ([basepay]\160000) Currency Format Result = $0.00

The last of the 3 BasePayFt is calculating the pay per square foot which is a fixed 160,000 sqft. The result should be $0.39. I have changed number formatting to Currency on all 3 fields and the result continues to be returned as $0.00. Any suggestions?

Maurice C on 9/18/2013: Richard,
Say I've got a very large list of products and using a combo box for selection is becoming too awkward to use (for whatever reason), could I create a button next to the ProductID field that opens up a continuous form showing all my products and I can select the product from there. Do you foresee any problems with this method?

Reply from Richard Rost:

Sure. I have that for my course list in my customer database since my list of courses is so long. You may also want to consider putting your products into categories and using cascading combo boxes.

John Borrelli on 10/3/2013: In your examples in Lesson 14, branched from this lesson 8 you used two seperate tables. I have one table with, along with other part related information, has Part number and Serial number. I want my form to display the selected part number and serial number. My combo box, with Part Number and Serial Number works fine for the Part number.Regardless of what i select in my combo box my Text box for serial number only displays the first serial number associated with that part. Can I get it to display the serial number selected with the part number.
John Borrelli on 10/3/2013: With each lesson you discuss reading the Forums associated with that lesson. I of course only read the exerpts displayed with each lesson. Today I followed your instructions (after 16 lessons) and read the entire forum, which lead me not only to many issues i am encountering but also to a slew of free tips techniques and mini lessons....THANK YOU. ok next time I'll follow instructions(maybe).
Candice Garman on 10/3/2013: When you add the calculated fields in the form - SUMExtPrice, SUMSalesTax, SUMExtPrice+SUMSalesTax does that value save in a table or query or not at all. Is there value in doing these calculations in a Query instead of the Form?

Reply from Richard Rost:

Those values are not saved anywhere, just displayed for you. WHERE you calculate them does matter sometimes. If you have a HUGE dataset you may find performance is a little better if you calculate them in a query. It will load a little slower initially, but then the results will scroll faster. In a form, each page view is calculated as you scroll. Loads faster, but slower on the scroll.

John Borrelli on 10/6/2013: thank you for taking the time in Expert 8/5 for going back to the relationships and showing them. Please do that more often. All the issues i am having in my work database are from pp table design and incorrect relationships.
John Borrelli on 10/7/2013: In lessons 7 & 8 you talked about calculated fields in Queries and reports and they are working well. thank you. However, I need to create a report that only shows the totals. My boss wants a summary of sales showing only the totals ... My reports all show the sales details then sum them. How do i get only the totals into a report?
Bonnie S on 10/31/2013: Richard, near the end of lesson 5, after you have enforced referential integrity for Customer ID in customerT and OrderT, and have enforced referential integrity and set cascade delete for OrderT & OrderDetailT (on OrderID), at timeslot 14:00, when you try to add another record you get an error message. I have worked this through more than 2 times, and then started fresh again from the end of Lesson 4, but I do not get that error message. Rather, it allows me to create the new record. I can't figure out what I have done wrong, since I have marked and saved the referential integrity as you showed us. What other setting could be allowing this? Thanks for your help. Bonnie, as always, appreciating such good teachings.
Annette Richard on 12/11/2013: Following through Video 5 using Access 2010. I applied the relationships described from minute 11 to min 13. I went back to the order detail table and am still able to pick a product without an orderID. In the OrderDetail Table, there is no zero. Any ideas as to why I can still do this?
John Miller on 12/11/2013: Having a problem with the "Enforce Referential Integrity". I have followed the video (starting at the 11:30 mark) to the end. I have done this several times and I keep coming up with the same result, it allows me to add line items in the "OrderDetailF" with out giving a warning that I can not do this. It will also not give a warning directly in the "OrderDetailT". I have even looked at the HandBook for this lesson and it still comes up the same. What is wrong? one other piece of info I am running Access 2010 not 2013.
John Miller on 12/11/2013: Mr. Rost, I had a problem with "Referential Integrity" not working. Expert 8 Lesson 5 TIME INDEX 11:30. I reviewed all of the previous lessons from lesson 2. What I finally came up with was that the default values in any number field, i.e. foreign Key Fields, except the Main Key field have to have a default value set to 0 (zero). Not sure if you cover this or not in any previous lessons or not. If you did then I missed it and apologize for bringing it up.
Nicko on 1/28/2014: Richard
Great Lessons. Have not started building database just watching videos
Have a lot of contact data info in spreadsheets. A problem I have is contact name (Full Name) is one cell is their a function in excel I can use to break this down into two cells Frist Name Last Name
Nicko

Alex Hedley on 2/15/2014: If you pull your data into Access you can do it there.

Tip
http://www.599cd.com/tips/access/131007-update-query-separate-first-name/

Full Lesson
http://www.599cd.com/site/courselist/access2013/expert/x14/

Pru Hewett on 6/27/2014: Hi Richard. I'm trying to create an IIF function where, if the data in my column headed IncomeExpense is Expense, the amount in my Amount column is changed to a negative figure. Following your Expert Level 8 lesson 4, I have typed in: IIF([IncomeExpense]=[Expense],[Amount]*-1). When I run it, a popup box asks for the parameter value of IncomeExpense. Where am I going wrong please?

Reply from Richard Rost:

Well, is IncomeExpense a TEXT field that says "Expense"? If so, you'd need:

IIF(IncomeExpense="Expense",Amount*-1,Amount)

If you put something like that in [brackets] then you're telling Access it's a FIELD NAME. You don't need the brackets if your field names don't have spaces in them (which is what I teach in the very first Beginner lessons). However, if you want the VALUE of the data in that field, you CAN'T use brackets.

I'd recommend a Yes/No field. Make YES an income and NO an expense. Then you could just say:

IIF(IncomeExpense,Amount,Amount*-1)

The field will evaluate to TRUE or FALSE based on it's value and you'll get either the Amount or it's negative.


Brian Farley on 9/9/2014: Something worth mentioning, probably in reference to the previous questions.

If you have chosen the default value in the OrderID field in the OrderDetailT to NULL or empty, like I did, it will allow a record without a OrderID, so leave it default to 0


Adi on 9/12/2014: Hello Richard,
this is how I learn about IIF from your Access Expert 8. I'd like to share with you and the class.

We use decimal comma in general arithmetic. So the "IsTaxable" value 8% is written as = 0,08 instead of 0.08

The problem is, if I write IIF([IsTaxable],[ExtPrice]*0,08,0) then access will warn me for syntax error.
So I replaced 0,08 with 8/100.

IIF([IsTaxable],[ExtPrice]*8/100,0)
well it works partially for the TRUE-part. The FALSE-part show NULL (blank) instead of 0 (zero).

When I recheck the function, somehow it turns into
IIF([IsTaxable],[ExtPrice]*8/100)
See? the False-Part simply disappears. {IIF(Condition,True,False)}

I then realize, that Access assumes the last {,0} not as the {False-Part}, but as a part of {100,0} = 100.

So i modified the IIF function into
IIF([IsTaxable],8/100*[ExtPrice],0)
Now it works perfectly as expected, although I'm not sure if it is the ideal solution for the case.
Another solution that works is, when I put 0,08 inside a bracket IIF([IsTaxable],[ExtPrice]*(0,08),0)

What will be the more elegant solution for this case? Do you have any tips about how MS Access handles comma and point as decimal marks? I'm afraid, my database will behaves differently on the other half of the world if there is no agreement about this simple issue :D

Reply from Richard Rost:

I really know very little about how other currencies are handled in Access. My experience is limited to the US and Canada. If putting the currency value inside of parentheses works, I'd just stick with that.

IIF([IsTaxable],[ExtPrice]*(0,08),0)

Seems like a reasonable solution to me.

Clay F on 11/25/2014: Hello, On Expert8 Lesson5, at the 12:59 mark, I was unable to enforce referential Integrity with the OrderID from OrderT and OrderDetailT, the error message says "Access can't create this relaionship and enforce referential integrity", then Data in the table DetailT violates R.I. rules. Please help, I tried adjusting the Default value of the OrderID but to no avail. Thanks!

Reply from Alex Hedley:

Check that all the Records contain data in your JOIN Fields.

Clay F on 11/25/2014: Problem Solved...I had a dirty record in the OrderDetailF from class and the dirty record didn't allow the Enforce RI. THANKS, carry on!
Michael on 11/27/2014: In Access Expert 8 Lesson 1 at 4:25, you add a command button to the ProductSubF to open the ProductF. When I attempt to replicate this using the command button wizard, Access does not display the fields for the ProductSubF.It does however, display all fields for the ProductF. As a result, I can't link the ProductSubF to the ProductF using the wizard. Interestingly, the record source is the same for both forms - Product T. Also interesting, when I tried going the opposite way - putting the button on the ProductF to open the related record on the ProductSubF, Access displayed all fields for both forms and allowed me to make the link. Any idea why Access would "see" all fields in both tables in one instance, but not in both? I ran into this same problem when trying to link the ProductVendorSubF to the VendorF. I was able to make the connection between the VendorProductSubF and the ProductF. Thanks in advance for any insights you can provide.

Reply from Richard Rost:

Were you working on ProductSubF while you still had ProductF open in design view? It's more reliable to CLOSE ProductF and then work on ProductSubF by itself. I'd have to recreate my steps from the video to tell you exactly what happened, but that's how I generally recommend you do it if you have problems. Could have just been a glitch too.

Michael on 11/27/2014: Richard, as a work-around solution for the issue with the command button wizard problem that I ran into, I went into the Build Event property and changed the code.

For anyone that runs into this same issue, here's what I did:

I created a command button on the ProductSubF to open all records. This at least allowed me to get a working command button onto ProductSubF. Remember, I couldn't open specific records because the ProductSubF fields were not appearing in the left pane in the command button wizard.

I then accessed the code behind the command button throught the BuildEvent property.

I compared this code to the code for a working command button that would open a specific record on a form from a command button on a subform.

I deleted all of the code for the command button on the ProductSubF and brought over the working code for the other forms.

I made name and reference changes where needed. Here's the code:

Private Sub OpenSelectedProductButton_Click()
On Error GoTo Err_OpenSelectedProductButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ProductF"
stLinkCriteria = "[ProductID]=" & Me![ProductID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenSelectedProductButton_Click:
Exit Sub

Err_OpenSelectedProductButton_Click:
MsgBox Err.Description
Resume Exit_OpenSelectedProductButton_Click

End Sub

I would still be interested to know why the fields aren't/weren't visible in the left pane of the command button wizard.

M.


Reply from Richard Rost:

This may work, but you shouldn't have had to do it. This is way beyond Expert 8 material, and I wouldn't have shown it in class if this was required. Something else went wrong while you were creating your button.


Patrick Hoffmann on 12/16/2014: Hi Richard,
In Access Expert 8 Lesson 1 at around 4:25 I ran into the same issue that Michael posted about on 11/27/2014, when trying to do the Command Button to open the ProductF. I chose the "Open the form and find specific data to display", but did not have any of the fields display for the ProductSubf and had all the fields display for the ProductF. I did have the ProductSubF open by itself at the time.
Thanks for any advice you can give me

Pat

Reply from Alex Hedley:

Hi Patrick,
Which version of Access are you using.
People have had this issue when using 2007, if that's the case I'd advise upgrading if you can.
Could you try with the SubF not open and see if that helps

Patrick H on 12/19/2014: Hi Alex,
Yes I've done a little research online and found that this seems to be a 2007 issue, so I am going to upgrade to office 365. I've tried it a number of different ways and it's very consistently not working.
Pat

Reply from Alex Hedley:

Unfortunately 2007 was extremely buggy, I've kept away from it as much as I can.
Hope the upgrade helps

Nicholas S on 12/27/2014: I cannot get the subform to only show the items for that particular order. It shows all of the details on all of the orders, what is going wrong?

thanks

Reply from Alex Hedley:

Nicholas if you click on the subform in Design View, in the Property Sheet there is link Master/Child Fields.
Check these are filled in using your JOIN Field

Maged Al Shaer on 2/22/2015: I would like to know how to make record is required by the form not by the table and if I didn't fill in the required field it gives me another color with message


Reply from Alex Hedley:

You could use the following Tip to Check a Form for Blank Controls.

Nour Raslan on 3/31/2015: Hi Richard you are doing great in the every lesson. I have Problem with IIF i entered same as you did i have this error .
SalesTax:IIf(istaxable,ExtPrice]*0.08,0)
i get this message :
"YOU ENTERED Invlid character or Comma,
Or You entered without Surrounding with Qutations Mark".
please corect me about this.

Reply from Alex Hedley:

Looks like you missed an opening square bracket "["

SalesTax:IIf(istaxable,ExtPrice]*0.08,0)
=>
SalesTax:IIf(istaxable,[ExtPrice]*0.08,0)

Nour Raslan on 4/4/2015: Dear Alex
i tried this:

SalesTax:IIf(istaxable,[ExtPrice]*0.08,0)
i copy and paste the your statement still givs me the error and pointin on the ","before the opening "[" I tried that more than 4 times the same error
thank you
Nour

Reply from Alex Hedley:

Have you seen this Tip?

=IIF(Condition, Value If True, Value If False)
=IIF(State="NY", "Yes", "No")

Can you wrap the true condition in ()
Are the Fields named the same in your Table/Query?

Spiros Poulis on 6/9/2015: Dear Sir, , why you does not include in the subform the foreign key OrderID, in order to connect the parentID and the ChildID, when the 2 forms are connected? Which field links the two forms?

Reply from Alex Hedley:

The OrderID exists in the RecordSource of the Form as it is in the Query, it doesn't always have to be on the Form but it must be in the RecordSource.

Nour Raslan on 7/5/2015: Hi Richard
How Can I start the main menu from desk top "out of Access"

Reply from Alex Hedley:

Can you elaborate?

James Childers on 8/11/2015: I am trying to add up all the payments from a customer and then deduct them from the total loan amount the customer owes to show a current balance. I have created a query that adds up the payments but when I try to calculate this it only will deduct one payment from the total loan amount. Can you tell me how to fix this please?
Thank you,
Jimmy

Reply from Alex Hedley:

What do your Queries look like?

Brian Merrick on 8/18/2015: I am making a product database for my company Vinny's Workshop. It consist of Product name, type of wood and hardware table. I created the product Table, Wood table and a Hardware table. I already made the hardware subform. I am stuck on the wood table and the product table. Instead of making three separate table can i put the wood table into the product table?

Reply from Alex Hedley:

Are you wanting to store 3 lists? 1 containing product, 1 wood and 1 hardware?
Just repeat the process you did making the first form with the other two.

John Newton on 10/10/2015: Hi, In Lesson 1 of 2013 Expert 8; Richards mentions that sometimes fields are not update-able when Forms made from Record Sources from Queries with Many-to-Many relationships. Why is that and is there a work around?
I guess I'm having trouble with Many-to-Many in general, but specifically adding records.
Any help or guidance would be greatly appreciated.
FYI I have viewed all Expert Courses 1-30

Thanks
John


Reply from Alex Hedley:

Do you need all the fields in your Query?
You could just remove the join that is causing the record set not to update and work with that instead

See an explanation here.

Ryan Weemhoff on 1/21/2016: When I use =SUM(ExtPrice) as suggested in Expert 8.6 (~18:45 min) my form displays "#Error". Please comment.

Reply from Alex Hedley:

Do all ExtPrice have a value?

Ryan Weemhoff on 2/5/2016: Thanks for your reply. I figured it out. I was putting the sum control on the order form instead of the suborder form. However, I am trying to create an OrderListF (record source: OrderT; displaying orderID, CustomerID, OrderDate, and Total), but I cannot get the total to work. My control source is OrderDetailF.Form!Total but my return is "#Name?"

Reply from Alex Hedley:

Do all Total values contain a number?

Ryan Weemhoff on 2/9/2016: Following up on my initial follow up post. I realize I was jumping ahead to access 12.1 with an OrderListF question. I now know the correct way to do it using a query with an aggregate sum function. However, I'm simply experimenting on my own as I work through the lessons. I thought setting the source of an OrderListF to the OrderT and then setting the control sources for the CustomerName and Total to the OrderF would work...but it didn't. Not sure why though, relative to all the internal logic of access/database design. If there is an easy explaining please provide. Otherwise feel free to dismiss question and forego publishing these comments in the forum. Thanks.

Reply from Alex Hedley:

You can only set Controls to be bound to a value on the underlying record source

Eric Michalek on 3/19/2016: Storyboard.
Up to Expert 7, it seemed good enough to understand each step individually. The fundamentals now understood, I m having a rough time keeping up with the concepts of what the lessons are trying to accomplish.
The classes have been excellent in showing how things are done. But for the expert level classes, I need more focus/detail on what each video is trying to accomplish. To explain, I watch the entire lesson to understand what the objective was. And then, I watch again to understand the process needed to accomplish that objective. Finally, I m ready to watch the lesson to see how everything works together.
In the end, I m re-watching each lesson several times before I m ready for the next. I shouldn t complain. After all, I really like your lessons. They are really informative and fun to watch. Much better than listening to a college professor drone on. I just wish I didn t have to re-watch so many times for the full benefit of your classes.
Some of my college professors had solutions for this. They made use of storyboards , a pictorial representation (not an outline) but a picture of what is to be accomplished. Then the picture is annotated/marked up to show what is needed to make it work.
This would be very helpful if incorporated into your video, even better, the handbook for each lesson. I would certainly buy all those handbooks. Then I could use it as a tool before watching, and as a reference while watching the first time.


Reply from Alex Hedley:

Do you have an example you could share i.e. an image?
Take a course you've done and produce the storyboard, upload it to one drive or something similar and share the link.
I write most of the handbooks and happily take any suggestions for improvements.

Virginia Mergl on 5/13/2016: Hi, I am having a problem with a query to make up my invoice prices, I have only one record on my tables and when I run the query it shows 42 times the same record. I can not figure out what I have done wrong. Thank you.

Reply from Alex Hedley:

It's probably a cartesian product.
How is your JOIN set up.

Virginia Mergl on 5/14/2016: I posted a question yesterday but I figure it out myself. Because I forgot a relationship between PetID and PriceID, the query was showing one pet with all price codes (42). Now that I figure it out it is working properly. Sorry about that, thank you
Mike Holmes on 5/21/2016: This is probably a basic questions on junction boxes but I need to ask anyway. I have created a junction box with Owners and Sites. I have formed relationships in the junction T with the Owner IDs and Site IDs. When I do a Query to get information from the OwnerT and SiteT, I bring the * in for the Owner T and fields from the SiteT. However, when I run the query, there is no information about the Owners or Sites.

My question is, do I have to physically input information again from the OwnerT and SiteT so that the OwnerSiteQ will show the information?

Reply from Alex Hedley:

You have OwnerT info and SiteT info
In your JunctionT you will need OwnerID and SiteID so 1 | 1, 1 | 2 etc then in your Query join OwnerID from OwnerT on OwnerID in JunctionT and the same for Site

 

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