Planning Your Database

Lesson 2: Planning Tables, Fields, Forms & Reports

S M L XL FS | Slo Reg Fast 2x | Bookmark

In Lesson 2, we will discuss planning your database, covering how to determine what tables and fields you need, what information belongs in each table, and the importance of organizing your data properly from the start. We will talk about sketching out forms and reports on paper before building them, deciding what features you want, and how to separate different types of data into their own tables. You will learn why careful planning is necessary to avoid problems later, and we will discuss some basic naming conventions and best practices as you prepare to move forward with your database project.

Navigation

Prev: Lesson 01
Next: Lesson 03

Keywords

Access Beginner, database planning, table design, field selection, forms design, report design, database roadmap, customer data management, order tracking, employee tables, product inventory, accounts payable, accounts receivable, database normalization,

Subscribe to Planning Your Database
Get notifications when this page is updated

Intro

In lesson two, we will discuss planning your database, covering how to determine what tables and fields you need, what information belongs in each table, and the importance of organizing your data properly from the start. We will talk about sketching out forms and reports on paper before building them, deciding what features you want, and how to separate different types of data into their own tables. You will learn why careful planning is necessary to avoid problems later, and we will discuss some basic naming conventions and best practices as you prepare to move forward with your database project.

Transcript

In lesson two, we'll discuss planning your database: what tables do you need, what fields should go in each table, what do you want your forms and reports to look like. The first thing you should do when planning a new database is to sit down with paper or a whiteboard and try to figure out exactly what you want your database to do.

Plan this out in advance, make a list of all the features that you want included in your database. What kinds of data do you want to store in the database? What do you want the different forms and reports to look like? What other features do you need?

Do you want to store customer information, name and address, for example, track correspondence with those customers so every time you talk to them, that's stored in the database? Do you want to generate mailing labels on a regular basis, perhaps to a certain set of customers? Do you want to create quotes and invoices and print those out? Do you want to store order history? Keep track of everything your customers have purchased? Do you want to track employee time sheets, clocking them in, clocking them out, calculating the number of hours they've worked in a week and so on? Do you want to maintain a product inventory so you know how much of each unit you have on the shelf? Do you want basic accounting features, accounts payable, accounts receivable, all that stuff?

These are all things you should write down before you even start building your database in Access. You should know exactly what you want the database to do and have a roadmap.

Now once you've figured out what you want your database to do, write down the types of tables you think you'll need. Remember all of the data in your database will be stored in one or more tables. I like to use index cards, one for each table. If you want to use sheets of paper, that's fine too. You want all of one kind of information in the same table. For example, all the information about your customers go in the customers table. All of the information about the orders they place goes in an orders table. You wouldn't put order information in the customer table and generally you don't put customer information in the orders table, although there are some exceptions. I'm going to track employees and products in their own separate tables. Employees and products are two completely different things so they each get their own table.

Later on, we're going to learn how to relate these tables together. For example, customers can place orders so they can be related to each other. But they're completely different things. Customers and employees, although they represent people and you could certainly store them that way if you wanted to, for the purposes of a beginner database, we'll put them in separate tables. You generally get different kinds of information about employees than you would for customers. So for my beginner lessons, I like to separate them. Later on in the more advanced lessons, we can put them together because they're both types of people. We'll just have a category on the person table so we can track which ones are employees and which ones are customers. But that's more advanced. We'll get to that later on.

Customers can actually place multiple orders. So you would not want to store order information in the customer table because you wouldn't know how many orders each customer would place. The customer could have one order, could have 500 orders. So that information goes in separate tables. We're going to talk a lot more later on about relating tables together. For now, just remember, each type of information goes in its own separate table.

People ask me all the time, well, how do I know what tables to make? How do I know which fields go in which tables? It's literally just a matter of experience. You've got to see some examples to know the right way to do it. And that's what I'm going to show you.

Now that you've got an index card for each table that's going in your database, take each index card, for example, customers, and write down a list of all the fields or types of data that you want to store in that table. Remember, each specific bit of information is considered a field. If we were making an Excel spreadsheet, which more people are familiar with, each field would be a column of data. So, for example, in my customers table, I've got basic information, like first name, last name, you can put middle name and all that stuff in there too if you want that. I've got address, city, state, postal code, or zip code if you prefer, phone, fax, if you still use fax numbers. I've got a mailing list field which indicates whether or not this customer is on my mailing list. You might have people that you don't want to send your newsletter to, for example. Customer since will be a date field, so I know how long this person has been a customer. I've got their credit limit to store a currency value. What's their maximum credit limit? And then finally, I've got their email address and some notes. I put a notes field in just about every table.

Now make sure you're as specific as you think you're going to need when setting up your fields. It's easier to put two fields together later, something called concatenation, than it is to try and split them apart. For example, if I just had one field called customer name and later on, I want to send out a mailing that says 'dear John' and the customer name field says 'John Smith,' now I've got to figure out how to split that apart into two fields. So do that up front. It's easier to type in John and then in the last name field put in Smith than to try and pull that apart later.

Address, for example, is another one. Address, city, state, zip code is usually enough for most of my clients. But I have built databases for companies before that do heavy mailing lists and they want to split the address field up. You've got the number of the building on the street. You've got the street name like Main. And you've got the type. Is it a street? Is it a drive? Is it an avenue? So mailing list companies like to split all that up. Some other companies like address one and address two. Address two could be like a suite number. This is completely up to you. Some companies want a zip and a zip plus four. Again, your decision. Decide that now. Be consistent.

Just remember if you ever think there's a possibility in the future that you're going to want it separated, separate it now. Be as specific as possible but don't go overboard, of course.

Now one thing you'll notice is that I didn't use any spaces in my field names or my table names. First name, for example, is capital F, first, no space, capital N, name. Mailing list, capital M, capital L, no space.

In my 20 plus years of building databases, I've discovered that it's much, much easier later on if your field names and your table names and your query names don't have spaces in them. When we start getting into Visual Basic programming and macros and all that stuff, spaces can cause problems. Trust me, don't use spaces. I will explain a lot more about this in future lessons.

One thing I want to address right now at the very beginning of the course is something that people comment about all the time in my forums and when they email me. They say that I use the phrase "in a future lesson" or "in future lessons, we'll do this" or "I'll teach you about that." It's almost a complaint, like people think I'm intentionally withholding information so I could sell more classes. No, that's not the case at all. But just like the analogy, you have to learn how to walk before you can learn how to run, it's the same with learning how to use computers or any education for that matter. You can't just jump into calculus without understanding basic arithmetic and algebra.

So when I tell you I'll teach you more about proper naming conventions for tables and for fields in future lessons, we'll cover it when we get to it. I can't teach you everything all at once up front. I wish I could. I wish I could just plug your brain into my computer and download all this information right into your head. But it doesn't work that way. So when I say we'll cover it in a future lesson, trust me. That usually means I'm going to give you a little bit of information now just to satisfy your curiosity, but we'll go over the details when it's appropriate.

If you really want to learn something when I say we'll learn about it in a future lesson, I've probably already covered it because I've done this course before in earlier versions of Access. So if you really want to learn it, like if I mentioned something about queries, go to my website, search for the topic and you'll find it.

I'm only holding back because I know the proper way to teach this. I've been teaching Access for almost 30 years. So just trust me when I say we'll get to it in a future lesson.

Now back to designing our tables. Only put customer information in the customer table. You'll notice I don't have any information about this customer's orders in the customer table. I've seen people try to build databases like this. They're in the customer table. They've got order information, order amount one, order amount two, order amount three, and so on. Where does it end? How many order fields are you going to put in the table? Hopefully you're going to want your customer to have hundreds of orders. So it doesn't make sense to store that information in the customer table.

Right now this customer is limited to only three orders and that's bad database design. It's a subject called database normalization which I have whole lessons on and again we'll get to that in a future lesson. But for now just think to yourself: customer table, customer information; order table, order information.

Now I've eased up my stance on this somewhat. The last time that I did this course was back in 2013 and it's now 2021. So I've eased up on this a little bit for only some things. For example, I used to say if you've got multiple phone numbers or multiple email addresses or multiple physical addresses put them in a separate table. Now I'll allow you to have one, two, max three because I know a lot of people nowadays have multiple email addresses, multiple physical addresses or a ship to address, a billing address and so on. So for beginners, if you want to have two phone number fields or two address fields, that's okay. If ever going to be more than three, you're going to put it in a separate table and again we'll cover that when we get to making multiple tables. But if you want to have a bill to address and a ship to address in the customer table, that's fine. If you want to give the customer the ability to have three email addresses, that's fine. Three is my limit. If you go over that you really should consider making multiple tables for it.

So set up a separate order table to put the order information in. Where I've got an order table, an order date, sales rep, that could be an employee, the order total, the sales tax collected, whether it was delivered or not and so on. All the information about each order will go in the order table. This of course is just a simple example for class but you get the idea. Later on I will teach you how to link these two things together with IDs. Each customer will be assigned a customer ID. Each order will have an order ID and we'll link them together using that customer ID. Each order will have a customer ID associated with it so we know which customer placed the order. I will spend lots and lots of time on relationships, a very, very important concept in Access. I devote whole lessons to it and we will get to that eventually.

So with this setup you can have an unlimited number of customers within reason. You can have an unlimited number of orders for each customer. You aren't storing order information in the customer table, that's bad. And you generally don't store customer information, aside from the ID, in the order table. With the exception of maybe a ship to address, you might want to know where the order was shipped. So if the customer moves, you can still tell if the order was shipped to his old address. We'll talk about all of that when we get to relationships.

Now if this seems at all confusing, don't worry about it. We're actually not going to work with multiple tables and relationships until we get into the expert series. So we've got a lot of learning to do first before we get to that point. For now, I just want you to recognize the different types of information in your database will go into their own tables. For today's class we're going to focus solely on the customer table but later on we'll add other tables like contacts, employees and eventually orders and products, vendors, and lots more.

The next step, after you figure out all the tables that you need, is to get out some paper or a whiteboard and draw out the way you want your forms to look on the screen. Remember, forms are what the user interacts with the database with. You do all your data entry and lookups using on-screen forms.

And yes, for those of you who are curious, these are the original images I drew from my Access 2010 class over 11 years ago. Maybe back then I still shipped most of my classes on CDROM and I kept these images as an homage and also because I didn't feel like making new ones. No, I'm just kidding. Honestly, these images still do the trick. They show you what you need to see and your sketches don't have to be works of art. They just have to be something simple to guide you as you're building your database.

It's easier to build forms if you have a roadmap, a template that you can see exactly what you want your finished product to look like. Even if you don't know how to do something now, if you don't know how to build what you want in Access, just write down what your brain wants it to look like on paper and then eventually you'll figure out how to translate that into Access. I'll show you. It's much easier to build a building if you have an architect put together some blueprints.

Now, as you can see, I'm no artist but I do sit down and sketch out what I want each of my forms to look like when the database is finished. And whenever I build a database for a customer, I tell them the same thing. Sit down, sketch it out on paper. Take a picture of it and send me that so I can see what you want the database to look like, what you want the workflow to be.

Here for example, I've got a main menu on the left with a couple of different buttons on it: customers, orders, employees. I want to click on that first button, the customer button, have it open up the customer form with all the basic customer information on there, name, address, phone number, and so on. I want to see the customer's contact history below that so I can see the last couple of phone calls, what we talked about. And I want to see the customer's picture over on the right.

Now at this point, you might not have any clue how to set this up, but that's what you want it to look like for your business, for your workflow. We'll get there as far as translating all that into Access. It's not that tough.

Oh, and one tip, actually two tips. Tip number one: if you are going to use a whiteboard, make sure you take a picture of it with your cell phone camera and save it. I've had people put stuff on whiteboards that were amazing and then they forgot to save it and then the cleaning staff came in and it was gone the next day. So trust me, take a picture of your whiteboard.

And the second tip is no matter what you see on other websites or read in books, we do not store files inside our database like pictures. We don't store attachments. There's something called an attachment field. If you see other things like books or websites talk about these kinds of things, don't do it. Trust me. You'll thank me later. I'll show you how to properly handle this later on. And here's a hint: if you're curious and you want to see now, post a comment down below in the comments section and I'll point you to a couple videos I got to cover this kind of stuff. Part of the beauty of having done this course three or four times over the past 20 years is that by now I've got a lot of these videos already made. I don't have to say in a future lesson. I can actually tell you in this lesson, you know, expert 14, we will cover this topic. I will start doing that and I'll put helpful links down below the video. In the comments section down there, if you're curious about something right now, just click on that link and go right to that lesson if you want to. I have thousands of hours of Access training videos on my website. There's very little that I have not covered before. I just redo the beginner lessons every now and then for new users who have never used Access before. I don't want you to be confused by minor changes in the interface. Access has been very similar since 2007 with some minor differences that I will cover over the course of the next couple hours.

The next step is to gather together all of your printed reports or if you don't have them printed right now, sketch something just like you did with your forms. You've probably got paperwork that you're using right now, things that you're printing out from Microsoft Word or Excel, or forms that have been given to you by an insurance company that you work with, or a vendor or that kind of stuff. Gather all those things together so you can see what kinds of reports you're going to need to build later on. Here, for example, I've got an accounts receivable printout that's important at the end of each month. I've got an invoice that's important. I'm going to print that out or email them to my customers, and I've got a set of mailing labels that I need for sending out these invoices.

Now an important word about terminology. A lot of people call paperwork forms. Here's a form that I want you to fill out for me. They hand you a clipboard with a piece of paper on it. Fill out this form. Remember, in database terminology, a form is something we're going to use to work with on the screen, whereas a report is something that is generally designed to be printed out or saved as a PDF file, for example, or emailed. But they're generally formatted for printout. Think forms - screen; reports - print.

You're going to do all of your work on the screen, looking up data, entering invoices, typing in customer stuff, that kind of stuff, in forms. When you want to print out an invoice or print out mailing labels, that's going to be reports. Can you go the other way? Can you print out a form? Sure. Can you view a report on the screen? Of course, you can do a print preview and see what it's going to look like. But generally, forms for the screen, reports for printing or emailing. Or if you still happen to be living in 1992, faxing.

So get all your current printed paperwork together, put it all in a folder. This will make it easy when it comes time to build reports later so you've got all your stuff in one place. You don't have to go hunting around for all the different reports. Speaking as a contract developer when I used to build databases full time for other clients, one of the tough things was that they would say, I want the database to do this and this and this and this. And I build them a database that had maybe 10 forms and some menus and whatever. And they'd say, I need invoicing and I need accounts receivable. I put those reports together. Then when the database was all done, they'd be like, I need this report and this report and that report and give me like 20 different reports that they want to generate. And that can sometimes take longer than building the database itself. So definitely take that into consideration when you're building your database, whether it's for you or for your company or if you're building it for someone as a client. Reports can be time consuming, trust me.

The bottom line here is to plan ahead. Don't just get crazy and start jumping into Access and think you're going to build your final company database right now. In fact, one thing I'm going to tell you is while you're learning Access, at least for the next couple of classes, I don't want you to work on your actual company database, your project that you have planned at all. I want you to build the sample examples that I have for you in class. You will learn better. Follow along with me, build my database. Don't try to apply it to what you're doing because this is a teaching exercise and I guarantee you between now and the time you finish the class, you're going to learn all kinds of new stuff and you're going to scratch what you've built and start over three times. I've done it myself.

A complex database takes a lot of planning and you don't want to be almost finished with your database and then realize, oh, I forgot to put this in. So now you've got to go back and put it in your table, which means you've got to update your queries and add it to all your forms and add it to the reports. So try to get this stuff on paper as much as you can up front. I know it's impossible to think of everything up front and yes, you can certainly add things on the fly as you're going, but the more you plan ahead, the more time you will save later.

At least figure out all the tables you're going to need, what you want all your forms to look like, all the reports that you need. Don't worry too much about queries. We usually build queries to support a form or to support a report. We'll talk about queries a little bit later.

But tables, the fields in each table, the forms and the reports that you need, write all that stuff down now before you continue on with the next lesson. Once again, plan ahead now to save a lot of time later.

Quiz

Q1. What is the very first step you should take when planning a new Access database?
A. Sit down with paper or a whiteboard and plan exactly what you want the database to do
B. Start building tables in Access immediately
C. Search online for similar databases and copy them
D. Focus on designing queries before anything else

Q2. Why is it important to separate different types of data into their own tables?
A. Each type of information has unique fields and structures
B. It makes the database look more professional
C. Access can only handle one type of data per table
D. To keep the database under size limits

Q3. When deciding which fields go into a table, what is one strategy the instructor suggests?
A. Use index cards or paper to list all the fields for each table
B. Use as few fields as possible for simplicity
C. Combine all information into a single text field
D. Only include fields you need today, not future requirements

Q4. What is the benefit of splitting fields like "Name" into "FirstName" and "LastName"?
A. It is easier to use data in mailings or reports that need specific parts of the name
B. It makes the data entry slower
C. Combining them later is easier
D. It helps prevent data loss if a field is deleted

Q5. What database design principle is highlighted by not storing multiple order fields (e.g., Order1, Order2, Order3) in the customer table?
A. Normalization
B. Data mitigation
C. Data inflation
D. Projection

Q6. According to the lesson, what is generally true about including multiple phone numbers, emails, or addresses in the customer table for beginners?
A. Up to three is fine, but more than that should be in a separate table
B. You should always use a separate table, even for one extra email
C. Only one phone and one email are ever allowed
D. Access will not allow multiple fields of the same type

Q7. Why should you avoid putting spaces in your table and field names, especially for beginners?
A. Spaces can cause problems in programming or with VBA/macros
B. Spaces make the names too long
C. Spaces slow down database performance
D. Access does not allow spaces in any object names

Q8. What is the main difference between a "form" and a "report" in Access terminology?
A. Forms are for on-screen data entry and lookup; reports are for printing or sharing
B. Forms can only be printed, reports can only be viewed on screen
C. Forms do not display data, reports do
D. Reports are only for administrators

Q9. Before building forms and reports in Access, what does the instructor recommend doing first?
A. Sketch out what you want your forms and reports to look like on paper or a whiteboard
B. Code everything in VBA first
C. Only use Access's built-in templates
D. Input sample data to see how it displays

Q10. What is a potential issue if you do not plan out all needed reports before starting your database?
A. Adding new reports later can require significant changes and extra work
B. Reports are automatically generated and do not need planning
C. Access restricts you to ten reports per database
D. You cannot edit reports once created

Q11. What should you focus on during the planning phase of your database design, according to the lesson?
A. Identifying tables, fields in each table, forms, and reports you will need
B. Writing all your queries in advance
C. Learning VBA before anything else
D. Installing Access add-ons

Q12. Why does the instructor recommend not to work on your real company database while learning from these lessons?
A. You will likely learn new information and want to start over as you progress
B. You might accidentally delete your company data
C. Access is not suitable for real company databases
D. There are no sample files provided

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A

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 is all about planning your database. Before you even open up Access and start building, you need to invest time in figuring out exactly what you want your database to accomplish. This means sitting down, grabbing some paper or a whiteboard, and working through what you need the system to do for you or your business. Make a comprehensive list of all the features and tasks you want your database to handle. Do you need it to store customer information like names and addresses? Would you like to track conversations or correspondence with customers, print mailing labels, issue quotes or invoices, keep order histories, manage employee timesheets, track product inventory, or add basic accounting functionality? Decide on all these details up front.

Once you have your feature list, think about the kinds of data you need to store. Next, organize that data into tables. Each type of information belongs in its own table. For instance, all your customer details go into a Customers table, orders go into an Orders table, and so on. You should not mix order information into the Customers table, nor should you put customer information in the Orders table. Employees and products each also deserve their own tables since they are completely different kinds of records. Down the road, you'll learn how to relate these tables together, like connecting orders to the customers who placed them. But for now, keep the information types separate.

A common question I get is how to decide what tables to create and which fields to put in each table. The answer comes with experience, and I'll show you by example in this course. To start, I recommend using index cards or sheets of paper, one per table, and writing down all the tables you think you'll need. Then, for each table, list all the fields - these are the bits of specific information you want to store. If you think in terms of a spreadsheet, each field is a column. For a Customers table, you might have first name, last name, address, city, state, postal code, phone, email, mailing list status, a customer since date, credit limit, and a notes field. Try to be as specific as necessary now because it's easier to combine fields later than it is to split them apart. For example, store first and last names in separate fields, rather than combining them.

When it comes to naming your tables and fields, avoid using spaces. So, instead of 'First Name', use 'FirstName'. This practice may not seem important at first, but it saves a lot of headaches later, especially when you start using Visual Basic or macros in Access. Spaces can complicate things, so trust me and leave them out.

As for advanced techniques and features, I know some of you will be eager to jump ahead. When I say that we'll cover something in a future lesson, it's because you need the basics before moving onto more complex topics. Teaching is a process, and building up foundational skills now will help you understand the more advanced stuff later. Many of the topics I reference are already covered in detailed lessons on my website if you want to look ahead.

Returning to database design, each table should only hold information specific to that object. Don't be tempted to squeeze multiple pieces of order information into the Customers table using fields like 'OrderAmount1', 'OrderAmount2', etc. That approach limits your customers to a set number of orders and leads to poor design. It's better to create a separate Orders table and relate it to the Customers table. This concept is called database normalization, and while we will go into depth in future lessons, for now, just keep similar types of data together in their own tables.

Over the years, my views on storing multiple addresses or phone numbers have softened slightly. While I used to recommend storing each unique piece of information in its own table, I now think it's acceptable for beginners to have up to two or three of these fields (for example, billing and shipping addresses) in your customer table. However, if you anticipate needing more, make a separate table for them.

For the Orders table, you might have fields for order date, sales rep, order total, sales tax, delivery status, and so on. Keep in mind you will eventually link orders to customers through a customer ID, which ensures each order can be traced back to who placed it. We will cover relationships in detail later, so don't worry if this feels unclear right now.

For this lesson, our focus is on the Customer table, but later we will add more, including contacts, employees, orders, vendors, and products. The next step in planning is to sketch out your forms. Forms are what you and other users will interact with in the database to enter and view data. Do not worry about artistic skills here - just make a simple drawing that outlines what you want your screens to look like. You might want a main menu with buttons for customers, orders, and employees. For the customers form, you might want all the basic information displayed, space for notes, and perhaps a list of recent interactions. Don't worry right now about how you'll build these forms technically; just focus on what you want to see.

If you are working with a whiteboard, take a picture of it with your cell phone to save your sketches, in case it gets erased. Also, avoid storing files or attachments like photos directly in the database. There are better ways to handle those, and I have other tutorials on my website covering the topic.

Next, gather up all your existing paper forms, printed reports, or anything else you've been using for your business, such as invoices, mailing labels, and statements. Review what you already print out or share with customers, employees, or vendors, and make note of what types of reports you want your database to produce in the future. That way, you can plan for all the necessary reports from the start, and reduce the chances of missing something important later on.

A quick clarification about terminology: in database design, a 'form' is what you use on the screen for entering, editing, or viewing data. A 'report' is something you design specifically for printing or saving, such as invoices, statements, or mailing labels. While you can view reports on the screen and print forms, the distinction is that forms are for data entry and work, while reports are for output and distribution.

Reports can take as much or more time to design as the rest of your database, so gather your requirements for those early in the planning process. From my own experience as a contract developer, clients often think of a lot more reports once the database is almost finished, which means coming back to add or change things later. Catch as much as you can up front to avoid extra work.

One last piece of advice: as you go through these lessons, do not try to build your own project database immediately. Instead, follow along with the sample examples I provide here. You will learn more effectively by working through the course structure, and you are much less likely to get frustrated or have to start over multiple times, which happens even to experienced developers.

To sum up, plan thoroughly before you start building. This means making a list of all the tables you will need, writing down which fields each table will have, sketching out all the forms for on-screen work, and collecting all the reports you anticipate needing. Try to get as much of this down on paper as possible - it will save you time and headaches later. We'll talk about queries when we get to them, since those typically support forms and reports.

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

Planning your database on paper or whiteboard
Listing desired database features
Determining data types to store
Designing forms and reports before building
Identifying required tables for your database
Assigning the correct fields to each table
Using index cards or paper for table planning
Keeping each data type in separate tables
Understanding basics of table relationships
Building a customers table with specific fields
Proper field naming conventions (no spaces)
Deciding field specificity, like splitting names
Guidelines for address and contact fields
Allowing multiple phone numbers or addresses
Avoiding mixing unrelated data in one table
Sketching form layouts before building
Form vs report in Access terminology
Gathering sample reports for design reference
Importance of planning reports ahead of time
Distinction between on-screen forms and printable reports
Tips for preserving sketches and whiteboard drawings
Avoiding storing file attachments in databases
Guidance on careful planning to minimize rework
Working on sample projects before company databases
Recommendations for planning tables, forms, and reports before building

Article

Before you build a Microsoft Access database, it is essential to spend time carefully planning what you want that database to accomplish. Good planning will save you time and frustration later and help you create a database that meets your needs both now and in the future.

The first step is to sit down with a blank sheet of paper, a whiteboard, or even index cards, and outline exactly what your database should do. List all of the features and functions you would like to see. Ask yourself what kinds of data you want to store. Do you need to track customers, their addresses, and the history of your communications with them? Would you like to generate mailing labels, create quotes and invoices, or keep a record of order histories for each customer? Should the database keep track of employee hours, manage inventory, or provide simple accounting features like tracking accounts payable and receivable? All of these are examples of database features you might find useful, but every database is unique. The key is to record everything you need before you start building.

Once you have your feature list, start thinking about the main types of information you want to store. Each kind of information should have its own table. For example, all customer information belongs together in a customers table. All the details about orders should go in a separate orders table. Products, employees, vendors, and other types of data usually each get their own tables. You do not want to mix these in the same table. For now, keep customers and employees in their own tables even though they may both be people, because the information you want to track for each is usually different. You can always adjust your design later as you gain experience.

After identifying your tables, take some time to decide what fields belong in each table. A field is a single piece of information about something in your table, such as a customer's first name, last name, email address, or phone number. If you are used to Excel, you can think of each field as a column in your spreadsheet. It is important to be specific when choosing your fields. For instance, instead of a single field for customer name, use a first name and last name field. This will make it easier later if you want to personalize letters or sort your customers by last name. Similarly, consider whether you want address information split up into separate fields such as street address, city, state, and zip code. Some businesses need even more detail, like splitting street numbers from street names, or separating mailing addresses into address lines one and two.

Decide now how detailed you need your fields to be. It is much easier to combine fields later if necessary than to try to split them apart after your database is full of data. While being specific, do not go overboard and add unnecessary complexity unless you are sure you will need it.

When naming your fields and tables, avoid using spaces. Instead of writing First Name, write FirstName or First_Name. This is a best practice, especially when you get into programming or writing macros, because spaces can lead to errors or make your database harder to work with later.

Keep the information in each table focused and do not mix unrelated data. Avoid the temptation to store multiple orders in the customers table or multiple phone numbers in a single field. If each customer can have multiple orders, keep orders in their own table and link them to customers using a unique customer ID. This is an example of a relational database and the process of keeping data organized in this way is called normalization. Similarly, if you think each customer might have several addresses, phone numbers, or emails, think about how many is reasonable. For most simple databases, you can create fields like Email1, Email2, and Email3, but if you need to track more than three, consider using an additional table specifically for email addresses linked back to the customer.

After you have outlined your tables and fields, sketch out what you want your data entry forms to look like. Forms in Access are how you and other users will interact with your data, entering new information and looking up existing records. You do not have to be an artist. Simple boxes and labels drawn on a page or whiteboard are enough. If you use a whiteboard, always take a picture of your designs so you have a record you can refer to later.

Imagine a main menu form with buttons to open different areas such as customers, orders, or employees. Each button opens a form to view or edit the corresponding table. The customer form might display their contact details at the top, a list of their recent communications below, and perhaps a space for their picture on the side. Even if you are not sure how to implement these design ideas in Access yet, capture your vision on paper. Having a visual reference will help you stay organized as you build your database.

For reports, gather examples of any printed or digital paperwork you currently use in your business. Reports in Access are intended for print or sharing, such as invoices, mailing labels, or monthly account statements. Forms are mostly used for working with data on screen, whereas reports are designed for output. If you refer to printed paperwork as forms in your business, remember that in database terms, forms are for the screen and reports are for printing or saving as PDFs. Keep all your sample reports together in a folder so you know what you will need to build.

Planning your reports early is important because creating them can sometimes be more time-consuming than creating the tables and forms themselves. Knowing your reporting needs up front will help you structure your data and avoid surprises later.

Do not get carried away and start building your real business database just yet. Practice with example databases using lessons or tutorials. This gives you a chance to understand what works and what does not before you commit to an actual project. Even professionals frequently redesign their database structures as they discover new requirements, so do not worry if you have to revise your plan as you go.

Focus for now on planning your tables and fields, and sketching out drafts of your forms and reports. You do not have to worry about queries yet. Those are often created along the way to help support forms or generate specific reports.

Careful planning now, even though it requires some effort, will save you a lot of time and trouble later on. Get all your database ideas organized on paper before you start building. This way, you will have a clear roadmap to guide you as you move forward, making the actual creation of your database in Access much smoother.