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 tryin [more...]

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/2 [more...]

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. [more...]

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. Remembe [more...]

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 [more...]

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 [more...]

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 [more...]

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 t [more...]

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 nee [more...]

Add Your Questions or Comments