How to Use Crosstab Queries in MS Access

Sometimes we just need to pivot our data in a way that makes sense for reporting purposes, and the crosstab query in Microsoft Access gives us that capability as we prepare data for reporting, statistics, or data science usage. Crosstabs are a very handy way to accomplish some otherwise difficult data transformation, and knowing how to use them is important for your data toolkit!
Related Videos:
How to Use Union Queries in MS Access
• How to Use Union Queri...
How to Split One Column Into Two in Access Using the Split Function
• How to Split One Colum...
How to Query the Last Row in a Series in Microsoft Access
• How to Query the Last ...
How to Format Dates in MS Access Queries, Forms, and VBA Code
• How to Format Dates in...
How to Use Nz in Microsoft Access to Handle Null and Empty Values
• How to Use Nz in Micro...
How to Use Crosstab Queries in MS Access
You are watching this video now!
How to Use Functions in MS Access Queries
• How to Use Functions i...
Want to get access to premium content made just for you and have a chat with me? Find me on Patreon :
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.co...
Want to work with me 1:1? Contact me today and book your free 20 min consultation!
Contact form you can find at www.mackenziema...
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a TH-cam Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
#CrosstabQuery #MSaccess
• How to Use Crosstab Qu...

[@demis4228]

Thanks so much for taking the time to make this incredible video! At 8:30 you have a nice crosstab with months. How would I add a standalone calculation such as average and standard deviation of all months within the same crosstab? Any assistance is greatly appreciated!

[@seanmackenziedataengineering]

No problem! Good question - to add another calculation like Average, you can create another field in the *design grid* by starting a new column, setting the Crosstab row to "Row Heading" then selecting Average in the Total row and adding something like MyAverage: MyField in the Field row. Then you'll have a new row heading with your calculation.

[@Tcreason23]

I enjoyed watching the video and I am wondering how I can put the date (column) to be in order from today's date 6/14/2023 to 7/8/2022?

[@seanmackenziedataengineering]

Wow, that would include hundreds of columns! Generally, you can just add the desired columns in the designer, in the order that you want. However, if you want to sort dynamically, you may want to format the date in a sortable format (like ISO) so that the date is sortable alphanumerically. ie. 2022-07-08, 2022-07-09, etc. Alternatively, you can just run your crosstab and then either create a query on the crosstab with all the fields you want in the order you want (usually you're going to use the crosstab with other data anyway), or just write a simple procedure in VBA to auto-generate the query on-the-fly. Good topic for a video!

[@Tcreason23]

@@seanmackenziedataengineering Trying to set this up for a weekly date, to pull data for how many cores were broke down that week.

[@ricardoglopes7687]

Excellent presentation. I did not figure out that it is possible to have Company and Employee, as it is possible in Excel. I have a question. The Column Headings field is for fixed data, not allowing entering a variable. I don't like hard coding as it stays buried in the SQL Sentence, and not easy to document it well. Do you have any idea how to force a column when there are no occurrences of one item of all possible strings?

[@seanmackenziedataengineering]

Thanks for the comment! You could try just adding one row to your source query using Union with a null in the value field. That may push the column you need through the model. Let me know if it works!

[@ricardoglopes7687]

@@seanmackenziedataengineering I did not try yet, but the usage of a Null in the value field is really an elegant solution. I will find a way to create this record without the Union as my set of queries is already many levels deep (maybe too many!).

[@tutsecret499]

How do I count AgeGroup range. I have the Age field, AgeGrp, Gender F, M. F-M, M-F. So which function I use on the query to count example how many 0-4 age group, 5-14, 15-25 and so forth. I automated the Age instead of typing, once I have the DOB, the Age field is fed with the age. Also how I can count these Age grp under each gender. Thank you.

[@seanmackenziedataengineering]

To simply count the AgeGroup range, you could just use Count with Group By in a Select query, like:
Select AgeGrp, Count(PersonID) From MyTable Group By AgeGrp Order By Age Grp;
for your gender question:
Select Gender, AgeGrp, Count(PersonID) From MyTable GroupBy Gender, AgeGrp Order by Gender, AgeGrp
Neither of these require a crosstab, but you could display the second query nicely in a Crosstab with gender as rows and AgeGrp counts as columns or vice versa.

[@AsanIT]

Hello Sir Hope you are doing well
Please give me some ideas on how to find the remainder number of products from the importproducttable and salesproduct
Actually we add some products into importproduct table and the when we sell the products we add the sales to salesproduct table and we need to find aggregate number of remainder of the imports and sales group by product name.
Regards

[@AsanIT]

I have created the sql query like the following
Select tb_imports.goods, sum(tb_imports.no_goods - tb_sales.no_goods) As Remainder
From tb_imports left join tb_sales on tb_imports.mid=tb_sales.goods
Group by tb_imports.goods;
It is show the correct answer for the imports and when add sales it does not shows the correct number

[@seanmackenziedataengineering]

You can do a query for each context where the numbers are correct, then join those two in a query based on product number. Each query will execute separately and get the correct calculation. Use these in a third query based on those two summary queries and you have your solution.

[@AsanIT]

@@seanmackenziedataengineering thank you so much for the reply

[@stanTrX]

Hello Sean. Is it possible to make a dynamic form from crosstab queries? I want to add new or remove columns etc.

[@seanmackenziedataengineering]

Never done that! I don't think it will be very easy, since you would need to change the form design on the fly. It can be done but will probably be programming intensive. Great idea for a challenge or something! Anyone else have ideas on how to do this?

[@sharonshires7934]

Hi, there. I went through your tutorial to be sure I was doing this correctly but I'm still coming up with a column that is blank with a column heading of . Do you know what is causing this or what it means?

[@seanmackenziedataengineering]

Good question! Check if you have any rows with null (empty or unknown) values in your category input column. You may have rows with numeric values but the category is null.

[@sharonshires7934]

@@seanmackenziedataengineering Thank you!

[@claudiosilva7697]

Excelent. Thanks

[@seanmackenziedataengineering]

You are welcome!

[@tutsecret499]

Is there a way to make a macro or VBA to do this routine task: I manually, go to the query, I highlight the whole column calculated age, copy, then I highlight the Age field and paste, and voila, the Age field from the table receives all the ages from the calculated age field in the query. So how I can do these steps via VBA or macro. I don't want user keeps doing this copy and paste over and over, it should be done via VBA behind the scenes. The reason we copy the already existing available age in the query is because they want the Age field from the table to be stored with the Age value/data.

[@seanmackenziedataengineering]

Great question! You can do this using a single Update query with a join, or using a VBA procedure. Alternatively, you could just have a query that joins the query and the table. It would show all the fields in the table plus the calculated field from the query. No update would be required, and whenever you opened the new query, the ages would already be recalculated. It would always be up-to-date.

[@tutsecret499]

@@seanmackenziedataengineering I agree with you, and that's the way it's was set up. The problem is the table that has the Age field does not update, unless I manually copy from the query and paste it on the Age field found either on the table or on the query. I want to skip the copy and paste process. The query has the following field Age from the table, and Automated Age expression field. So the Age field from the table never gets updated, unless I copy and paste it. I might be a better way to do it.

[@abrar1945]

why in crosstab Between [Start Date] And [End Date] is not working ?

[@seanmackenziedataengineering]

You probably want to create the Crosstab based on another query that has [Start Date] and [End Date] so that the filtering is resolved first, before the Crosstab operation. ie. Just make a query MyQuery like Select * From MyTable Where MyDate Between [Start Date] and [End Date]; Then make your crosstab using MyQuery.

[@abrar1945]

@@seanmackenziedataengineering thanks for reply,
Actually i was not making parameters stardate and enddate, now its working but now i am facing another issue.
When i am entering this formula in date criteria "Between [Forms]![Form2]![StartDate] And [Forms]![Form2]![EndDate]" to get data between dates , getting error that form isn't valid field or expression.

[@seanmackenziedataengineering]

@@abrar1945You had spaces in Start Date and End Date in the previous example. Probably just add spaces and it will work.

[@abrar1945]

@@seanmackenziedataengineering thanks 🙏