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 More... What's New? Tips & Tricks Access Index Access Tips Excel Index Excel Tips Waiting List

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

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 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 firstO 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.0524BasePay: [baserate/lb]*[goodlbs] Currency Format Result = \$62,011.84BasePayFt: ([basepay]\160000) Currency Format Result = \$0.00The 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: RichardGreat 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 NameNicko
 Alex Hedley on 2/15/2014: If you pull your data into Access you can do it there.Tiphttp://www.599cd.com/tips/access/131007-update-query-separate-first-name/Full Lessonhttp://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
 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, , , stLinkCriteriaExit_OpenSelectedProductButton_Click: Exit SubErr_OpenSelectedProductButton_Click: MsgBox Err.Description Resume Exit_OpenSelectedProductButton_ClickEnd SubI 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 mePatReply 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.PatReply 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?thanksReply 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 Alexi 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 youNourReply 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 RichardHow 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,JimmyReply 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-30ThanksJohnReply 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 insteadSee 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
 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 infoIn 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
 Ida H on 9/14/2018: I am having a problem with Enforcing Referential Integrity. I have watched the video Expert 8 Lesson 5 Several times and can not seem to find my problem. I have set my relationships per the video for the CustomerT, OrderT and OrderDetailT. However, when I hope my OrderDetailForm it still allows me to add a record even though it does not give a OrderID. I am running Access 2010. I looked at the forum and someone else had the same problem but there is no solution posted. I know once the Order form is created and I add the OrderDetailF as a subform it will not much matter. However, based on video I should be getting an error message at this point.

 You may want to read these articles from the 599CD News:
 8/25/2018 NEW: Access Dev 9, 10, 11 8/25/2018 Microsoft Access Developer 11 8/25/2018 Microsoft Access Developer 10 8/25/2018 Microsoft Access Developer 9 8/23/2018 Access Dev 11 is ONLINE 8/17/2018 Access Dev 10 is ONLINE 8/15/2018 Access Tip: Search Form 8/15/2018 Access Tip: Locked v. Enabled 8/15/2018 Access Dev 9 is ONLINE 7/31/2018 Microsoft Access Developer 8

Learn

 Access index Excel index Word index Windows index PowerPoint index Photoshop index Visual Basic index ASP index Seminars More...
Customers

Online Theater
Insider Circle
Student Databases
Change Email
Info

Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Help

Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services

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
Contact

Live Chat
General Info
Support Policy
Contact Form
Email Us