|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Interview Questions Text By Richard Rost 
4 years ago
So here's my list of things to make sure you know if you want a job as an Access Developer. Note that if you're taking a Microsoft Access class in school (college, high school, etc.) then you may find these questions on your exams. You are allowed to use these, but insist you quote me. Remember, if you get answers from only one source, it's "plaigerism." If you get them from multiple sources, it's "research." For most of these questions I will give you a short answer (which teachers sometimes prefer) and I'll also give you a link to a video or article on my website which goes into more depth so you can conduct further research there. These questions are in no particular order. Sometimes I started writing one and two more came to mind. Also, these aren't "gotcha" questions. I hate those. I have taken tests before where they ask you questions where the answers are so crazy and out there that even someone like me who has been working with Access for 30 years has never needed to know it. Like "in what group under Access Options can you find the setting for switching to ANSI 92 database syntax?" Come on. If I need a weird setting like that, I'll either hunt for it or Google it. (The answer is the "Query design" group, by the way). That's not something you're going to need on a regular basis. These are actual, real-world questions for actual production databases. When doing research for this video, I Googled "MS Access Interview Questions" and there were a LOT of stupid questions on some of these pages. A lot of them were out of date (asking questions about Web Apps and MDE files). Some of them were just plan dumb. This video doesn't include questions on VBA (Visual Basic for Applications). If you'd like to see me do a VBA Q&A video, let me know in the comments below. QuestionsQ1: What is Microsoft Access? A1: Access is a relational database application that includes a graphical development interface in addition to a desktop-based database engine. It is not a database server, but it can be used as a front-end for a server-based application like SQL Server.
Q2: What are the parts of an Access database? A2: Tables are used to store data. Queries are used to display data in different ways (sorting, criteria, etc.) and to manipulate data (action queries). Forms are used to create an on-screen user environment and for data entry and editing. Reports are used for printing data, or sending data to other people in a printable format (like PDF). Macros are used for automating repetitive tasks, although I really don't like them. I prefer Modules which give you the full power of the Visual Basic for Application language.
Q3: What is the importance of relationships between tables? A3: Tables should be properly relational to minimize errors, increase efficiency, and eliminate duplicate data. This involves the proper use of ID fields (typically Autonumbers) so that each record has a primary key field, and any other table that needs to refer to records in this table stores that ID as a foreign key. For example, you wouldn't store all of a customer's data in the order table.
Q4: What is SQL? A4: SQL stands for Structured Query Language. In the context of Microsoft Access, it is the language in which queries are written. You can have SELECT, INSERT INTO, DELETE, and many other types of queries. This is not to be confused with SQL Server which is a specific database server program by Microsoft.
Q5: What is Normalization? A5: Database normalization is organizing your fields so as to reduce data redundancy and improve data integrity. For example, you wouldn't store all of a customer's data in each of their orders in the order table. Fields like phone number should be stored in the customer table. It's not usually necessary to store them in each order.
Q6: What is Referential Integrity? A6: The most important role of referential integrity is to ensure that child records in a one-to-many relationship are not "orphaned." In other words, you cannot delete a customer as long as he has orders. You can cause those child records to be deleted if you delete the parent record by turning cascade deletes on, however I don't generally recommend it.
Q7: What is a many-to-many relationship? A7: A many-to-many relationship involves three tables. Each record from table 1 can be related to one or more records in table 2. In order to set this up, you require a third table, called a junction table. For example, you may have cars and drivers. Each car can be driven by multiple drivers. Each driver can have multiple cars. The junction table indicates which cars are related to which drivers, and vice versa.
Q8: What's the largest file size for an Access database? How can you overcome this limit? A8: Access ACCDB files are limited to a maximum of 2 GB. However, you can overcome this limitation by splitting your database into a front-end file, which includes queries, forms, reports, etc., and one or more back-end files, which include your tables. Theoretically you're only limited by the size of your hard drive.
Q9: Is it possible to add user-level security to Microsoft Access back-end (table) files? A9: Not really, no. It is possible to secure your front-end (user interface) database so that users cannot change your forms, modify reports, edit your VBA code, etc. You can put a single database password on your back-end table, but it's the same for all users. While you could encrypt (scramble) the data with an algorithm, Access alone does not have user-level security. Users need full read/write access to the back-end database file in order to work with the database. If you need proper table security, the best option is to use a database server, like SQL Server.
Q10: When would you want to upscale your Access database to SQL Server? A10: As previously mentioned, if you need tight data security, SQL Server is much better than Access. Another reason to upscale would be if your database is running slow over a network with multiple users. Each query in Access has to pull all of the records over the network and crunch through them. Whereas with a database server, the query is processed server-side and only the resulting set of records are transmitted over the wire.
Q11: What is the attachment type, and when would you use it? A11: Richard says attachments are evil and you should never use them. Access is not designed to store images, documents, or other types of files efficiently. Instead, store the file in a folder on your PC / Server and save a reference to the path and filename of that file in the database.
Q12: What is a calculated field? A12: While there is a calculated field data type in tables, Richard recommends you don't use them. This violates proper rules of normalization. Instead, use calculated fields in queries. These can be used to calculate values on the fly for results that don't need to be stored in the table. For example, an order line item may have a unit price and a quantity. You can calculate the total with simple multiplication.
Q13: Is it better to store a person's name in one field (Name) or two fields (FirstName, LastName) A13: It is always easier to put multiple fields together than it is to pull them apart. Store FirstName and LastName separately. You can use concatenation in a query to put them together. Also, don't use "Name" as a field name. It is a reserved word.
Q14: What is a continuous form? A14: A continuous form is used to show multiple records on the screen at the same time, one over the other, as opposed to a single form which shows one record at a time.
Q15: What function would I use to add months to a date field? A15: The DateAdd function can be used to add or subtract days, weeks, months, quarters, or years to any date value. For calculations only involving days, you can simply add or subtract that number from the date. For example, to add a week, just add 7 to the date value.
Q16: What property would I likely use to ensure that all new customers start with a CreditLimit of $1500? A16: The Default Value property, in either a table or a form, can be used to set initial starting values for new records. It does not affect existing records.
Q17: You open a form or report and see an Enter Parameter Value prompt. What is a likely cause of this? A17: Access is looking for a parameter value, most likely from an underlying query that the form or report is based on. Either that value is missing, or something is spelled wrong.
Q18: Your user wants to only print a report containing records that he manually selects. What's the easiest way to go about this? A18: Create a Yes/No field in the CustomerT table called Selected. Add a checkbox to the CustomerF form for the user to select the records he wants to print. Create a query with a criteria where Selected=TRUE. Build a report bound to that query.
Q19: What property should I use to ensure that all phone numbers are entered in a 3-3-4 format, such as 239-555-1212? A19: Use the Input Mask property in either the table or form. For that specific mask, I would recommend 000-000-0000 which forces all 10 digits. However, if the area code is optional, you can use 999-000-0000.
Q20: You want to display a field on a form, but it's from a different table than the form is bound to. What function would you use? A20: You can use the Dlookup function to get a value from a different table or query to display on a single form. However, if you're using a continuous form, and you're looking up values for many records, it's usually better to put the tables together in a query first and get the value from that.
Q21: You want to open a report and show only a specific record, such as the invoice for the current order. How would you do it without VBA? A21: Create a query based on the OrderT table with a criteria that gets the OrderID from the currently open form: Forms!OrderF!OrderID. Then build a report bound to that query.
Q22: Why would you want to set the field size of a Short Text field to a value less than 255? A22: While in older versions of Access, this was important to save disk space and keep file sizes small, it's no longer the case. The only reason to limit the size of a Short Text field now is to limit the user's data entry. For example, you can prevent the user from entering more than 5 characters for a ZIP Code.
Q23: How would you write a query criteria to show all orders between Jan 1, 2022 and Oct 1, 2022? A23: While you could use the BETWEEN keyword and say: BETWEEN #2022-01-01# AND #2022-10-01# you have to be careful because if there is a time component in the order date field, you won't see those orders from Oct 1. A better method is to say >=#2022-01-01# AND <#2022-10-02#
Q24: How would you write a query criteria to show all orders from the past 30 days? A24: Use the Date() function, such as: >=Date()-30 Q25: Your client wants to track students, parents, teachers, administrators, and misc employees in his school database. Should you create a separate table for each, or one table? A25: You should use one table, as these are all the same type of data: people. Use a field to indicate which type of person you are dealing with, preferably from a related PersonTypeT table. Q26: Your client deleted OrderID #101 because the customer canceled the order. Now he wants to know how he can get 101 back so there isn't a gap in his Autonumbers. What can you do? A26: First, unless the OrderT table has related records, the number itself is meaningless. Autonumbers are not for you. They're for Access to maintain relationships. Use a custom counter field if the client wants incrementing sequential numbers. However, you can restore a deleted Autonumber by compacting the database (if it's the last record) or by using an Append Query.
Q27: Your client has split his database. The front-end and back-end files are both in a server folder that everyone uses. The database locks up and corrupts routinely. What is a likely reason for this? A27: Assuming there aren't any network or PC problems, the problem is likely caused by users sharing the same front-end file. Every user should have their own copy of the front-end file on their local PC which is attached to the back-end file on the server.
Q28: You have a database with a CustomerF and a subform ContactF that you want to show contacts for each customer, however the subform is showing records for all customers. How do you fix this? A28: Make sure the Link Master Fields and Link Child Fields properties of the subform object are set properly to, in this case, CustomerID.
Q29: You build a form to edit customer records, however when the user presses the TAB key to move from one field to another, the cursor jumps all over the place between random fields. How do you fix this? A29: Set the Tab Order to the desired sequence of fields
Q30: Your client is trying to open the database you just emailed him, however he's getting a security warning saying that active content has been disabled. What do you do? A30: Set up a Trusted Location in the File, Options, Trust Center on the client's PC. Put any databases in that folder.
Q31: Your client has an Excel spreadsheet containing sales figures and he wants to work with that data in Access. What's the best way to go about doing that? A31: If the spreadsheet gets updated regularly and he wants to keep working with it in Access, he can link to the sheet. Access will treat it as a table. However, performance will be better if he imports the data directly into an Access table.
Q32: What setting would you use to ensure that no two customers in your CustomerT table have the same email address? A32: Index the email address field, set to Yes (No Duplicates).
Q33: How do you setup a lookup field in a table? A33: You don't. Richard says they're evil. Table-based lookup fields were added to access to allow novice users the ability to select from a list of options. The proper method is to set up a second related field, store a foreign key in your table, and use a combo or listbox to make the selection on a form. Q34: How would you prevent a user from entering a value in the CreditLimit field of over $1500? A34: Use a Validation Rule, such as: BETWEEN 0 and 1500
Q35: In what section of a report would you want to put something that repeats on the bottom of each page? A35: The Page Footer Q36: What are some of the benefits of Microsoft Access? A36: Access is better at storing and working with data than spreadsheet programs like Excel. It's more cost effective and easier to learn than other professional database systems. Access is ODBC compliant and makes it easy to share data online by connecting to a remote SQL Server. The list of benefits goes on and on...
Got some ideas for questions? Post them below!
|
||||||||||||||
|
| |||
I recently had one of my 
