# MS Access better practices? **URL:** \ **Category:** In My Humble Opinion\ **Created:** [April 22, 2015, 3:14pm UTC](https://boards.straightdope.com/t/ms-access-better-practices/718289 "2015-04-22T15:14:13Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![FasterThanMeerkats](https://avatars.discourse-cdn.com/v4/letter/f/278dde/32.png) [@FasterThanMeerkats](https://boards.straightdope.com/u/FasterThanMeerkats)\ **Post date:** [April 22, 2015, 3:14pm UTC](https://boards.straightdope.com/t/ms-access-better-practices/718289/1 "2015-04-22T15:14:13Z")
Ok, the thread about making Excel faster prompted me to ask the following questions about Access (currently using 2013): 1. Is there any easy way to create pivot table format reports (crosstabs?) in Access without having to create special union queries and all that? In my mind I’ve already got the data tables and relations built, so why do I need specialized queries for a single report? It’s impractical because I need a lot of reports and don’t have time to build/maintain tons of specialized queries. By comparison, in Excel I can create reports with ease with pivot tables or sumifs and lookups and don’t need to create 2-3 staging worksheets for each. Access seems to not like crosstabs, and I find that all useful management reports are essentially crosstabs. 2. What is the best way to map daily transaction dates (e.g. 2/18/15) to a monthly calendar so I can have a 12-row column of Jan, Feb … Dec? This is relatively trivial in excel using a month() function but appears to be a pain in access. As you may have guessed I’m an excel guy and if there weren’t space/computation limits I would never need anything else. All help appreciated. ---
**Author:** ![FasterThanMeerkats](https://avatars.discourse-cdn.com/v4/letter/f/278dde/32.png) [@FasterThanMeerkats](https://boards.straightdope.com/u/FasterThanMeerkats)\ **Post date:** [April 22, 2015, 3:21pm UTC](https://boards.straightdope.com/t/ms-access-better-practices/718289/2 "2015-04-22T15:21:21Z")
Also, can we please move this to GQ? Forgot which board I was on. ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [April 22, 2015, 7:46pm UTC](https://boards.straightdope.com/t/ms-access-better-practices/718289/3 "2015-04-22T19:46:20Z")
> [@FasterThanMeerkats](#): > > 1. What is the best way to map daily transaction dates (e.g. 2/18/15) to a monthly calendar so I can have a 12-row column of Jan, Feb … Dec? This is relatively trivial in excel using a month() function but appears to be a pain in access. > > All help appreciated. That’s actually relatively simple - there’s a MonthName() function in Access that allows you to get the name of the month. In your case you will want a calculated field that’s something like ```auto MonthName(Month([TransactionDate]), True) ``` I’m not quite sure what you mean by “a 12-row column” - doest that mean you want to group by the month name, so that your output query only has 12 rows, or that you want to add an additional column to the table that contains the month name, with the same number of rows as the table? The solution I’ve given is the latter, not the former. If you could give an example of where you have to create specialised queries for a crosstab report, that might help. It’s definitely possible to create a crosstab query relatively simply, but if your underlying data changes in a way that makes you change the query, the problem might lie with your data structure. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [April 23, 2015, 1:29am UTC](https://boards.straightdope.com/t/ms-access-better-practices/718289/4 "2015-04-23T01:29:11Z")
> [@Dervorin](#): > > … I’m not quite sure what you mean by “a 12-row column” - doest that mean you want to group by the month name, so that your output query only has 12 rows, or that you want to add an additional column to the table that contains the month name, with the same number of rows as the table? … I interpret that he wants to take his detail transaction data where each record includes, say, department, transaction date, and amount, and produce a rectangular output that is 12 columns wide (one for each month) and however many departments tall where each “cell” in the result matrix is the total of amount for that department and month. Which absolutely can be done in SQL, but is a kludge no matter how you express it. ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [April 23, 2015, 9:00am UTC](https://boards.straightdope.com/t/ms-access-better-practices/718289/5 "2015-04-23T09:00:42Z")
> [@LSLGuy](#): > > I interpret that he wants to take his detail transaction data where each record includes, say, department, transaction date, and amount, and produce a rectangular output that is 12 columns wide (one for each month) and however many departments tall where each “cell” in the result matrix is the total of amount for that department and month. > > Which absolutely can be done in SQL, but is a kludge no matter how you express it. Actually, in Access it’s not as much of a kludge as it in in SQL Server. Access allows dynamic pivots without specified values, unlike SQL Server where you have to explicitly state the column values for which you want to pivot the data. Once you get the month name, using the approach described above, it’s relatively easy to set up a crosstab query that gives you a rectangular output 12 columns wide, with as many departments as there are in the data set. It behaves much like an Excel pivot table in this regard. ---
**Author:** ![Melbourne](https://avatars.discourse-cdn.com/v4/letter/m/b5e925/32.png) [@Melbourne](https://boards.straightdope.com/u/Melbourne)\ **Post date:** [April 23, 2015, 10:45am UTC](https://boards.straightdope.com/t/ms-access-better-practices/718289/6 "2015-04-23T10:45:56Z")
> [@FasterThanMeerkats](#): > > Access seems to not like crosstabs, and I find that all useful management reports are essentially crosstabs… It’s been a long time, so I can’t offer any useful advice, but yes, all useful management reports are essentially crosstabs, and no, I only rarely used union queries, (only when there was an error in the data design) never used that pivot table thing, and didn’t have to always create a crosstab query to get a cross tab report. The report itself is flexible enough to do ANYTHING, but that probably wouldn’t help you. But I think we did do simple crosstabs in the report query ??? And we certainly reduced the number of reports by putting in an input box, to customise/generalise the data source. ---
**Author:** ![JerrySTL](https://avatars.discourse-cdn.com/v4/letter/j/e274bd/32.png) [@JerrySTL](https://boards.straightdope.com/u/JerrySTL)\ **Post date:** [April 23, 2015, 4:49pm UTC](https://boards.straightdope.com/t/ms-access-better-practices/718289/7 "2015-04-23T16:49:19Z")
A better place to ask this question is [http://www.utteraccess.com](http://www.utteraccess.com) ---
**Author:** ![FasterThanMeerkats](https://avatars.discourse-cdn.com/v4/letter/f/278dde/32.png) [@FasterThanMeerkats](https://boards.straightdope.com/u/FasterThanMeerkats)\ **Post date:** [April 23, 2015, 7:23pm UTC](https://boards.straightdope.com/t/ms-access-better-practices/718289/8 "2015-04-23T19:23:37Z")
Thank you all for the responses. Yes, my goal was tables like **LSLGuy** discussed. **Dervorin** , I’ll give that function a try. Will also peruse the [utteraccess.com](http://utteraccess.com) site as well. Appreciate it!