Sorting "mmm yyyy" as a date field in Pivot Table View

[CSADataCruncher]

I'm having trouble getting my records to sort correctly in a query used to create a pivot table of expenses.

Our payroll records are pulled by week ending on Saturday and I've created a new column in my query to pull out the date (Using the week ending date) into a new column which is formatted as "mmm yyyy" (Sep 2005). The column is working correctly so that week ending 10/1 is showing up for October... this is what I want.

However, the problem is that when I change the view to a pivot table the columns are organized as "Apr, Aug, Dec, Feb, Jan, etc." I know I've seen a way to force the columns to sort exactly how you want, but now that I need that feature, I can't find it.

If I need to change this back to a date field, I don't have a problem with that as long as the date field would only include the month and year.

Thank you in advance for any helpful suggestions.

Peggy Neubert
CSADataCruncher

 

[dhookom]

Add columns for your week ending date as just the year and week ending date as just the month number.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CSADataCruncher]

Thank you. I did this and it worked. I really wanted to keep the month and year together, but as long as it works, the managers are happy.

Thank you again, for your help.

Peggy

 

[dhookom]

Having your sorting and grouping separated doesn't mean that your report sections or controls need to be separated.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CSADataCruncher]

Right. But this particular group of managers prefer Excel Spreadsheet reports and Excel Pivot Tables rather than preformated Access reports. They like to be able to play with the data and they don't all have access to the Access program. (plus I wouldn't want them "playing" with the live data anyway).

So, is there a way to have the month and year in a column together and sort correctly?

As mentioned... the way you helped me has satisfied their request. Now, when the Pivot Table view is selected (and then exported to Excel), I have the year grouping with a total and the subsequent months underneath. So, actually, this may have been a better solution for them after all.

Peggy

 

[dhookom]

Sorry, I was losing track of the fact that this is a pivot table and not a report.

I have always preferred separating into two fields in pivots since it seems easier to filter the results by year.

You can provide the value to the Pivot Table as
Format([WeekEndingDate],"yyyy mm")
Your managers will need to know that July is 07 ;-)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CSADataCruncher]

Thank you... was that a jab at managers? jk... it's so true.

Thank you again!
Peggy

 

[dhookom]

Who me? Take a jab at managers?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]