“Guys today. I m gonna do introduction tutorial and access on making pivot tables out out of a table. So here s our table. If you ve been following along the access tutorials.
You ll be familiar with this table. I actually added a extra column of the state and so basically we want to make a pivot table. Which if you re not familiar with is a pretty common tool in excel. But we want to do it in access.
Okay and maybe we want to make a pivot table that summarizes the total salaries by each state. Ok so pretty much what we want to see is how the total salary in new york. The total salary in connecticut. Total salary in texas ok.
So let s close this table. Start with a blank slate click on the table of interest just once and go to create more forms pivot table ok and if you re familiar with pivot tables you ll know that they re quite flexible tools so first things first you want to add fields so click on this blank area and you get this pivot table field list ok now the pivot table. We re making here is quite simple it can get more complicated. But the cool thing about pivot tables is when you make a mistake you can always correct your mistake.
Quite easily. And i ll show you example of that okay so first let s say i want to take state. I grab state and i drag it and you see as i go over different areas of this blank area. I get these blue highlights.
Okay so i can drop this when i let go of the mouse..
I can drop this field of state into these areas so let s start by throwing it into the column area okay. So you see here s our state s and it puts it in alphabetical order. But we don t have any data in this area over here. So there s nothing really that it s showing us.
Yet so the next thing is what we set the salaries in so let s grab salaries and since salary is data right in the form of numbers. We re gonna throw it into this drop totals or detail fields here okay and you see now what axis. Automatically does is it compiles the connecticut salaries. The new york salaries and the texas salaries.
And it hasn t really given us a total or we can do is we could play around with it before we do anything else we can throw it we can grab state click drag drop it into the rope area and then we can pull it back and drop it up back up to the column area. We can even grab it up and put it up here into the filter area and over here. We d actually have to select connecticut and we see connecticut right or any combination okay. But for our purposes.
I think row looks pretty neat. So we ll stick with row. Okay now let s say. We also want to add a little bit more detail into this pivot table by adding.
The people who work or who reside in each of these states. So we can grab let s say their last name grab drag and we put it right next to state and see how that looks okay. So that looks pretty good shells in connecticut. We have these two people new york.
We have these three people and in texas..
We have these two people right and these are their respective salaries right now again i encourage you to play with this stuff until you get it to look how you like here you see i pulled it slightly to the right and made a slightly slight difference in the look and the feel okay see just a little bit of difference. And you know i encourage you to play around with it honestly with pivot tables. It s one of the things. It might not pay to spend too much time learning.
Every single detail might play or to just play with them. And you know see what you get okay so we have some options here we can right click on let s say state and we can go we can sort by ascending in which case it s i was already sorted by sending now we can sort it by descending. Now we can go back to ascending. We can go within state and we can also play with some other options we can show details we can hide details right within salary.
We can right click we can say auto. Calculate sum and that will give us what we originally intended which was give us the sum in each state. So the sum in connecticut of salary is one hundred and seventeen thousand and these are the two people these are the individual salaries. I can click this minus sign get rid of the individuals of all these states and just see the totals.
I can second hit plus and get the individuals back. I can get rid of this grand total actually. There s an option here to not show this i believe we ll find that but in the meantime get back to salary. We can also remove the grand totals.
We can grab it again throw it in the de area. We can see other things like the count. There s two people in connecticut. Three people in new york two people in texas.
There s other things like average minimum maximum and all kinds of other statistical stuff we can do here we can go to the properties of the sheet and play with some other options..
There s loads and loads of options that we can go on and on about to no end. But the main idea is to play around with it and until you do have it to a place where it looks how you want it to look okay and by the way to get rid of that subtotal. We right click on state. The grand total we right click on state and we remove subtotal we uncheck subtotal okay.
I think the count is extra it doesn t look that good so let s get rid of those fields let s drag salary. Again you can always start from near that s the great thing about pivot tables. Okay so we can auto count and just say some uc keeps adding these things so to route to to just get what we want we must actually click on that particular aspect. And say remove so we don t want the counts.
So click on counts remove this is kind of a nice view in my opinion of each state. We get to see the people in the state and their salaries and the total salary for the state. Which was our original goal. But we played around with a lot of stuff in order to just see get familiar with the workings of pivot tables okay so again these over here are your fields from your table okay so what you re gonna do is you re gonna grab the fields of your you re interested and you re gonna drag them and drop them into these areas of the pivot table if you like them you keep them.
If you don t like them you can move them around right like this if you totally want to remove them you can grab them drag them a little bit off this area. You ll get this delete sign that you see right here you see that change and you get rid of it and then you can start fresh. So pivot tables are really cool they re really useful to summarize data. And if you don t know how to use them in excel.
They re really important to another tool in excel so make sure to watch that excel tutorial video on tables. Okay. I m gonna do a video right after this on how to make pivot tables pivot charts in axis which is which just adds one other dimension to what we re doing here. And that dimension is it also charts this stuff into bar graphs line charts pie charts whatever you need okay so i hope this video was helpful make sure you watch the other access videos subscribe to the page share and practice practice practice have a great ” .
Thank you for watching all the articles on the topic Access – Pivot Table . All shares of thetruthaboutdow.org are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.
“Many more great Excel and Access tutorials linked below:rnhttp://www.youtube.com/rdjalayerrnrnBe sure to Subscribe so that you get access to new Excel and Access tutorial videos when they are released, including more advanced techniquesrnand many useful and practical ones. Thanks for watching!”,
Access, pivot, table, pivottable, pivot chart, pivot table, fields, records, tutorial, teach, access tutorial, functions, formulas, video tutorial, trick, ti…