Course List - Access 201

Student Interaction: Microsoft Access 201

Richard on 1/1/2007: Creating relational databases, relating customer and contacts table, relational combo boxes, much more. 68 Minutes.

Josiah�Whitman on 11/19/2007: I've signed up for 201, 202 et al and have found that the database files haven't appeared in the "help" folder within your downloads. Is this still a feature?

Richard Rost on 11/21/2007: Josiah, we moved the files. When you play the very first video of each course (if you're online) you should see a link to all of the video files: http://599cd.com/access/studentdatabases

�Joe on 2/12/2008: Does it matter if you use the CustomerID from the ContactT table or the CustomerT table? This is in regards to your course 201.

Richard Rost on 2/28/2008: While building a simple contact query? No it does not matter which one you use and they have the same value.

Joe on 2/28/2008: I've taken yoour database that we created in the lessons and made it a front end/back end system where only the tables are in the back end. If I have all of the forms and queries in the front end and I want to make a check box field called "approved" on a form named LeadDetailF which uses LeadDetailT but I want it to do something like this: IF the box is checked THEN 3 different fields in the LeadDetailT cannot be updated by anyone. I'm trying to lock down 3 fields in the table because there will be a dozen people in my office that will have the front end db on their PC so I can't lock down forms, or can I? If so, can you tell me the code?

Richard Rost on 3/1/2008: That can certainly be done, but you'll need a touch of programming. Starting in Access 301 I cover VBA programming. You can use an AfterUpdate event to say, "IF this field is checked, THEN make these three fields disabled." It's programming that goes right in the form field.

�vatsal sanjiv on 3/15/2008: sir i want to create a formula n i m not able to do so.. for instance i purchased a policy on 01/01/2001 n that will get mature on 01/01/2010 the amt of premium which i have to pay will be on 01/01/2001... 01/01/2002... 01/01/2003... . . . 01/01/2010 i want to make such a table which automatically generates dates(containing year also) and it must have another feid of status (paid/unpaid) eg 01/01/2001 paid 01/01/2002 paid 01/01/2003 paid 01/01/2004 unpaid 01/01/2005 unpaid 01/01/2006 unpaid . . . 01/01/2010 unpaid n its must also have a filter

Richard Rost on 3/19/2008: What you basically need to do is pre-fill a table with payment amounts for each policy. This is not easy to do, and you'll need to learn how to use something called a RECORDSET in order to do it (or you could use a loop with an Append query). I'll be covering Recordsets in an upcoming lesson. I'll keep your question on-hand and try to use it in class.

�Eva on 3/31/2008: I have one Shop called Cottage Stores I want to give it a Month's orders free how do this in a Query Please. I love this site

Richard Rost on 4/1/2008: Eva, I would need to know much more about what you're doing. What do you mean by "give it a month's orders free?" Do you mean that you have one customer who should get all of his orders free for a month? And you want to do this in a QUERY? I would do this in the order form (later on in Access 301+) when a product is added to the order... have it's price automatically come in as ZERO if the "GiveFreeOrders" value is checked on. In a query, you'd probably just have to use an IIF statement to check the dates, an assign the total to ZERO if you're in the right date range. Very interesting question however.

Richard Rost on 4/24/2008: It's as if they read my mind... Access 2003 includes a step in the Combo Box Wizard that asks you what field you want to SORT your combo box by. Good addition!

Judith�Koester on 5/24/2008: I have more than one client per project. When I run a query, I get duplicate project names since the project and client tables are linked. How do I eliminate the duplicate project names?

Richard Rost on 5/24/2008: Judith, what exactly do you want? Just a list of projects without the client names? If that's the case, just use the project table. If you generate a query with projects linked to clients in a one-to-many relationship, you're going to get duplicate project names. Now, if you're generating this for a REPORT, then you can use a SORTING & GROUPING LEVEL to only show the project name once and then a list of clients underneath it. Remember this from Access 104? If I'm wrong and you're trying to do something different, please let me know.

Kevin�Dore on 5/25/2008: I have created a database on my PC at home and I want to email it to my office PC or save it to disk so that I can use it at the ofice. I have tried many times to do this without success. How do I go about this please ?

Richard Rost on 6/3/2008: Kevin, all you should have to do is simple email the MDB file to your other PC (or burn it on a CD) and then open it up on the other computer. That's it. All the info you need is in that one file - unless you have external linked tables.

Walter�Pohle on 8/16/2008: I do not have Company names but word like to fill in with Last&First Name in the Customer Field .Can this be done on form or do you have to use a Query

Richard Rost on 9/8/2008: Walter, you want to put the FirstName and LastName fields together in a form field without using a query? You can do it by setting the ControlSource of an UNBOUND text box to: =FirstName & " " & LastName But your field will NOT be editable.

Nathan�Wittmann on 9/11/2008: I have created a form and have noticed that if I start to put info into the form and tab through without finishing the form it auto saves the info with out using the save record button i have placed on the form. I want to use this button to do multiple validations, but that wont work if it will auto save when you tab through. I want it to just loop through and have the user save only if they select the button. So in short is there a way to turn the autosave feature off? Thanks for your help!

Richard Rost on 9/11/2008: Nathan, if you open up the form's properties there is a CYCLE property. Change that from All Records to Current Record. That will make the TAB order return to the first field on the current record instead of moving to the next record. As soon as you close the form or move off the record, the data is saved to the table. You can control what happens then with EVENT programming. For example, you could put code in the form's BeforeUpdate property that would let you CANCEL the user saving the record if they don't enter valid data - this is much better than just handling it in a button. I cover the BeforeUpdate in my Access 305 class.

Robert Yarbrough on 11/1/2008: Two questions. Using the form developed in the class, how do you add an entry (note) for an existing customer, not just write over an existing entry? Also, how would you run a report for a 90 day follow up on contacts from only the latest entry date? thanks

Richard Rost on 11/7/2008: Robert, you would need to create a separate, related NOTES table (very much like our Contacts table) where you could have multiple notes for each customer - with a date. In fact, you could just use the Contacts table to store notes. That's perfectly fine. Now, to make a report for a 90-day followup, just make a query where your date criteria is

David�Bender on 2/3/2009: Hi, for CustomerID @ 09:04 what if you had hundreds if not thousands of data entry�s how would you identify the customers, I�m sure not one at a time.

Fran�Raymond on 3/6/2009: When to use a lookup tables?? In my database, schools apply for ecoschool status each year. They achieve bronze, silver or gold. Some years we have a few schools apply, other years we have many. I'm thinking i should have a main table (tblSchoolMain) that links to a status-by-year table (tblStatusYear) and the Status field and Year field in this table should each be lookups to a lkpStatus and lkpYear. I will now have 4 tables - does this sound right?

Richard Rost on 3/9/2009: Fran, that sounds fine to me. As long as everything is properly related, it's usually BETTER to design the database with every 'thing' in a distinct table.

�Raul on 6/21/2009: Mr.Rost: I would like to make a formula and the result be save in a field. So I can make an invoice for customers. Maintenance Fees=(1)*65+9Lots-10*24 How can I do this? Thank you for your attention to this matter. Raul Pendas

Richard Rost on 6/25/2009: Raul, if you want just a normal calculated field, you can do it with a query. That's easy. We will cover that in Access 220. I also have a tutorial on it in the Tips & Tricks section. Now, if you want that value to calculate AND be stored in a table field (perhaps so you can edit it), you have to do some more work. You need to use some VBA programming (or a macro - but I prefer the VBA). I cover it in Access 303 because it's a little more advanced, but again I have a Free Tutorial explaining how to do it. There is a LOT more to it than I cover in the Free Tutorial, of course. Get the full Access 303 class for complete details.

Jonathon�Mun on 7/14/2009: I'm having trouble linking data between two tables. First table is a Customer table and the second table is a customer issues table. Both tables will be populated monthly from an Excel table import. If I were to enter each issue for the customer manually, it would be easy to select the proper Customer. But because the tables are being imported with Customer data and not the Customer ID, I cannot easily create the relationship I need.

Richard Rost on 7/16/2009: Jonathon, without some piece of data to RELATE the two tables together, you're going to have a hard time. You need a customer ID, customer number, or even something unique like a phone number where you can piece the data together. What information in your CustomerIssues table relates the Issue back to the Customer?

�Carolyn Quinn on 7/17/2009: This is great! However, what if someone else is using the database and do not know the customer ID? I would need to always have a handy customer ID list showing so they will know what CustID goes with what ContactID. That is why a subform would be used. right? Now i need to understand subforms or should i develop a query with the info i want to show up and make my form based on that query?

�Carolyn Quinn on 7/17/2009: Okay - don't answer my first question. It was too early to ask. I need to know something else. I don't like to have the auto numbers so close to the same thing. I want to change the Contact number to start a little higher up the ladder of numbers. How do i do that? Thanks

Richard Rost on 7/21/2009: Carolyn, that's why we'll use COMBO BOXES for picking a customer. Your end user will never have to know an ID. They'll be able to pick from a combo box.

Richard Rost on 7/21/2009: Carolyn, you shouldn't have to worry about what your IDs. Your end users don't even have to see them. They're completely for Access and relating your tables together. HOWEVER, if you want to "kill" a bunch of Autonumbers, just insert a bunch of blank records and delete them. If you want to artificially inflate your Autonumbers so your first customer isn't ID 1 then just insert a record, copy and paste it 1000 times, then delete it.

�Cindy McPherson on 7/24/2009: Is there a way to change the data in a table field from the decription lookup field to the key number field? I have 800+ records & do not want to change them all manually.

Richard Rost on 7/25/2009: Cindy, I would need more information on this to help you, but you can use an UPDATE QUERY if you need to move values from one field to another.

�Gary on 7/29/2009: I changed my replaced auto numbers with new column and deleted the old column. now when I add new info i get id numbers that conflict with the old autonumbers. can i fix it?

Richard Rost on 7/29/2009: What do you mean they conflict with your old Autonumbers? Let me see if I understand you... you had some records (lets say IDs 1 to 10). You deleted that ID field and then added another one. Now you're being reassigned new IDs that just happen to relate to other records in your database (someone already has ID 3 for example). In this case, you're going to have to just WASTE a bunch of Autonumbers. Insert about 100 blank records, then delete them. That should waste IDs 1 to 100. Of course you SHOULD have deleted all of the related records along with those IDs in the first place, but that's OK. Does this help?

Manan Mem on 9/27/2009: hi. i have a simple table of 3 fields (all info are filled in already) barcode, product name and price. I need to form or query where the clerk can type in (not selecting from list like combo box) the barcode and have the product name and price displayed. thanks

Richard Rost on 9/27/2009: Manan, in order to do this, you need to learn how to use the DLOOKUP function, which is a little more advanced. I cover it in my 300-level Access classes. Here's a sample tutorial.

�Bill Donegan on 11/3/2009: When I was inserting combo boxes I got them right, first pass, about 50% of time.I believed the combo was "actually" storing data. But it doesn't really.It shows the second field selected after the ID field in the "combo-design" field drop-down box. I'm wondering how it does that, and discards any other selected fields used in the combo itself. So if it doesn't work first time I just delete and build anew. Slowly coming along!!

Richard Rost on 11/3/2009: Bill, I'm not sure I understand what you mean. The combo box wizard shouldn't "discard" any of the fields you select. I've never had this problem before. Are you sure you have all of the updates and service packs for your version of Access?

�Bill Donegan on 11/3/2009: Richard, I was just relating my experience with building various combos and I shoulda taken your advice that I've since come across .i.e. go over the stuff several times, rather than trying to apply immediately...I just wondered if anyone else was having similar difficulty grasping combos..they appear simple..my error was in believing that by selecting a value in the rows shown in combo that I was actually storing that data...not just the ID...I now know that all I'm storing is the ID....the appearance of the e.g. CompanyName field data in the combo might lend the impression that this is actually stored somewhere...Now, I know that's not so....you cover it in 202 when you demonstrate that the RowSource property is actually "CustomerID" and it's really the ID thats in the field...could be confusing....And arising from another posting, I have updated all my Office to ensure that I'm running the most up to date release of 2000. But still getting the greyed out formatting buttons in Form Design....random...maybe will discover cause sometime. Many Thanks for your help (and your courses). Bill

�Bill H on 2/22/2010: I have an existing database with a �projects� table. I have it working with a one-to-many relationship with a client table. I have multiple projects with the same client. I need to update the projects table to include an additional one-to-many relationship to include several project managers working on the same project, i.e., architect, mech. engineer, elec. engineer, etc. Can you please point me in the right direction with the necessary videos I need to watch? (Please don�t say all) I have watched Access 201 and 203. Thanks.

Reply from Richard Rost:

All. :) Ha ha ha. Seriously, you just need to do the exact same thing again. You already have a one-to-many from Clients to Projects. Now you need a one-to-many from Projects to Managers. So, set up a Managers table. Add a ManagerID to your Project table. Create a subform in your Project form (or even a simple listbox) that show the Managers. The design layout is up to you. The only problem you might run into is that you can't have a subform on a CONTINUOUS form. So if you already have a main form with your client, and then a subform showing his projects, you can't have another subform inside of that subform. I would just have a CLIENT form with a listbox showing projects. Double-click on the listbox to open another form showing that specific project. That form could have another listbox showing the managers, etc. Sound good? You should already know how to do all of this if you've successfully built the previous relationship OK.

Add Your Questions or Comments