All I know is that it’s a “database management system”, and I don’t even know what that means! I have tried fooling around with Access and have gotten nowhere. Where do I start and what is the purpose of this?
In: 4
Non techie version:
A *database* is where we organize data into lists of related information for storage. It makes the data more accurate, easier to navigate, and it takes up less space.
A *database management system*, such as Access, contains all of the tools for constructing and maintaining those lists, and for inserting and retrieving information from them.
A database is an app that holds data records in a tabular format. Any data that can be transformed into a table will pretty much benefit from using a form of database. Almost data collected on a form can be tabulated and stored. The best way to store them is to use a database. That’s why databases usually hold many different tables which hold many different columns.
How you arrange the data will have an impact on how flexible the app you can build. For example, let’s use contact app as example. A simple app would have a table for an entry with columns like first name, last name, phone number, email, address, etc. But this is a very static form like entry. A more advance app would have a table for the User. Then on the phone number, address, email table, it has the ID of the User. That way the user can have multiple entries.
Database pretty much runs the internet. Almost any data you call are store on a form of database. A site like reddit would have a much more complicated database and wouldn’t rely on Access, but Access is easier to learn and use for home/personal user. A home/personal user might want to keep business inventory, keep track of payments, etc.
Can’t beat u/JawGBoi explanation. I just want to add that use it for work frequently. But one of my favorite uses is one of the templates you can download when starting Access and clicking ‘New’. Is the **Home Inventory**. If you have home or renter’s insurance, it’s a great way to keep track of your stuff in case of theft or disaster and learn a bit about how Access works. You can even attach pictures if you ever need for an insurance claim.
If you have 100 customers and they each have 10 orders, and each order has 5 items, you can store this in 3 tables, Customers, Orders, Items. Each table will have a key value for each row. There is a CustomerID, OrderID, ItemID. To relate things together, you use the ID keys. So there is a way to put 5 ItemID into 5 rows of an Order to make a single order and each row has the OrderID. Then you relate the CustomerID to the Order by using the CustomerID and OrderID. You could add a new table called CustomerOrders that has the CustomerID and the OrderID. Now you can relate customers, orders, and items.
So let’s say you want to know all the customers who ordered ItemID 35, which is a green widget. So you write a query, something like this.
Select distinct c.CustomerID from CustomerOrders c, Order d where c.orderID = d.OrderID and d.itemID = 35;
That gives a list of customerID who have ever ordered green widgets. Of course, you want customer names and addresses so you would probably store that in the Customers table or if they move around you might put those customer properties in a related table of CustAddress which is related to customers by the customerID. With queries you can report on the data as finely as your database allows. You can count the number of green widets ordered by each customer, find the average, or count how many were ever ordered by every customer combined.
Access also has VBA so you can write functions to return values that would be cumbersome to put into a query and you can write code to update values in the database. For example, you can write a job that runs on a schedule to automatically send out invoices and update a value in a table to show when the invoice was sent.
At the core, EXCEL is just making lists.
ACCESS is for building collections of lists, and relating the contents of those lists to each other.
Let’s say you are hungry, and open the door to your refrigerator to see what is available for a quick snack. You see some cheeses, and milk, juice, bottled water, etc. There’s leftover Pad Thai, and maybe some old meatloaf.
You close the door, still hungry, but now you have a good idea of what is inside the fridge, right?
That’s EXCEL. It’s a spreadsheet, that let’s you make simple lists. The lists can tell you the kinds of things you have available to eat, and how many, and all manner of things ABOUT the stuff in the fridge. It will always show you what you have inside, assuming you update the lists.
Now, let’s imagine that you are on a diet. You need to have a certain amount of calories, and only from certain food groups. Maybe you are lactose intolerant, or need gluten-free meals.
Glancing in the fridge, (EXCEL), you could find the ingredients for dinner, right? But, you would have to sort and organize (FILTER) the stuff, grouping it and trying to make the best out of what you have.
But, what if you had a bunch of pre-packaged meals in there? All of the correct ingredients, organized by your requirements, and also automatically re-ordered when you run low? Wouldn’t it be nice to be able to just reach in and grab a Mexican meal? Or an Italian one?
That’s the power of ACCESS. ACCESS is a relational database, that means it creates connections between the objects it contains. So, your Italian meal may have cheese, tomato sauce, ground beef, and spices. There are a lot of similar items that can be combined or substituted for any of those, but because you may be lactose intolerant, you maybe use tofu instead. ACCESS would automatically update all your recipes that use cheese and substitute tofu.
Sure, you can do a FIND/REPLACE of your recipe ingredients in EXCEL. But, with ACCESS, you can store variations of those recipes, changing only the ones that matter.
A good way to think of ACCESS is to go to a website to buy a car. You enter YEAR, MAKE, and MODEL, and some pricing information, then click a button and there are all those cars you can afford. That’s relational database at work there…
You can then make a list of all the cars in your area, and print it out as a table. That’s EXCEL.
True, ACCESS can also do that. But, the main difference is that EXCEL does database stuff with a lot of extra steps. ACCESS does EXCEL spreadsheets exports with the click of a button.
ACCESS is EXCEL on steroids, and then some. It is, as someone mentioned, based on the idea of the Structured Programming Language (SQL). You can create a stripped-down version of a sophisticated database in ACCESS, and then upscale it to ‘real’ SQL.
You can’t do that with EXCEL. At best, you can create worksheets that mimic the table information and schema (the structure and relationships between fields and how they are connected). You can use EXCEL to do a LOT of things, using VBA or other coding techniques.
Microsoft Access is a relational database management system (RDBMS).
That’s essentially fancy words for “a big fat Excel workbook, with very strict rules on what you can put where”.
In an Excel spreadsheet, you can put any data you want in any cell you want. It doesn’t really matter whether it’s part of a table or not, just click a cell and type something in there. You can make “”real”” tables with headers and fancy sorting and filtering if you really want to, but it’s optional.
In a RDBMS, tables are the only thing you get. You can’t just pick a cell and chuck data into it, you have to make the table with headers first, and then you’re allowed to add rows to the tables you make. And inside those rows, you’re not allowed to put just whatever you want in any column. You specify ahead of time when you make the table what each column is allowed to have in it. Like, “The `total` column is only allowed to have numbers in it,” or, “The `date` column is only allowed to have a properly formatted date in it”, etc. Try to put in a kind of data it’s not expecting and the database will throw an error and tell you to go kick rocks.
It’s a lot more restrictive than Excel. That’s on purpose. By restricting what you’re allowed to do, the database can make a whole load of assumptions about your data. Where it is, what kind of data it will be, whether there will be duplicates or not, all kinds of things. Those assumptions add up to a lot of saved time, meaning databases can be *extremely* fast at looking things up. *Way* faster than Excel.
Some systems (including Access) include an Excel-like interface so you can see and interact with the data kind of like you would in Excel. But you can do a lot more complicated stuff by talking to the database directly. To do that, you have to speak its language, which is called “Structured Query Language”, or “SQL” for short. Using SQL, you can ask the database very complex questions. And since it is so well-optimized, it can do them very quickly.
You generally only want a database like MS Access in two situations. First is that you have a *LOT* of data, so it matters that it’s very fast. Second is when you intend to hook the database up to another piece of software or a website using programming, in which case the ability to ask for things in SQL will make the programming way simpler. If it’s just you, a human, looking at and editing data manually, you probably just want Excel or some Excel-like program, such as Google Sheets.
If youre familiar with spreadsheets, its essentially a spreadsheet like Excel. Whereas a spreadsheet is most often used to work with the data on the sheet youre looking at, Access is meant to look at all of your spreadsheets at the same time.
It does this via allowing you to build a user interface on top of it where you might enter your name, address, and phone number on one screen and hit “submit” and it sends it to the appropriate spreadsheets that store this info (names, addresses, and phone numbers). In a database, the spreadsheet is called a table instead of a sheet.
Now, you can go to your addresses table and look at all the address information youve collected and its just addresses, nice and clean. You can perform searches and say “show me all the people with the name of Kevin [from the names table] that live in New York [from the addresses table]”.
The way they link the names to the addresses is via a unique identifier called a “key”. When you are submitting your information, you may have had to register your username first. It then gives you an ID such as UID0000123. When you submit the data into, and into its subsequent tables, all records (entries) are stamped with that. So next time you search for your stuff, the system is actually just querying all things that match your user ID. That way, you don’t store all of the information over and over again throughout the tables to try to give enough identifying information to make a match. You just store the ID with each piece of data you tuck away.
In the business world, this is what people do to manage their business processes. An example might be a group that receives 450 invoices from various sources. Instead of opening them all in Excel and then trying to add all the tabs in the worksheets together, all of the excel documents are imported into Access as rows of data in the tables and they create a report that automatically adds it all up for them and creates a single summary file on the other side. Takes 5 minutes to do in Access, would have taken hours in Excel.
So, ever found yourself looking at a spread sheet where you need to enter a bunch of the same information a hundred times? Maybe you’re making a list of all the voters in the county/state, and you need to include information like where they’re allowed to vote, based on where they currently live.
Every single person in the list is going to have an address. It’s going to include a street number, a street name, and a town and postal code. And once you know that, you’ll know their polling station, which is another address. And a LOT of polling places are going to be the same, because groups of people go to the same one. But on a spreadsheet, where all the data is looked at all at once, you’re going to have to enter that information for. Every. Single. Person.
That’s called data duplication, and it sucks. It sucks because it takes more time and effort on your part, it sucks because it increases the chance for you to make a mistake and accidentally tell someone they’re being polled at the wrong address, and it sucks because it makes your file massively larger than it needs to be – and thus, slower to use, and more expensive to host.
So, you want to save time and effort and space, and make less mistakes along the way. The easy way to break down the polling places by town, and say which ones are within which towns – Then I only need to write the polling places’ addresses once, and anyone who wants to can cross reference the address of someone on the list with their polling place.
This is why databases are called relational – I identify a bunch of variables, and then break them down into the data about that particular variable, and only that variable… and then put all that on a table. Then I give that table a way to be referenced easily, and can omit work, effort, money, and mistakes when I refer back to it.
So – I make a table that is about People, and a table that is about polling places. The polling places table has NO data about people – but the people table has a single entry under each person that says polling place number is… and then an entry. Now I can sort my people table and send out the right ballots to everyone who lives within a specific area. And, if someone mistypes, and says someone goes to polling place 532 when there are only 400 entries on the polling place table – it is easy to see the issue.
This is what Access is and does – it is a tool that allows me to build multiple related tables, and minimize all the associated issues that could crop up if I were entering the same data multiple times. It is actually a fairly powerful tool, once you learn to use it – but there’s a lot of details about database architecture that you’ll have to learn, and a lot of planning you have to do, to make a useful database.
I’ve used Access quite a lot, though not recently.
One strong point is that Access makes it easy to construct a front end for your data. By a ‘front end’, I mean a way for users to interact with the data, including adding new records and modifying information. More sophisticated database programs allow you to create front ends, but the ability is not built into the system–you may have to use another programming language to do it.
It occurs to me that a lot of us have answered what it is – but very few of us answered the “what is the purpose of this” part of your question.
The answer to that is that a database gives you the ability to explore data and its connections, but to a modern mindset, which is used to using google, that may not mean much. To understand exactly what it means, we really have to look at how hard related information was to find before databases, and get a feel for how vastly they’ve changed things. This is going to be best highlighted by looking at a set of tasks that databases have made relatively easy and painless, and discussing what those looked like before computerized databases were widespread – I’m going to use finding out about people and places as my example
So… let’s talk about all the information you might have, when you want to research someone or someplace. I’m only going to toss us back to the year 2000 here – but remember that things get harder the further back you go. Anyways, you might know any one piece of the following: An address, a name, or a phone number. How do you find out the other two, or any additional information?
The answer was that you needed a distinct tool, depending on the information you had. If you knew a phone number, you needed the phone registry for that area code, which was sorted by number, and would give you the name of the owner (and maybe the address). If you knew the name, you needed the phone book for that geographic location, which would give you the phone number (and maybe the street address). If you knew the street address, you would need to look at the street directory, which was likely to tell you the name (and might have a phone number).
Three distinct and cumbersome tools, to find out the same pieces of information, depending upon which one you had. If your information was incomplete (say you didn’t know the full name of the person you were looking for, or knew a nickname… ) you might not even be able to use the piece of information you did know to find the person/place. If you wanted additional pieces for information that were related (like say when someone bought their house, and/or how much it last sold for), you needed to look at yet more pieces of information.
Getting a house’s estimated price (something we can do in minutes on Zillow now), meant cross referencing a street directory with tax bills, then doing a title search at the hall of records to see the last time the deed had changed hands – and you might get 40 year old data that wasn’t useful.
A database lets all of that be tied together… and allows you to freely resort the information, either to answer specific questions (how most of us are used to using them), OR to just look at the assembled data in a new and interesting way. That second one is massive – prior to widespread functional databases, for an analyst who was looking at data, the ability to try out new ways of looking for insights was arduous at best – physically resorting all the information to match your new paradigm, on the off chance something valuable might be gleaned.
Latest Answers