Transpose Data
By Richard Rost
4 years ago
Transpose Data in Excel & Normalize it in Access
In this video, I'm going to show you how to transpose data in Microsoft Excel, import it into Microsoft Access, and then set it up in properly normalized, relational tables.
Aarav from Bengaluru, India (a Silver Member) asks: I get budget sheets from my head office where the category does down column A and the years go across row 1. What's the best way to get this data into an Access database? I get fields called 2020, 2021, 2022, etc. and I know this isn't the way it should be.
Members
Members will learn how to add some extra functionality to our Budget form including filtering combo boxes, and a button to automatically add all of the categories from next year to our table.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Pre-Requisites
Links
Recommended Course
 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, transpose data in an access table, Transposing Data in Microsoft Access Tables, Transpose a Table, How do you transpose data in Microsoft Access, ms access transform rows to columns, convert rows to columns in MS Access, Reorganizing Access tables, how to convert horizontal data to vertical data in access
Subscribe to Transpose Data
Get notifications when this page is updated
Intro
In this video, we'll talk about how to transpose budget data in Microsoft Excel and then import and normalize it into a properly relational table in Microsoft Access. You'll see how to handle data where years are stored as field names, learn the easiest way to reformat it in Excel before bringing it into Access, set up the necessary Access tables, use append queries to move data, create relational forms, and generate cross-tab queries for reporting. We'll also cover tips like using reserved words properly and offer advice for connecting and sorting your data within Access.
Transcript
Welcome to another TechHelp video brought to you by accesslearningzone.com. I am your instructor, Richard Rost.
In today's video, we're going to talk about transposing data in Microsoft Excel and then normalizing it so it's in a properly relational table in Microsoft Access.
Today's question comes from Arov in Bengaluru, India, one of my Silver Members. Arov says, I get budget sheets from my head office where the category goes down column A and the years go across row one. What's the best way to get this data into an Access database? I get fields called 2020, 2021, 2022, and so on. I know this isn't the way it should be, and you are correct.
Arov showed me a sample of what he gets and basically it looks like this: He gets the budget sheet and every year they add another row on. There are a lot of them. The categories go down this way and the years go across like this. When you import this into an Access database, this becomes your field. I'm going to have a field called 2020, a field called 2021, and so on.
A few days ago, I did a video to show you this exact example and how to transpose it in Excel. To be completely honest, this is much easier to do in Excel. In fact, even if you get a database table sent to you like this, the easiest way to fix it is to copy it into Excel, transpose it, then copy it back into Access.
Can you do it straight into Access? Yes, but it's not easy and it involves some pretty tricky programming. If you really want to see it, let me know in the comments below and I'll show you. But here's the easiest way to do this.
Go watch that Excel video and this is what I end up with here. The simple solution is to take this data, copy it, go to a new sheet, right-click, go to Paste Special, click Transpose, and then hit OK. There you go. That's it. That's so much simpler in Excel.
Excel's got some really good points and purposes. For example, if you want to make charts, it's much better in Excel. I'll take data out of Access and chart it in Excel any day. Something like this is super easy to do in Excel and much more tricky to do in Access.
Now that you've got this, this is a piece of cake to pull into Access. Let's do that.
Before we get started, I have some other videos I want you to watch first if you don't know how to do this stuff. Very important. These are all free videos. They're on my website and my YouTube channel. Go watch these first.
First is importing data. I'm going to show you how to do it in just a minute. We're going to import that data that we just transposed in Excel and bring it into Access.
Next, go watch my relationships videos. You will understand how multiple tables work together with relationships, which is very important.
Go watch my normalizing data videos. You will understand what it means to normalize the data in a table so you don't have duplicate information, for example.
You should know how to make relational combo boxes. That's where you make a combo box to pick a value from a different table. Like on the order table, you pick a customer from the customer table.
And finally, append queries. You will need to know how to do this. You can take information from one table and append it into another one.
Go watch all those videos, then come on back.
I just transposed this in Excel. Let me rename this sheet so I know what you're going to get in. Let's call this one "To Be Imported," so I don't have to remember if it's sheet one, two, or three.
Before I forget, one thing I wanted to show you real quick is if you do get this kind of data sent to you in Access, and someone built a table this way - I get it all the time - the easiest way to do this is to just copy these records, control C, switch over to Excel, go to a new sheet, paste them in here, and just transpose this. Copy, right-click, paste special, transpose. There you go, it's fixed. Get rid of the ID if you want to, and now you have your information here that you can then import into Access the right way. It's so much easier to do this with Excel than it is straight in Access.
So I can close this down. Save changes. Sure, why not.
The first thing we have to do is import our data into our database from that spreadsheet we just made. So we're going to go to External Data - New Data Source - From File - Excel. Let's find our file. Browse. Mine's on my desktop, I think. Let's see here, Desktop, and it's called Transpose. There it is right there. Import.
The data I want is "To Be Imported." See, that's why I change it. It's easy to see.
Next: First row does contain our column headings.
Next: I'm going to change "budget" to "budget year." Data type is going to be a long, and these guys are all going to be currency eventually, but for now, it really doesn't matter because we're going to put this data into a different table anyhow.
Primary key really doesn't matter because again, this is just a temporary table.
Next, let's call this "budget import t." This is my budget import table. If you are going to do this on a regular basis, you can save these steps. I'm just going to pretend this is a one-off. I'm just going to do this once, get it fixed, get it working properly, and then use Access from that point on.
If you have to deal with this on a regular basis where they keep sending you new data, you might want to save the import and save these steps.
Here's my budget import table. That's what it looks like. We've got the budget year. Remember, "year" is a reserved word. You don't want to use just the word "year." So use "budget year," and all of these over here are categories.
This isn't the best way to store this data. Why? Because now you're limited to just these categories. What if you want to add another one, like "cleaning services," for example? The user should be able to add categories by adding data to a table. You shouldn't have to make design changes to your table to add more categories.
What we're going to do is peel these off into their own table.
Go to Create - Table Design. This will be our budget category. So, "budget category ID" - that's our auto number - and then the description. Remember, don't use the word "name." I see a lot of people use the word "name" as a field, but "name" is a reserved word. Don't use that.
Reserved words: Alex keeps a nice good list of them on my website. Here they are. There are lots of them. Don't use these as field names.
I'll put a link to the reserved words page down below. You can click on that in the glossary.
Let's save this table as "my budget category t" - my budget category table. Set the primary key.
Now, here is where we're going to put in our different categories. Again, it's just a one-off thing, so we're just going to type in Electric, Water, Telecom, Labor, Shipping, Rent, and whatever else you've got. You can get into subcategories, but that's a different lesson altogether.
There's our list of categories.
Now we have to make a budget table. Each record in the budget table is going to be a year, a category, and a value. Because right here, this is all just not right. So what we're going to do is make a year, the category, and then the value as a record.
Create - Table Design. This will be our budget table. So, "budget ID" (auto number). We're going to need a budget category. That's going to be from this table over here, one through six right now. This will be a foreign key, which will be a number (type: Long Integer) from the relationships video, just like customers and orders. You'll have the customer ID in the customer table (primary key, auto number), and you'll also store the customer ID in the order table as a foreign key, just the numbers. That's how you can tell the relationship. This is how we can tell what category this budget item belongs to.
Then we have the budget year. That'll just be a number. We're just storing the year for this case; we're not doing an actual date. If this were an actual date, you'd store it in the date field and you'd have to put the full date in there. But we just want the year, that's fine.
Then we have the actual amount of the budget. That'll be currency.
Save this as "budget t."
Just to show you how sample data is going to look in here, I'll just start showing you manually. I'll copy some of these over. The electric from 2020: Electric is category one, the year is 2020, the amount is 100. See how that works? This is a properly relational setup.
Next over would be the water from 2020: Water is category two; the year, again, still 2020; and the amount is 55.
Now, I don't want to have to type all these in. There's only six times three; that's eighteen values. That's still a lot of typing. What if you've got fifty categories and twenty years of data? So what we're going to do is use some append queries to copy that data into the right table.
Let me delete this stuff that we have in here right now.
We're going to use an append query. Unfortunately, the way this is set up, we're going to have to do it one category at a time. So we have to run the query six different times, changing it slightly each time. If this is something you're going to have to do on a regular basis, you're going to need six different queries that you can run to do each category.
Can you do it without multiple queries? Yes. It's possible with some VBA; you can actually parse down the field names in here. It's a lot more complicated than I'm going to show you right now, but if you really want to see how to do it, let me know and I'll do it in a developer lesson because this actually involves reading the table names and the table and stuff, and it's pretty complex. But if this is just something you have to do once to fix it, this isn't that hard to do at all with some append queries.
So, let's go to Create - Query Design. Make some room here. Bring in your budget import query. Now, what do we want to move to the other table? I want to move the budget year and then each one of these categories at a time (we'll start with Electric), and then we have to assign a value to the category ID. So make an append query (appending into our new budget table that we just made).
First up is budget year. Now we'll start with Electric, and Electric is going to go into the amount field in the budget table, right? The value from Electric for this year is 100. That's going into the amount. But I also have to set the budget category ID. What is that? Electric is equal to one. So this becomes a one. Just put a one in there, hit tab, and Access changes it to expression one. That's fine. Don't worry about that.
I'm going to save this as my budget append query. If you have multiple categories and want to save these, you can have budget append 1 query, budget append 2 query, and so on, just to keep it simple. But if you're just doing this once, you can just modify and rerun this query.
Run this query. Remember, I have my warnings turned off. Otherwise, you'll see that you're about to run an append query (it's going to modify six rows or whatever).
Budget table: There it is. There's my three records from the different years (2020, 2021, 2022) for category one. Those are all the Electric values.
Now, switch from Electric to Water, and Water is two, then run it. Switch from Water to Telecom (Telecom is three), then run it. Telecom to Labor (Labor is four), run it. Shipping (five), run it. The final one is Rent (six), run it.
Close that. Let's take a look at our table, and there's our data. See, one, two, three, four, five, six, and then values for each year, and there's the amounts.
That was relatively fast to do. I only have six categories I'm working with. If you've got ten or fifteen, it might take you a few minutes. But once you get this set up, now the database is set up properly. This is a properly relational set of tables.
If you want to pull this together with the category information so you can see it all together, go to Create - Query Design, bring in the budget table and the budget category table. The relationship should be made for you automatically because it sees the same budget category ID in both tables.
Budget ID, category ID, description of the budget, the year, and the amount. If you run it now, there's all your stuff together. Those are those two tables joined together.
Save this as "budget q" (budget query).
How do we work with this data in Access? We can make a form based on the budget table, a continuous form.
We already have a continuous form from our template. If you haven't watched my blank template video, go watch that. That's a prerequisite for all the other videos I told you to watch, so you should have watched it by now, and that explains how to make a continuous form.
I'm going to copy this guy (control C, control V). This will be my "budget f" (budget form).
Let's design you. Let's bind this table to data budget t. We're going to put in here the year and the amount. Let's just do this: get rid of you, go to "add existing fields." The category - we're going to make a combo box so we can pick that. Really, all we need to pull into here is the year and the amount; we don't really need to see the ID. Drop it in there like that. Get rid of these two little labels that come in, slide you up there, and I'm going to put the combo box in the middle. So it's going to be year, category, amount. We'll put the amount over here.
Let's see what we got here: double click, and that's budget year, that's amount. Move these labels where they belong. This one will just say "year." That's a label, just a caption - you can have that in the caption. That'll be the amount.
One more - copy, paste, slide you right there, and this will be the category.
Let's put a combo box right here. Grab our combo box, drop it down here. We're going to get the value from a table or query. What table has the list of budget items in it? The budget category table. Bring over both of those, because remember we're storing that ID and we want to see the description. Sort by description.
The key column is hidden (that's our ID), and that's what the box looks like when you open it up. We're going to store that value in the budget category ID in the budget table. Delete the label (doesn't matter), then finish.
My pet peeve with the combo box wizard is that it doesn't let you change the name of the box, so you have to come out here and change this to "budget category combo." You can call it ID if you want to; I like to call them "combo."
One thing I know we're going to have to do is change your tab order. You can go tab tab tab right across. Slide that up, slide that left.
You can put a total box down here if you want to. I'm going to copy this guy, click down here, paste, slide you to the right, and I'm going to change this to "sum amount." Control source will be "=Sum([Amount])" (in other words, summing up all the amounts from the text boxes above it). I'll make this gray because it's a calculated field, so the user knows they can't change it.
Last two things I did, in case you don't know: Tab order is covered in this video; form footer totals (putting a total down at the bottom) are covered in this video. If you want to learn about something next, I have a video for it.
If you really want to learn about all this stuff in the right way, step by step, that's what my full course is for. You won't be jumping around between different videos; you learn this, then that, then the next one, and it's all presented in order for you.
Now let's close this guy down, save changes. Let's open it back up and there's our form.
I'll do a little bit of formatting here. I like all my stuff left aligned. I know a lot of people complain about this; numbers should be lined up to the right. I know accountants like their currency values lined up to the right. I don't. That's how I like it. I like everything left aligned. That's just me; that's my preference. You don't like it, do it your way. It's your database. That's the beauty of using Access. You get to build it exactly the way you want it. You don't have to listen to me if you don't want to.
If you want this stuff sorted a different way, because right now it looks like it's sorting based on category, you can change the sort. I recommend making a query that has this stuff sorted the way you want it.
For example, let's say you want it by year descending and then by category ascending, meaning you want the newest year up top. If you get 1972 in here, that will be at the bottom. You want the new stuff at the top, and then the category can be sorted alphabetically.
We'll make another query: Create - Query Design, add in budget t. Actually, we already have a query; let's go back to that query (budget q). Let's just change the sort in this, and then we'll make the form get it from this guy.
Design view: how do you want to sort this? We'll sort by year descending. I always take sorted fields and move them to the left. That's important if you have multiple fields and multiple sort orders. Then we'll have the description, which is the category name. We're going to sort that ascending. Slide that to the left like that.
Since "description" could be in multiple tables, the word "description" could be misleading, so I'm going to put in here "category description" like that. That's called an alias. When it runs, it gives it that name (category description) or "category name" or whatever you want to call it.
This actually isn't in the form because the form is a combo box. It's based on this guy, so this is just for sorting purposes.
Now look at that - it's got the year in reverse order, and then each year has the category sorted inside of it like that.
Now we'll use budget q for our form. Make sure that the query has all the fields that your form needs.
I'm going to drop a button real quick on this main menu because this is annoying me. We're going to put a budget button right here for operations.
Forget the command button wizard. We're going to use a tiny bit of VBA. The command button wizard is OK, but I hate embedded macros. Learn a tiny bit of VBA folks. I'm telling you, just a little bit makes your database a whole lot more powerful.
I wasn't going to do any VBA in this lesson, but you might as well learn a little. This 20-minute video is free and teaches you all the basics of VBA. Go watch this video right now if you've never done any VBA before. I'm literally going to use one line of code.
This is going to be my budget form. Let's give the button a good name - "open budget f button," whatever. Then right click, build event; that opens up my code builder. You can ignore all the rest of this stuff (from previous lessons). Literally one line of code in here:
DoCmd.OpenForm "budget f"
That's it. That's all you need. Remember one command and put the form name in there. That's so much easier than even running through the wizard.
Close it. Open up the main menu again. I've got a button up here to open up my main menu, which runs this macro, which opens up this form.
Budget form. There it is.
Now let's change where this guy gets his data from. So instead of budget t, we want budget q. That's it.
Now when we open up our form, you can see it's already sorted the way we wanted it: 2022, 2021, and so on. If you want to filter this stuff, you can right-click in here and say "equals 2022." There's your 2022 stuff. Or you could say, "Show me all the Telecom stuff" - right-click, "equals Telecom." There's your manual way to do that.
In the extended cut for the members, I'm going to make some little boxes up here where we can do that much easier. This is the extended cut database. Just drop this down, 2021 - boom, there you go. Clear it. Drop this down, Shipping - there you go. Little bells and whistles for the members. These make it much easier for people who aren't Access experts to do the filtering and sorting.
Now, what if you want to see this data the way that it was initially sent to you? Your boss says, "Hey, we've been doing it in this Excel spreadsheet since day one, 15 years ago. I want my reports to look like this," even though you tell them, "Well, it's not properly relational, and Access doesn't like it." But he doesn't care. Bosses seldom do. Trust me. I've had to deal with corporate higher-ups my entire career. When they want to report a certain way, it's got to look that way.
This is what a cross-tab query is for. Want to do a cross-tab query? Go watch this video. I'll show you real quick how to do one, but go watch that if you want to learn more.
Let's cross-tab this stuff. Create - Query Wizard this time (my hand just naturally goes to query design; I almost never use the wizards - this is one of the only wizards I use: the cross-tab query wizard).
What table or query has the data? Go to your queries, go to your budget q that we made.
Next: The row heading is the side over here. You can put this either way: if you want the years down here and the categories across the top, or the years across the top and the categories on the left. It's just like a pivot table in Excel.
The boss says he wants it like this: our row headings will be our categories, so category description.
Next: The column headings will be the year (budget year).
Now, the actual data in here is going to be the sum (not budget ID), the sum of "amount." I don't want row sums. You could do a row sum if you want to; it'll sum up the entire row, so all of your electric expenses, for example. If you want, leave it on. I'll leave it on since I didn't leave it on the last one (the cross-tab query video I told you to watch).
Next: Let's change the name to "budget cross tab q" like that, and finish.
There it is. That's exactly how your boss wants to see it, with the exception of this being over here instead of at the end. I like it at the end better. There are each category and each year.
If you want to make this all pretty and have it ready by one o'clock, just select everything, copy it, flip back over to Excel, go to a new sheet, paste it in, and make it pretty. Excel is great for just taking things and making them look good quickly for a presentation, so you want to pretty this up and drop it into PowerPoint or whatever.
You can make a nice pretty Access report and format this and have it all jolly. If this is something you're going to do every month, that's the beautiful thing about Access: it's for things you're going to do over and over again. I need this report every Wednesday. Take an hour, build a report, make it pretty, and that way from now on, every month you just have to click one button and there's your report. But, if it's just a one-off thing, something you have to do quickly, just take this, drop it into Excel, make it pretty, and you're all set for your meeting.
The major difference between Access and Excel is that Access, I think, is more powerful in a lot of ways. It's designed for giving you an application with a user interface that makes it easier for you to automate things, like click a button and get your pretty report. But it takes more time to set it up and do it right, but once you set it up and do it right, you'll save a lot of time in the long run.
With Excel, you can do all this stuff, but you have to keep manually changing and manually updating things. You get the point.
Save that. There is the cross-tab query right there, and the append query.
So that is how I would take data from Excel that looks like this, transpose it to that, then drop that into Access with a couple of simple append queries and some cross-tab queries. You've got everything you want, everything you need in Access to handle all that, and now it's so much better.
Members, what do we have coming up? We're going to build that database I showed you. We're going to put some filter combo boxes at the top. We'll make a button over here to clear the filters, and the big one: we'll make a button to add the next year.
See, rocking along with your budget database, you can change that guy to filter. You can click that button to remove the filter. You can turn this other filter on if you want, or you can do them both. The boss says, "I want to add 2023's data to this." No problem. Add next year. Yes, whatever. Boom, there's 2023. Add next year. 2024. Yep. It's all automatic and it's all done with VBA code. Not a ton of code. That's some pretty cool code. But that is all covered in the extended cut for the members.
Silver Members and up get access to all of my extended cut videos - there are lots of them now; I've lost count - and Gold Members can download these databases that I build in the TechHelp video.
So what are you waiting for? Join today.
That's your TechHelp video for today. I hope you learned something, and we'll 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'll 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'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you've finished 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. 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. It will be shown in each video as long as you're a sponsor. You'll 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.
Don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.
Quiz
Q1. What is the main challenge with importing budget data from Excel into Access as described in the video?
A. The budget data in Excel is already relational and ready to import
B. The budget years are listed as records down a column, making it perfect for Access
C. The years are formatted as fields across columns, which is not ideal for relational Access tables
D. No challenge exists; importing is straightforward
Q2. What feature in Excel is suggested to restructure data before importing into Access?
A. Pivot Table
B. AutoFilter
C. Paste Special with Transpose
D. Text to Columns
Q3. Why is transposing the data in Excel before importing into Access preferred over handling it directly in Access?
A. Access cannot import Excel data
B. It is much easier and faster to transpose in Excel
C. Excel automatically normalizes data during import
D. Transpose feature is exclusive to Access
Q4. When importing transposed data into Access, why should you avoid using the field name "year"?
A. Year is not a descriptive term
B. Year is a reserved word in Access
C. Year is too long as a field name
D. Year is not accepted by Excel
Q5. What is the recommended naming convention for the imported table containing the raw data?
A. "raw data t"
B. "budget import t"
C. "category data t"
D. "budget main t"
Q6. After importing, what must be done to properly normalize the budget data?
A. Delete all duplicate records
B. Create separate tables for categories and budgets, establishing relationships
C. Keep all data in a single table
D. Only create an index field
Q7. What should NOT be used as a field name in Access, according to the video?
A. "budget category ID"
B. "description"
C. "name"
D. "amount"
Q8. What relationship exists between the budget table and the category table in a normalized Access database?
A. Many-to-many
B. One-to-many from budget to category
C. One-to-many from category to budget
D. No relationship required
Q9. What is an append query used for during the normalization process?
A. To delete data from a table
B. To update field names
C. To transfer data from the import table to the relational tables
D. To export data back to Excel
Q10. Why might you need to run multiple append queries when normalizing the imported budget data?
A. Each category must be appended separately
B. Append queries can only run once per table
C. It is required for all Access operations
D. To duplicate the data in several tables
Q11. What is a combo box used for in the budget form discussed in the video?
A. To enter free-text category descriptions
B. To pick a category from the related category table
C. To auto-generate categories
D. To select a year only
Q12. How can you display the data for the boss in the original spreadsheet layout inside Access?
A. Use only table views
B. Create a cross-tab query
C. Print the table as is
D. Use a Make Table query
Q13. What advantage does Access offer over Excel for repeated reporting tasks?
A. Access can't automate reports
B. Access is faster for one-time operations
C. Access allows building repeatable reports with a single button click
D. Excel is always better for all report types
Q14. What is the main difference when using Access instead of Excel for managing this kind of data?
A. Access provides a user interface and automates processes, but takes more setup
B. Excel is better for normalization
C. Access can't create relationships
D. Excel provides better data security by default
Q15. What is the reserved word documentation Alex maintains on the website important for?
A. Finding macros
B. Avoiding field names that may conflict with Access reserved words
C. Listing all possible VBA commands
D. Suggesting only table names
Q16. What is the final suggested action if a boss insists on seeing a report in the original spreadsheet layout?
A. Refuse to provide it
B. Manually recreate it in Access each time
C. Use the cross-tab query to recreate the layout and, if desired, export to Excel for formatting
D. Abandon Access in favor of Excel
Q17. What is the purpose of using a form with a continuous layout for the budget in Access?
A. It allows input of multiple, related records conveniently in a single interface
B. It only works for printing
C. It restricts data entry to one record at a time
D. It's only for expert users
Q18. To make a button open the budget form using VBA, which command is used?
A. OpenBudgetForm("budget f")
B. DoCmd.ShowForm "budget f"
C. DoCmd.OpenForm "budget f"
D. Form.Open "budget f"
Q19. Why might you build queries that sort your budget data by year descending and category ascending?
A. Sorting is only aesthetic and serves no purpose
B. To display the newest budgets first and group categories alphabetically for better analysis
C. To confuse the users
D. Only Excel can sort data
Q20. What is the primary benefit of saving import steps when regularly receiving similar budget data from head office?
A. It reduces the need for backup
B. It makes the process faster and less error-prone
C. It is only necessary for advanced users
D. It locks the tables from changes
Answers: 1-C; 2-C; 3-B; 4-B; 5-B; 6-B; 7-C; 8-C; 9-C; 10-A; 11-B; 12-B; 13-C; 14-A; 15-B; 16-C; 17-A; 18-C; 19-B; 20-B
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 how to transpose data in Microsoft Excel and then normalize it into a properly structured relational table in Microsoft Access.
This question came from a viewer who regularly receives budget sheets from their head office. In these sheets, categories are listed in one column, while the years are listed across the first row. Each year becomes a field, such as 2020, 2021, 2022, and more. While this is a common format in Excel, it is not ideal for a relational database in Access.
The initial data format typically looks like a table where new years are added as additional fields, so every year results in a new column. Importing this directly into Access causes each year to become its own field, which is not optimal. Managing such data effectively in a database requires transposing the information so that years become individual records rather than columns.
Transposing data is much easier in Excel than in Access. Even if you receive a table formatted like this in Access, the fastest way to fix it is to copy the data into Excel, transpose it there, and then bring it back into Access. While it is possible to handle everything directly in Access, it involves complex programming that is unnecessary for single-use situations. For those interested in a more advanced, all-in-Access method, I welcome suggestions in the comments.
Here is the most straightforward approach: transpose the data using Excel. Copy your original data, go to a new sheet, use the Paste Special option, select Transpose, and confirm the change. Excel handles this task very cleanly.
Once your data is transposed, it becomes much more manageable to bring it into Access. Before proceeding, I recommend familiarizing yourself with a few key Access topics. I have free tutorials available on how to import data, understand relationships between tables, normalize data to avoid duplication, create relational combo boxes, and use append queries. These foundational Access skills will make the process much smoother.
With the data transposed in Excel, name the sheet accordingly―for example, "To Be Imported"―so it's easy to find during import. Even if you are already working in Access and receive data in the wrong format, you can simply copy it, paste it into Excel, and transpose it in the same way.
Now, begin the import process in Access. Use the External Data tools, choose Excel, and select the transposed spreadsheet. Specify that the first row contains column headings. Adjust your field names to reduce conflicts with reserved words in Access; for instance, avoid using "year" alone as a field name. Call it "budget year" instead. Import this to a temporary table, since you're only staging the data before normalizing it.
At this point, you might recognize the limitation of the original structure: categories exist as individual fields, which restricts flexibility. To improve your database design, create a dedicated table for budget categories. Give each category a unique auto number as its ID and a description field for the category name. Avoid using reserved words as field names; refer to the reserved words list on my website if needed.
Manually enter your existing categories into the new table. If you ever want to implement subcategories, that's a separate, more advanced lesson.
Next, create your main budget table. Each record in this table should represent one year, one category, and one budget value. Structure the table with an auto number ID, a foreign key to the budget category table, the budget year, and the amount (using the currency data type).
To populate the main budget table, you want to move the transposed data into the new normalized structure. Sample records might look like Electric for 2020 with a value, followed by Water for 2020, and so forth. Manually entering data is impractical beyond a handful of records, so use append queries to automate the transfer.
Since each category is now a column in your import table, you will need to run one append query for each category. Each query grabs the relevant data for one category along with the year and value, and assigns the proper category ID. If this process is a regular occurrence, save separate queries for future use. Scripting a single automatic process is possible with VBA, but it's more complex and suited for advanced developer scenarios.
After executing the required append queries for each category, your budget table will now have one record for each combination of year and category with the correct values. This is now a truly relational structure and far easier to work with.
To view the budget data along with the category descriptions, create a query that joins the budget table to the budget category table. Access will automatically establish the correct relationship through the foreign key. You can then see the year, category description, and amount together in a single result set. Save this as your main budget query.
For data entry and display, set up a continuous form based on the budget table. Use a combo box to select the category, pulling descriptions from the budget category table while storing the category ID. Add fields for the budget year and the amount. Organize your form for clarity, and consider adding a calculated control to sum the amount at the bottom of the form. Details on configuring tab order and calculated totals can be found in my other recorded lessons.
If you prefer your data sorted by certain fields, such as by year in descending order and then by category alphabetically, adjust the sorting in your query and use that query as the record source for your form. You can also edit field labels for something more descriptive.
To streamline navigation, add a button to your main menu to open the budget form. I recommend using VBA to manage button actions―even just learning basic commands will make your database much more powerful and flexible than embedding macros.
Once configured, you can filter and sort your records directly within your form or by updating the query. In Access, filters can be applied quickly by right-clicking on values. In the Extended Cut for members, I demonstrate building user-friendly filter combo boxes directly on the form, adding buttons to clear filters, and presenting an easy method for adding a new year to the budget automatically. This advanced automation uses a modest amount of VBA to make the process efficient for long-term use.
If your boss or colleagues still want to see the data in the original spreadsheet layout, create a cross-tab query in Access. This works like a pivot table in Excel, letting you display years across the top and categories down the left, summarizing the amounts. You can then copy the result back into Excel for presentation, or build a customized Access report if you need a recurring solution.
The main takeaway is that Excel works well for quick, flexible data manipulations and presentation, while Access shines when you want to build a well-structured, scalable, and automated system for managing and reporting your data. Setting up Access properly takes time, but it pays off in efficiency and accuracy, especially when you need to repeat tasks over months or years.
For members, in today's Extended Cut, I go over building advanced filtering options right on the form, adding a clear filter button, and automating the process of inserting a new year into the database. All of this is wrapped up using VBA. Silver Members get access to all Extended Cut videos, while Gold Members can download the complete databases demonstrated in my tutorials, and Platinum Members gain even more benefits.
If you want the full video lesson with step-by-step instructions for everything discussed, you can find it on my website at the link below. Live long and prosper, my friends.
Topic List
Transposing data in Excel using Paste Special
Normalizing spreadsheet data for Access import
Renaming Excel sheets for easier import tracking
Importing transposed Excel data into Access
Handling reserved words in Access field names
Creating a budget category table in Access
Populating category tables with sample data
Designing a normalized budget table in Access
Setting up foreign key relationships between tables
Using append queries to move data into relational tables
Running multiple append queries for each category
Building queries to join budget and category data
Creating and customizing a continuous form in Access
Binding forms to queries for enhanced sorting and filtering
Customizing combo boxes to display category names
Adding calculated total fields to Access forms
Adjusting tab order and layout on forms
Sorting and aliasing fields in Access queries
Adding command buttons to forms using VBA
Opening forms with VBA code in Access
Creating cross-tab queries for pivot-style reports
Copying cross-tab query results to Excel for reporting
Comparing Access and Excel for data management tasks
|
