Interview Questions
By Richard Rost
4 years ago
36 Microsoft Access Interview Questions & Answers
I recently had one of my Developer students email me asking if I could help him prepare for a job interview he has coming up for a position as an Access developer. I know he's completed most of my Access Developer series, and based on the types of questions he's asked me in the past, I know he's more than qualified. But it got me to thinking about the questions I would ask if I were hiring someone as an Access Developer.
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.
If you can think of any more good interview / test questions let me know. Also if you'd like to see me make a video like this about VBA, Excel, or anything else I teach, post them in the commments below.
Links
Written Version
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, exam questions, test questions, quiz, practical questions, interview questions
Intro In this video, we will talk about 36 common interview questions and answers for Microsoft Access, covering topics that are useful for anyone preparing for an Access developer job or taking a test in school. We will go through must-know subjects like database structure, table relationships, normalization, SQL, security basics, splitting databases, and common mistakes to avoid, as well as best practices for handling forms, queries, and reports. Each question is explained with brief answers and direct links to supporting resources, making this an ideal prep guide for Access job interviews or exams.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we are going to talk about interview questions. I've got 36 interview questions and answers for Microsoft Access, or these might also be things that I might ask you in school on a test or quiz.
I recently had one of my developer students email me, asking if I could help him prepare for a job interview. He is coming up for a position as an Access developer. Now, I know he has completed most of my Access developer series, and based on the types of questions he has asked me in the past, I know he is more than qualified. But it got me thinking about the questions I would ask if I were hiring someone as an Access developer. So, this is my list of 36 things to make sure you know if you want to get a job as an Access developer.
Note that if you are also taking a class in school, college, high school, or whatever, then you may find these questions on your exams. You are allowed to use my answers, but you have to quote me. Remember, if you get answers only from one source, it is plagiarism. If you take them from multiple sources, it is called research. Be sure to do your research on other sites as well.
For most of these questions, I am going to give you a short answer, which teachers sometimes prefer instead of writing three paragraphs. I will also give you a link to a video on my website or an article that goes into more depth so you can conduct further research. So, I will give you the short answer here and a link to where you can learn a lot more about that topic.
These questions are in no particular order. Sometimes I start writing one and two more questions come to mind. Also, these are not gotcha questions. I hate those. I've taken tests before, like online tests to see if you know what you are talking about, like for Excel or Word. They ask you crazy questions that are so out there that even someone like me with 30 years of Access experience never needed to know that thing, especially like hunting through the menu questions.
If you have seen those before, you know, like, "In what Access options group can you find the setting for switching to ANSI 92 database syntax?" That is something you will very seldom ever need. If you do need something crazy like that, just Google it. By the way, the answer is the Query Design group, but again, that is meaningless.
So, these are actual real-world questions for actual production databases. When doing the research for this video, I googled Microsoft Access Interview Questions and there were a lot of stupid questions that came up on some of these pages. A lot of them were out of date. A lot of them were asking about web apps, which have been gone since 2013, and MDE files, which are Access 2003. Some of them were just plain dumb.
Finally, I did not include any Visual Basic questions in this video. If you want to see me put together a separate video on VBA questions, I would be happy to do so. Post a comment down below and let me know. So, this all has to do pretty much with just normal Access database development, not VBA.
Are you ready? I am going to give you the question first, read it, and give you a second or two to answer. Pause the video if you want to. Then I will flash the answer and read that, and you can see if you knew what you were talking about.
Here we go.
All right, question one is the simple one, and everyone always gives a different answer for this. But what is Microsoft Access? Take a second. Think about it. Pause the video if you have to. Got your answer?
Access is a relational database application. "Relational" is important because it means relationships between multiple tables, as opposed to a flat file database or something like Excel. It 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. Basically, it gives you all the tools to make your database and to work with the database locally or on a small network, and it can be used to connect to a bigger back end database.
Want to learn more about my "What is Microsoft Access?" video? You will find a link to this and all the other videos I am going to mention down below in the description below the video.
Question two. What are the parts of an Access database?
We have got tables that are used to store data. Queries are used to display data in different ways. You can sort, add criteria, that kind of stuff, and manipulate data with your action queries—update, append, delete, those kinds of things.
Forms are used to create an on-screen user environment for data entry and editing and your menus and stuff like that.
Reports are used for printing data or sending data to other people in a printable format like a PDF file.
Macros are used for automating repetitive tasks, although I pretty much do not ever use them.
Modules give you the full power of the Visual Basic for Applications language.
Those are the major components of an Access database. Yes, there is other stuff, but these are the big six. Want to learn more? Parts of a database.
Question three. What is the importance of relationships between tables? Why do you want them? What are they good for?
Rules should be properly relational to minimize errors, increase efficiency, and eliminate duplicate data. This involves the proper use of ID fields, typically auto numbers, so that each record has a primary key field and other tables that need to refer to the records in this table store that ID as a foreign key.
There is lots more information on relationships in my relationships video. This is a big one. If you are going for an Access job or taking a course, learn relationships. It is the most important thing in building a good Access database.
In fact, I used to wait until my expert level classes to really even start going over relationships. I just changed that. Now I start covering it in beginner level two because it is that important.
Question four. What is SQL?
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 other types of queries.
Now, this is not to be confused with SQL Server, which is a specific database server program by Microsoft. SQL is a language. SQL Server is a program—a database application, a database server specifically. There are different kinds of SQL. For example, Access's SQL is slightly different from SQL Server's SQL, which I hate. There are some differences, and it drives you nuts. But you just have to learn it.
Want to learn more about SQL with Access? Watch this video. I talk about the basics of writing SQL statements.
Question five. Define the universe. Give three examples.
Now I am just kidding, even Preecarthing said was dumb.
All right, next question. The real question number five. What is normalization?
Database normalization is organizing your fields so as to reduce data redundancy and improve data integrity. For example, you would not store all of the customer's data in their orders in the order table. The fields like phone number should be stored in the customer's table only. It is not usually necessary to store them in each order.
So you have one customer who has 15 orders. Each one of those orders does not have to have all of the customer's information. You generally find people doing this with Excel spreadsheets.
There are some exceptions. I talk about them in my normalization video. For example, if you want to track where an order was shipped, you might copy the customer's address into each order. But that is an exception rather than the rule. Sometimes you may even want to denormalize your database for increased performance. But generally, normalization is a good thing. Here is a video that talks about it.
Question six. What is referential integrity?
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, although I generally do not recommend it. Be very, very careful with cascade deletes.
Referential integrity means if you have an order and that order has line items in a separate table, as you should do for order detail items, you cannot delete the order until you first delete all the line items, or referential integrity will prevent that. But if you turn cascade deletes on, if you delete the order, it will delete all the line items too, which can be dangerous. Be very careful with this setting. You can learn more about referential integrity here.
Question seven. What is a many-to-many relationship?
A many-to-many relationship involves three tables. Each record from table one can be related to one or more records in table two. 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, but each driver can have multiple cars. The junction table indicates which cars are related to which drivers and vice versa.
Many-to-many relationships right here: you have drivers, their cars, and a junction table in the middle.
Question eight. What is the largest file size for an Access database, and how can you overcome this limit?
An Access ACCDB file is limited to a maximum of two gigabytes, and that is a lot of space. However, you can overcome this limitation by splitting your database into a front-end file, which includes your queries, forms, reports, and that kind of stuff—your interface, basically—and one or more back-end files, which include your tables. Theoretically, you are only limited by the size of your hard drive. You can take multiple tables and break them up into multiple back-end files.
I have my database set up that way. I have my customer table in one file and my order table in another file because each of those are getting pretty big. If you spread them out like that, you can attach your database to pretty much as many back-end table files as you want.
Want to learn more about splitting your database? There you go.
Question nine. Is it possible to add user-level security to Microsoft Access backend table files?
Not really. No. It is possible to secure your front end, your user interface database, so that users cannot change your forms, modify reports, edit your VB code, and so on. You can put a single database password on your back-end table, but it is the same for all users. Access has not had user-level security in a long time. I think 2003 was the last version that actually included user-level security.
While you can encrypt or scramble the data with an algorithm, Access alone does not have user-level security anymore. 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, your best option is to use a database server like SQL Server.
I have several videos that talk about securing your database. Here is one: Simple Security. Start with this one. Here is one about setting a database password. Then I have a couple of seminars. One is my Security Seminar. It goes through everything you need to know to lock down your Access database. I also have this one called the Encryption Seminar, where I teach you how to actually scramble that data in your tables using an algorithm. So, for example, if you want to store something sensitive, like credit limit, salary information, or credit card numbers, you can scramble that data so even if someone gets into your tables, the information is meaningless.
Question 10. When would you want to upscale your Access database to SQL Server?
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 the database server processes the query server side and only the resulting set of records is transmitted over the wire.
If you have a multi-user database set up and it is running slow, you might want to consider upgrading the back-end to SQL Server. What this means in a nutshell is, let us say you have got 100,000 customers in your database, and you want to see a report showing only the customers from New York. With an Access-only solution, you have to pull all 100,000 records down over the network wire and your local computer will crunch through them all and display just the customers from New York. With SQL Server, you tell the server, "Hey, send me just the customers from New York," and it will then just transmit to you the, let us say, 100 or so that are from New York. It is much, much faster that way when you are processing over a network.
If you want to learn more about SQL Server, check out this seminar. It is my Access SQL Server Online. I show you how to set up an SQL Server online on a web server and connect your Access database to it so anybody, anywhere in the world using Access can get a hold of your database.
What is the attachment type and when would you use it?
I say 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 computer, your PC, or your server, and save a reference to the path and file name of that file in the database using a short text field.
I know there is a lot of debate about this, but Access is not a file storage database. It does not store files, images, or those kinds of things very efficiently at all. It bloats your database size and you are better off storing them in a folder on your server or on your computer and then just storing a path of them because you can always open them up from the database with the click of a button.
I have a whole page full of Access evil stuff you should not do or at least do sparingly. I will put a link to this down below in the link section as well. This is constantly evolving.
If you want to learn how to properly display images in your forms and reports, watch this video. You do not store the images in your table in your database. You store them in a folder and you store the file name and path to that in your database and they will display just fine.
If you get interviewed and get asked this question, you tell them I said so. If it is on a test for your school, you tell your professor I said so.
Yes, you can use the attachment field if you have really, really small text files or a Word document, like a resume—sure, fine, if you have to. But I strongly recommend against it.
Question 12. What is a calculated field?
While there is a calculated field data type in tables, I recommend you do not use them. That violates the proper rules of database normalization. Instead, use calculated fields in queries; it is confused to calculate values on the fly for results that do not need to be stored in the table.
For example, an order line item may have a unit price and a quantity, and you can calculate the total for that line with simple multiplication. You can learn how to do that here.
Do not save calculated values in your tables, folks. Very, very rarely do you have to do that. Only if you are doing a gigantic set of data and you want to speed up the rate at which those fields are sorted and searched, you might then want to store a calculated value in your table. But it is very rare that you will actually do that.
Question 13. Is it better to store a person's name in one field called "Name" or two fields, "FirstName" and "LastName"?
It is always easier to put multiple fields together than it is to pull them apart. Store first name and last name separately, and you can use concatenation in a query to put them together. It is always easier to take first name and last name and smash them together than to pull those things apart.
Also, do not use the word "name" as a field name. I see this all the time—a field name called "name." It is a reserved word. Here is a video on concatenation, which is putting two strings together. I will also include a link down below to the reserved words list, which is in our glossary on the website. This is maintained by Alex and is a great job. These are all the words you should not use as your field names. Check it out.
Question 14. What is a continuous form?
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 just one record at a time. This is a continuous form: you have one record after the other. This is a single form: you only see one record at a time. Yes, I know some of these pictures are blurry. These are the older videos that I did. I did not take high-res images of the slides. I have to do it again.
Question 15. What function would I use to add months to a date field?
The DateAdd function can be used to add or subtract days, weeks, months, quarters, or years to any date value. You can also use it for time portions, too, like hours, minutes, seconds. For calculations only involving days, you can simply add or subtract that number from the date. Remember, in Access, the number one equals one day. For example, to add a week, you can just add seven to a date value. If you want to add an hour, add one twenty-fourth. It is fractions of a day. Here is a video on DateAdd. Here is a video that talks about date math: how to add those odd days, date fractions, and so on.
Question 16. What property would I likely use to ensure that all new customers start with a credit limit of $1,500?
The Default Value property, in either a table or a form, can be used to set the initial starting value for new records. It does not affect existing records. Bonus question: What would you use to go through and change the values for all of the existing records? You would use an Update Query—an Update Query. Here is a video on how to use the Default Value property and here is where you can learn more about Update Queries.
Question 17. You open a form or report and see an "Enter Parameter Value" prompt. What is a likely cause of this?
If you are not expecting it, I get this one all the time. People email me this constantly or post it in my forums. This 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. I would say nine times out of ten, it is spelled wrong. I get people all the time that send me databases with table and field names that look like this—nonsense—and they wonder why they have it wrong in a query. Maybe they typed it out for a criterion or something and they spelled it wrong. Nine times out of ten, it is spelled wrong. Be careful with that.
I have a whole video dedicated to this "Enter Parameter Value" problem. It happens that often. If you do not know what a parameter value is in a query, go watch this one. This is how you set parameter queries properly with a prompt using the criteria of a field in your query. If you do not know what query criteria are, you should not be getting a job as an Access developer. Go watch this video first.
Question 18. Your user wants to only print a report containing records that he manually selects. What is the easiest way to go about this?
Create a Yes/No field in the CustomerT table (or whatever table it is based on—assuming it is a customer report). Add a checkbox to the customer form for the user to select the records he wants to print. Then create a query with a criterion where Selected equals True, and build your report bound to that query. That is the easiest way to do it. Make a Yes/No field in the table. Check on who you want to display or not, and then make a query showing only selected records and then use your report on that. Here is a video that shows you how.
Question 19. What property should I use to ensure that all phone numbers are entered in a 3-3-4 format, such as 239-555-1212?
That property is called the Input Mask. You can put it in either a table or a form. For that specific mask, I would recommend 000-000-0000, which forces all 10 digits. However, if the area code is optional, if your business is mostly just local, then you can use 999, where zero is required and 9 is optional. You may or may not type in the 9. Want to learn about input masks? I have a video about it. Want to see it? Here you go.
Question 20. You want to display a field on a form, but it is from a different table than the form is bound to. What function would you use?
For example, let us say you have a customer table and a sales rep table, and you want to pull some information from that customer's sales rep and display it on the customer form. 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 are using continuous forms and you are looking up values for many records, it is usually better to put the tables together in a query first and get the value from that. Try to avoid using any of the domain functions like DLookup, DMax, DSum in a continuous form if you have lots and lots of records. That is going to slow your database down. In that case, use a query, pull them together, and display it that way.
Here is a video on DLookup. It is the granddaddy of all the domain lookup functions. Learn this one first.
Question 21. 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?
Here is a video explaining how to get a value from an open form, like getting that OrderID. This video goes over that exact circumstance: how to open a report to a specific record. In this video, I show you how to do it both with and without VBA. With VBA is actually easier—it is just one line of code. You have to know the right line of code.
Question 22. Why would you want to set the field size of a Short Text field to anything less than 255?
While in older versions of Access, this was important to save disk space and keep file sizes small, it is 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 five characters for a zip code.
Back in the day, when I first got started in Access 2.0 back in, like, 1994, hard disks were a lot smaller. At the time, Access did not really optimize disk space as good as it does today. If you reserved 100 characters or 255 characters for every first name field, there was a lot of wasted space because Access did not crunch all that down. You had to compact your database and you would get some of it back, but it was not anywhere near as good at maintaining disk space as it is today.
Today, it is not a problem. In fact, I routinely leave my field sizes at 255 unless I have a reason to keep them shorter. So there you go.
How would you write a query criterion to show all orders between January 1, 2022 and October 1, 2022, assuming you want records from both January 1 and October 1?
While you could use the BETWEEN keyword and say that, you have to be careful because if there is a time component in the OrderDate field, you will not see those orders from October 1 after midnight. A better method is to use greater than or equal to and less than your two end dates. For example, in my order field, I want to know what time of the day an order came in so I can track my busy times. So it will say, for example, the order date is July 1, 6:42 pm. Well, if I do a query that says show me all of the records between June 1 and July 1, it will include July 1 at midnight, but it will not include that 6 pm order. That is why I say "greater than or equal to January 1" and "less than October 2." You do not get October 2 at midnight, but you get everything less than that.
This, by the way, is called the ISO date standard—year, month, day. I use that because I have customers and students all over the world, and this is an unambiguous date format that everyone can get behind. So here is a video to watch using the BETWEEN keyword when you are searching between dates and how it might give you wrong values and why. Here is a video you can go watch if you want to learn more about that ISO date format that I use.
Question 24. How would you write a query criterion to show all orders from the past 30 days?
For the last 30 days worth of orders, you would use the Date function, such as "greater than or equal to Date()-30". It takes the system clock, whatever your computer is set to, and says, "Show me all the records that are greater than or equal to Date()-30," which is 30 days ago. Go watch that video on date math to learn more about this. Remember, there are two functions: Date and Now. Now is the current date and time together.
Question 25. Your client wants to track students, parents, teachers, administrators, and miscellaneous employees in his school database. Do you create a separate table for each one or one table to store them all?
I recommend using one table, as these are all the same type of data. They are all people. They all have pretty much the same fields—first name, last name, address, phone number. Most of the information about all these people is the same. You can use a field to indicate which type of person you are dealing with, preferably from a related person type table (one is students, two is parents, three is teachers, and so on). We do not want to keep multiple tables with the same type of stuff. Here is another question I get all the time.
Question 26. Your client deleted order ID number 101 because the customer canceled the order. Now he wants to know how he can get that 101 back so there is not a gap in his auto numbers. What can you do?
First, unless the OrderT table has related records, the numbers themselves are meaningless. Auto numbers are not for you. I have a whole video on this. They are there for Access to maintain relationships. Use a custom counter field if the client wants incrementing, sequential numbers (Order 1, Order 2, Order 3, and so on).
However, you can restore a deleted auto number by compacting the database if it is the last record, or by using an Append query. There is a trick. The bottom line here is that auto number does not matter. Those could be random numbers for all you care. It does not matter to you. It does not matter to the customer.
Now, if the customer has related records—so you accidentally, let us say, delete a customer record and he has got orders in the system and you want to somehow get that customer number six back—you can shoehorn it in there using an Append query. I have a whole video that shows you that trick.
But first, watch this: auto numbers—not for you. They are for Access, for relationships. Do not worry and obsess over what those auto numbers are. If you want sequential numbering—if you want your orders to be Order 1, Order 2, Order 3—then use a sequential number. Compacting and repairing, I teach about it in this video. This is good if you delete records at the end of your table.
So, you are messing around, you add a bunch of temporary records, you have got customers one through a hundred in there, and then you add 50 records just goofing around. Now you are up to 151. If you delete those records and compact your database, it will reset the auto number back to what it should be: 101.
But again, bottom line, it should not matter. It does not matter. Here is a video on Append queries. This is getting up to that trick. If you do not know what an Append query is, watch this. This is the video that teaches you how to use an Append query to shoehorn a record into a missing auto number slot. Again, you only use this if a disaster happens and you accidentally delete a record that you have related records for.
Question 27. 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?
Assuming there are not 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. When people share the front end across the network, you are much more likely to corrupt your database.
I ran into a company one time; they had about 15 people. The database was properly split, but both files were in the server folder and everyone was opening them. It just does not work like that. Everyone should have a local copy of the front end on their hard drives and they attach that to the back end.
I know it is a pain to run around and update all of those front ends every time you make a design change. That is why I wrote the Access Updater, which does it for you. You click the button and it just copies your front end to everyone else's computers. Next time they connect to the database, it will download the updates automatically.
Question 28. You have a database with a customer form (CustomerF) and a subform (ContactF); you want to show contacts for each customer. For example, every time you talk to them, you note you talked to them on the phone about this or stopped in the office today for that. However, the subform for contacts is showing all the records for all the customers. How do you fix this?
Instead of just seeing Jim Kirk's contacts, you are seeing everyone's, including Spock's and Bones', all their contacts. The way to fix it: make sure the Link Master Fields and Link Child Fields properties of the subform are set properly—to, in this case, CustomerID. That is the property that says, "Match this field in the subform with this field in the parent form." This will show only the contacts for CustomerID 6, for example.
Here is my video on subforms.
Here is an easy one. 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?
You set the Tab Order to the desired sequence of fields. There is a little button up on the ribbon. You click on it to set the Tab Order. You click "Auto Order," and it will automatically order them for you, top to bottom, left to right. Or you can manually set them if you want to.
This video shows you how to set the Tab Order, what a Tab Stop is, and how to cycle through the records on the same form or go to the next one.
Question 30. Your client is trying to open the database you just emailed him. However, he is getting a security warning saying that active content has been disabled. What do you do?
You set up a Trusted Location. It is under File - Options - Trust Center on the client's PC. You have to walk him through it over the phone or connect in remotely or do whatever you have to do. Any databases in that folder will bypass that security warning. Here is a video on it: how to set up a Trusted Location.
Question 31. Your client has an Excel spreadsheet containing sales figures and he wants to work with that data in Access. What is the best way to go about doing that?
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 like a table. However, performance will be better if he imports the data directly into an Access table. You can take an Excel sheet and link to it from Access, and Access will treat that sheet just like a table, but especially if it is a big sheet or it is over the network, it is going to run slow. The performance will not be great. So it is best to import that into a table, even a temporary table, and then work with it in Access as a table. If you get another one in a couple of weeks, you just import that one.
I talk about all that in this video about importing. Here is another video I have on creating a linked table to an Excel sheet from Access—linking to it.
Question 32. What setting would you use to ensure that no two customers in your CustomerT table have the same email address?
You are going to index the Email Address field and set it to "Yes (No Duplicates)." This is something I do in my database, for example. When you set up an account, that is your email address, and it is unique to every user.
Want to learn more about indexing? Watch this video.
Question 33. How do you set up a lookup field in a table where you can look up a value from a list (otherwise, there is a drop-down list)?
You do not. They are evil. They are on my evil Access list. Table-based lookup fields were added to Access to allow novice users the ability to select from a list of options. So you can pick, for example, your state from a list of the states.
The proper method is to set up a second related field, store the foreign key in your table, and use a combo box or list box to make that selection on a form. Table-based lookup fields are evil. They were added there for beginners. Do not use them.
Question 34. How would you prevent a user from entering a value in the Credit Limit field of over $1,500?
I know we talked before about the default value; this is to limit them, to not be able to type in a value of, say, $2,000. For this, we would use a Validation Rule, such as "Between 0 And 1500." You can learn more about validation rules right here.
Question 35. In what section of a report would you want to put something that repeats at the bottom of each page?
This is an easy one. That is the Page Footer. Remember, the Page Footer goes on the bottom of each page, and the Report Footer goes on the bottom once of the entire report, but it actually might display above the bottom page footer, so there are some tricks with that.
Remember, we do not use page headers and page footers in forms, only reports, because the page footer of a form is designed for when you print forms, and we do not print forms. Forms are for use on the screen. Reports are for printing out or making PDF files. In a form, you are always going to use the Form Header/Footer and never the Page Header/Footer.
Question 36. Last question. This is a very vague question. It is open to interpretation. But what are some of the benefits of Microsoft Access—the major benefits? Why would you want to use Access as opposed to something else?
Access is better at storing and working with data than spreadsheet programs or flat file databases like Excel. It is more cost effective and easier to learn than other professional database systems. Also, Access is ODBC-compliant (Open Database Connectivity), and it makes it easy to share data online by connecting to a remote SQL Server.
The list of benefits goes on and on. I think literally I have got like 30 or 40 of them. There is my video on the benefits of Access—lots and lots of different reasons why. But in a nutshell, what most employers or professors are looking for is: Access is definitely better than Excel when it comes to working with data. Excel is good for analyzing small bits of data or putting charts together, that kind of stuff. But Access is much better for storing and working with large sets of data than a spreadsheet is. It is relational, so you can work between different tables; we have talked about that before.
As far as comparing Access to bigger database server systems like SQL Server or Oracle, Access is more cost effective and easier to learn. There is not as much of a learning curve. You can more rapidly deploy your application in Access than you can with SQL Server.
What a lot of big companies will do is start prototyping in something like Access, and then, once they get it working on a small scale, then you can scale it up to a bigger server-based solution. Access is a perfect fit for all of that stuff.
One more. You ready for the bonus question?
Question 37. It is for extra credit. Here we go. Who is the best Starfleet captain? That is Picard, of course. Everyone knows that is Picard. Come on.
So there you go. There are your 36 interview questions and/or test, quiz, or school questions that I think you might get asked if I was interviewing someone for a position. Those would be the questions I would ask, and I would specifically look for answers to things like the attachment types, storing images in databases, and the things that you should not do with Access.
If you have any ideas for additional questions, send them to me. Post them in the comments down below. I will put them together for my part two of this. If you would like to see me do one of these interview question type things on VBA, then let me know. Post them in the comments down below.
If you want me to do something like this on Excel or Word, I am more than happy to. But there you go. There are your Access interview questions.
I hope you learned something and I hope you get your job when you go for your interview. I will see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me, and you will get one free Expert class each month after you finish the Beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full Beginner courses for every subject, and one free Developer class each month after you finish the Expert classes. These are the full-length courses found on my website, not just for Access too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond Sponsor and have your name or company name listed on a Sponsors page that will be shown in each video as long as you are a sponsor. You will get a shout-out in the video and a link to your website or product in the text below the video and on my website.
But do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more, and they will always be free.Quiz Q1. What is Microsoft Access?
A. A relational database application with a graphical development interface.
B. A spreadsheet program primarily for calculations.
C. An operating system for database servers.
D. A programming language for websites.
Q2. Which is NOT a major component of an Access database?
A. Tables
B. Queries
C. Slideshows
D. Forms
Q3. Why are relationships between tables important in Access?
A. They allow for storing all data in one table only.
B. They enforce data integrity, minimize errors, and eliminate duplicate data.
C. They increase the number of tables needed.
D. They make queries unnecessary.
Q4. What does SQL stand for?
A. Simple Quick Language
B. Structured Query Language
C. Source Quality Logic
D. Server Query List
Q5. What is database normalization?
A. Combining all fields into one table for easier access
B. Organizing fields to reduce data redundancy and improve data integrity
C. Creating duplicate data in multiple tables
D. Deleting all extra tables
Q6. What is the main purpose of referential integrity?
A. To prevent users from entering data in forms
B. To ensure child records are not orphaned in a one-to-many relationship
C. To allow all users full access to backend tables
D. To remove all constraints from tables
Q7. How is a many-to-many relationship implemented in Access?
A. By linking two tables directly
B. Using a lookup field
C. With a third junction table relating records from two tables
D. By combining both tables into one
Q8. What is the file size limit for a single Access ACCDB file?
A. 512 MB
B. 1 GB
C. 2 GB
D. Unlimited
Q9. How can you overcome the ACCDB file size limit?
A. Use only short text fields
B. Split the database into front-end and multiple back-end files
C. Export all tables to Excel
D. Use only queries and forms, no tables
Q10. Can you set user-level security on Access backend table files?
A. Yes, for each individual user
B. No, only a single database password is possible
C. Yes, through advanced encryption
D. Only in Access 2019 or later
Q11. When should you consider upscaling your Access database to SQL Server?
A. To add images easily
B. When you need increased security and better performance over a network
C. When working alone with small data sets
D. To reduce the number of tables
Q12. How should files such as images and documents be stored when using Access?
A. Using the attachment field in the table
B. Store them in folders and reference their paths in the database
C. Directly in short text fields
D. In calculated fields
Q13. What is a calculated field in Access, and where is it best used?
A. A number that is typed manually each time
B. A field that stores a formula in a table
C. A value calculated on the fly in a query, not stored in the table
D. A text field displaying fixed values
Q14. How should you store a person's name in your database?
A. In a single field called "Name"
B. Separate fields for first name and last name
C. In a long text field called "FullName"
D. As part of the address field
Q15. What is a continuous form?
A. A form showing only one record at a time
B. A form that prevents scrolling
C. A form displaying multiple records on screen at once
D. A form designed only for printing
Q16. Which function is used to add months to a date field in Access?
A. AddMonth
B. DateSum
C. DateAdd
D. PlusMonths
Q17. What property sets a default value for new records in Access?
A. Indexed
B. Default Value
C. Validation Rule
D. Data Type
Q18. What is the most common cause for an "Enter Parameter Value" prompt you did not expect?
A. Network issues
B. Missing or misspelled field or parameter in the underlying query
C. Insufficient permissions
D. Misconfigured table relationships
Q19. How can a user print a report containing only manually selected records?
A. Use a Yes No field with a checkbox to select records and filter the report
B. Print the entire report and discard unwanted pages
C. Manually write SQL code for each selection
D. Use the Format Painter tool
Q20. What property ensures all phone numbers are entered in a specific format (e.g. 239-555-1212)?
A. Validation Rule
B. Default Value
C. Input Mask
D. Data Macro
Q21. What function allows you to display a field on a form from a different table?
A. DLookup
B. Sum
C. PrintField
D. FindRecord
Q22. Should a Short Text field's size be set to less than 255 characters mainly to save disk space?
A. Yes, always
B. Only in legacy versions; today it's mostly to limit user input
C. Never
D. To improve network performance
Q23. Why should you be careful using the BETWEEN keyword for date criteria?
A. It is case-sensitive
B. It ignores time components and may exclude intended records
C. It only works with number fields
D. It deletes records outside the range
Q24. How would you show records from the past 30 days in a date query?
A. >Date()-30
B. <Today()+30
C. =30
D. BETWEEN Date()-15 AND Date()+15
Q25. How should people like students, parents, teachers, and administrators be stored in an Access database?
A. Each in their own individual table
B. All in one table with a field to indicate the type of person
C. In a lookup field in another table
D. By duplicating phone numbers in each table
Q26. If a client deletes a record with AutoNumber ID 101 and wants to restore that specific number, what is generally true?
A. AutoNumbers are for relationships, and gaps usually do not matter
B. You must never use AutoNumbers
C. Compacting and using an Append query always restores the number
D. The number 101 is lost forever
Q27. What is a likely reason for an Access split database locking up and corrupting on a server?
A. Users are all using their own local copies of the front end
B. Both front-end and back-end files are shared on the server folder
C. There are too few tables
D. The front end is not split
Q28. If a contact subform shows all records for all customers, not just the current one, what setting likely needs adjustment?
A. The Default Value property
B. The Link Master Fields and Link Child Fields properties
C. Tab Order
D. The Input Mask
Q29. How do you fix a form where keyboard Tab moves the cursor unpredictably?
A. Rebuild the form from scratch
B. Set the Tab Order of the fields correctly
C. Add a subform
D. Use only mouse navigation
Q30. What should you do if an Access database gives a security warning about active content?
A. Move the database to a different folder
B. Set up a Trusted Location via the Trust Center in Options
C. Run as administrator
D. Uninstall Access and reinstall
Q31. What is usually the best way to work with Excel data in Access?
A. Only link to the spreadsheet always
B. Import the data to an Access table for optimal performance
C. Convert the Excel file to PDF and import
D. Copy and paste the data into Word
Q32. How do you ensure no two customers have the same email address in Access?
A. Use a Validation Rule on the field
B. Set the field's property to "Indexed (No Duplicates)"
C. Require users to review previous entries
D. Use an Attachment field
Q33. What is the recommended way to set up a value lookup in a table?
A. Use table-based lookup fields
B. Always allow users to type values
C. Use a related field with a combo box on a form
D. Build a macro for each value
Q34. Which property would you use to restrict the Credit Limit field to a maximum of $1,500?
A. Input Mask
B. Default Value
C. Validation Rule
D. Indexed
Q35. In which section of a report should you place information that repeats at the bottom of every page?
A. Report Header
B. Detail Section
C. Group Footer
D. Page Footer
Q36. What is a major benefit of using Access over spreadsheet programs like Excel?
A. Easy to store very large sets of related data efficiently
B. It always has a lower file size
C. It is a graphic design tool
D. It can only be used on Linux
Q37. (Bonus) According to the video, who is the best Starfleet captain?
A. Kirk
B. Archer
C. Janeway
D. Picard
Answers: 1-A; 2-C; 3-B; 4-B; 5-B; 6-B; 7-C; 8-C; 9-B; 10-B; 11-B; 12-B; 13-C; 14-B; 15-C; 16-C; 17-B; 18-B; 19-A; 20-C; 21-A; 22-B; 23-B; 24-A; 25-B; 26-A; 27-B; 28-B; 29-B; 30-B; 31-B; 32-B; 33-C; 34-C; 35-D; 36-A; 37-D
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone covers a comprehensive set of interview questions and answers for Microsoft Access. Whether you are preparing for a job interview as an Access developer or expecting a test in school, these are the essential concepts you should be comfortable with.
Recently, one of my developer students reached out for some prep ahead of a job interview for an Access developer position. Knowing his skill level from his previous questions and progress, I was confident in his abilities, but it got me thinking about what questions I would ask if I were hiring someone for such a role. The result is a list of 36 fundamental things you should know about Access database development.
If you are studying for a class, you might find some of these on your exams as well. If you use my answers in your coursework, be sure to quote me and, as always, do further research on other reputable sites to avoid plagiarism.
For most of these questions, I'll provide a concise answer that a teacher or an interviewer would be happy with, and I'll also point you to more in-depth resources on my website for each topic.
The questions in this list are not in any particular order. Sometimes, one question sparks another. More importantly, none are tricky or obscure "gotcha" questions. I strongly dislike those, especially ones seen in automated certification tests that ask about rare menu options that even seasoned developers rarely need to know.
These 36 questions are all real-world, relevant issues that you are likely to encounter in everyday Access development. In my research, I found a lot of outdated or misleading question sets online, with a focus on features that no longer exist. That is not the case here.
This video does not cover Visual Basic-related questions. If you are interested in a separate set devoted to Access VBA, let me know in the comments and I will consider making one.
Let's start:
The first question is foundational: What is Microsoft Access? Take a moment and think of your own answer before you read on. Microsoft Access is a relational database application. The word "relational" is important because it refers to managing relationships between multiple tables, not just a single flat file like in Excel. Access provides a graphical interface and a desktop-level database engine. It is not designed as a database server itself but can serve as a front-end to server-based databases like SQL Server. It enables you to design and work with databases locally or in small workgroups, and connect to larger back-end databases if needed.
Next, what are the core components of an Access database? You have tables, which store the data; queries, which allow you to retrieve, filter, and manipulate that data; forms, which provide a user-friendly interface for data entry and editing; reports, used for printing or sharing data in formats like PDF; macros for automating repetitive tasks; and modules, where you can use Visual Basic for Applications to write more advanced logic. These six are the pillars, though other features exist.
Understanding relationships between tables is key. Relationships ensure minimized errors, efficient data management, and prevent duplicate data. This is chiefly achieved using ID fields, typically AutoNumbers, so each table's record has a primary key. Other tables point to that with a foreign key. Mastering table relationships is perhaps the most important concept for anyone building an effective Access database.
The term SQL stands for Structured Query Language. In Access, queries are written using SQL, which lets you perform SELECT, DELETE, INSERT INTO, and other operations. Do not confuse SQL with SQL Server, which is Microsoft's standalone database server. SQL is a language, while SQL Server is an application.
Moving on, database normalization refers to organizing data to reduce redundancy and improve integrity. For example, customer contact information should live in a Customers table, not in every order they place. There are exceptions, but as a rule, normalization is critical.
Referential integrity is another core concept. It ensures that child records in related tables (like orders for a customer) cannot exist without their parent records. Cascade deletes can automatically remove linked child records when a parent is deleted, but this feature must be used with great care to avoid accidental data loss.
Many-to-many relationships require a junction table. For example, connecting cars and drivers where each car can have multiple drivers and each driver can operate more than one car. The junction table keeps track of these pairings.
A single Access ACCDB database is limited to a two-gigabyte file size. To manage larger sets of data, you can split your database into multiple back-end files for tables and a single front-end containing forms, queries, and reports. This way, you're only really limited by your available storage.
Access no longer supports true user-level security on back-end table files. You can protect the front end or set a password on a back-end file, but everyone uses the same password. If you require granular, per-user security on your data tables, you'll need to use a database server like SQL Server.
When should you consider upscaling to SQL Server? If you need better data security or your database is getting slow with multiple users on a network. SQL Server processes queries on the server and only transmits the results, vastly improving performance and security.
Regarding attachments, Access offers an Attachment field type, but I do not recommend using it. Access handles file storage inefficiently, so it is better to store files elsewhere on your server and simply record their path in a text field.
What about calculated fields? Although Access tables let you create calculated fields, you should calculate values in a query rather than storing them, to maintain proper normalization.
Is it better to store a person's name in one field or split it into first and last name? Always split them. It is far easier to combine the two than to try to pull apart a single name field.
A continuous form displays multiple records stacked vertically on the screen, while a single form shows just one record at a time.
When adding months to a date, use the DateAdd function. To increment days, simply add to the date field. Time calculations use fractions (such as 1/24 for one hour).
To set a default value for new records, use the Default Value property. Existing records can be updated with an Update Query.
Seeing an "Enter Parameter Value" prompt usually means there's a reference to a field or parameter that doesn't exist or is misspelled in the data source for a form or report. Nine times out of ten, it is just a typo.
If a user wants to print only selected records, the simplest way is to add a Yes/No field to your table, place a checkbox on your form, and then build a query to filter for selected records.
To enforce phone number formats like 239-555-1212, use the Input Mask property in your table or form.
If you need to display data from another table in a form, use the DLookup function for single records. For lists or continuous forms, it's better to create a query that pulls the information together beforehand, as multiple domain lookups can severely impact performance.
To open a report showing just a specific record (like a single invoice), use query criteria based on a value from an open form. There's an easier VBA method too, but it can be done without code.
Restricting the field size of a Short Text field to less than 255 characters today is mostly for limiting user input rather than conserving disk space. Access now manages storage much more efficiently than in previous decades.
To get orders between two dates (inclusive), be cautious with the BETWEEN keyword if your date fields include times. Using a greater-than-or-equal and less-than comparison is safer to capture a full day's data.
To find all records from the past 30 days, use the Date() function in your query criteria.
For databases tracking students, parents, teachers, and staff, use a single People table, with a type indicator, rather than separate tables for each group. This follows normalization and avoids redundant structures.
If an order is deleted, clients might want to reuse the missing auto number. In practice, auto numbers are meaningless to users and are solely for Access to manage relationships. Only in rare cases, where you have related records and need to recover a deleted number, is there a somewhat complicated way to do it with an Append Query.
If your split database frequently corrupts or locks up, a common reason is multiple users sharing a single front-end file across the network. Each user needs a local copy of the front end, connected to the single back-end file on the server.
When your subform shows all records instead of just those for the related parent, check that the Link Master Fields and Link Child Fields properties are correctly set, matching the key fields in each.
If tab order (the order the cursor moves when you press Tab) is random in your form, set it manually or use the auto order feature, so data entry flows logically.
When a client gets a security warning about active content, set up a Trusted Location on their machine under Access options, so databases in that folder open without security prompts.
If a client needs to use an Excel spreadsheet in Access, they can link to it, treating it as a table, but for better performance it's best to import the data directly into Access. Linked Excel files, especially large or remote ones, can slow you down.
To ensure unique email addresses in your Customers table, index the Email field with the "No Duplicates" property.
Table-based lookup fields (those drop-down lists set at table level) are not recommended. Instead, store the related key and use a combo box or list box on your forms for proper normalization and easier maintenance.
To prevent users from entering a credit limit above a certain value, such as $1,500, set a Validation Rule in the table to prevent entries outside the range.
If something needs to repeat at the bottom of every page on a report, it belongs in the Page Footer section. The Report Footer only appears once, at the end.
Among the many benefits of Microsoft Access, it stands out for being more powerful and reliable than spreadsheets for storing and managing substantial datasets, but is still easy and cost-effective compared to server-based solutions like SQL Server or Oracle. Access is fully ODBC compliant, making it easy to share or migrate data online.
For a little levity, my bonus question: the best Starfleet captain is obviously Picard!
That wraps up my list of 36 essential Access interview (or test) questions. These are the concepts I would expect any serious candidate to have a strong grasp of.
If you have suggestions for more questions or want to see a similar set for VBA, Excel, or Word, let me know in the comments. I hope these pointers help you in your studies or interview preparations.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List What is Microsoft Access and its key capabilities
Parts of an Access database: tables, queries, forms, reports, macros, modules
Importance of relationships between tables
Definition and basics of SQL in Access
Database normalization principles and exceptions
Referential integrity and cascade delete considerations
Many-to-many relationships and junction tables
Access ACCDB file size limit and database splitting
User-level security limitations in Access
Reasons to upgrade Access databases to SQL Server
Attachment data type and why to avoid it
How to store and display images in Access forms and reports
Calculated fields in Access: pros and cons
Storing first and last names in separate fields
Continuous forms vs single forms in Access
Using DateAdd and date math functions
Default Value property for new records
Handling "Enter Parameter Value" prompts
Creating reports for user-selected records with Yes/No fields
Input Mask property for phone numbers and formatted fields
Using DLookup and domain functions
Opening reports for specific records without VBA
Field Size property and limiting text length
Query criteria for date ranges and time components
Query criteria for records from the past 30 days
Designing a single table for multiple person types
AutoNumber fields and handling missing or deleted numbers
Splitting a database and front end file best practices
Setting subform Link Master Fields and Link Child Fields
Setting Tab Order and Tab Stop in forms
Setting up Trusted Locations for security warnings
Importing vs linking to Excel data in Access
Creating unique indexes to prevent duplicate emails
Problems with table-level lookup fields
Using Validation Rules to limit data entry values
Understanding report Page Footer and Report Footer sections
Major benefits of using Microsoft Access |
