Need help with sorting with months of year

[gacapp]

I hope someone can help. I have what I thought was a simple sort, but I can't figure it out. I am trying to do a sort on a report for months of the year. The report has monthly totals on it (For example - it shows the total number of patients seen in May, as May 2015, with the number associated with it, as well as June, etc). So on the report is looks like this:

Provider1 Visit by Month Patients Seen
Sam May 2015 15
April 2015 20
January 2015 14

I need to know how I can have the Visit By Month sorted so they are January, February, March, etc.
If I do a sort on it, it does it alphabetically which means it would list as January, July, June, etc, which is wrong.

So can someone help me with this?



Thank you very much.

[ranman256]

you need 2 fields,
[month] (to show) as "May 2015"
and SortMonth as "20150509"
format([month],"yyyymmdd")

sort on SortMonth

[gacapp]

Ranman256 - thank you for the response. I'm sort of new to Access so forgive me. Do you mean add those fields to the query, which the report gets its info from, or add it in the report? If it's added to the report - how do I do that? Again sorry for the questions but i am trying to learn. Thank you

[JamesDeckert]

gacapp,
How is the date field defined in the table? Is it a Date/Time field? Or a text field?
if it is a Date/Time, then sorting is very straight forward. If you're defining it as a text field, your sorting will be more challenging.

[ranman256]

add this to the query.
if its in a report, sort on the query field.

[gacapp]

Let me try and spell this out better because I am getting confused.

I have a table, we will call it Table1. In that table in a field called DateofVisit, which is a date/time field. There is also a Provider field that is text. A field called PatientsSeen that is a number.
I enter the provider name, number of patients seen and date of the patient visit.

I have a query called MonthQuery - which totals the visits for each provider, for each month.
- There is a field I pulled from the table call DateofVisit and that field is grouped on. And here is have to enter the month I want totals for.
- Another field i pulled in is Provider, which is also grouped on.
- Another field called PatientsSeen which is I am getting the Sum for.
- Another field that Access created for the sum which looks like this: Year([Table1].[DateOfVisit])*12+DatePart('m',[Table1].[DateOfVisit])-1

I have a 2nd query called YearQuery - which totals the patients visits for the year and these are listed by month so you have January total + February total, etc.
- There is a field I pulled from the table call DateofVisit and that field is grouped on.
- Another field i pulled in is Provider, which is also grouped on.
- Another field called PatientsSeen which is I am getting the Sum for.
- Another field that Access created for the sum which looks like this: Year([Table1].[DateOfVisit])*12+DatePart('m',[Table1].[DateOfVisit])-1

I have a report called MonthlyReport that I am pulling info from MonthQuery
In this report i have to choose the month, then is lists the providers and the monthly totals of patients seen. And it lists the month like so: January 2015, February 2015, etc.

I have a report called YearlyReport that I am pulling info from YearlyQuery.
In this report it groups by provider and lists the totals for each month (January 2015, February 2015, etc) and totals all the months for the year.

The problem is that the listing on the YearlyReport does not list the monthly totals (January 2015, February 2015, etc) in the correct order from January down to December.

It is a simple thing but i can't figure out how to do it.

I appreciate the advice a few people have sent, but I don't know where to put that code and how. Does it go in the table, query or report?

So if someone can spell it out for me, and show me how, I would really appreciate it.

Thank you

[June7]

It won't list them in chronological order because the grouping is based on a text value (April comes before January alphabetically).

Calculate a field in query (or expression in group setup of report design) to base grouping on:

Format([DateOfVisit], "yyyymm") AS YrMoGroup

[gacapp]

when I take your expression and put in into a new column in the query I get an "error in syntax". So then if I open the expression builder and try to retype it, I get this:

Format( «Expr» [Date_Of_Visit] «Expr» , "yyyymm") as YrMoGroup, which still gives me a syntax error.

I'm sorry but it is not working. I do understand why my original way won't order the way I want.

Sorry but if you can help....

[June7]

Your field name has underscores?

In the query design grid Field cell, type:

YrMoGroup: Format([DateOfVisit], "yyyymm")

What I originally posted is what the SQL statement will show. Switch to SQL View to see constructed statement.

[gacapp]

Ok I got it to list in my report as 201501, then 201502 ascending. So my last question is - how would i get that show as January 2015, February 2015?? I assume i would use the expression builder. I'm trying but i can't get it. Again thank you sooooo much!!

[June7]

Calculate two fields for these different formats. One for the grouping criteria, one for the display in textbox on report.

[gacapp]

I'm sorry but I am confused.

On my query I made a field that has this expression:
DateOfVisit By Month: Format$([Patient_Data].[DateOfVisit],'yyyymm') which shows the dates as 201501, 201502, etc.
There is also the original expression that looks like this: DateOfVisit By Month: Format$([Patient_Data].[DateOfVisit],'mmmm yyyy') that showed the date as January 2015. This is not shown because if I check the box to show it, I get a "duplicate output alias" error.

On my report I have a text box that the Control Source is: DateOfVisit By Month, which again displays like 201502.

So do I add another text box on the report?? And what would be in it?? And the Control Source would be??

Again I apologize but I am trying to learn as I go and I keep getting stuck!!

[June7]

Calculate two fields.

DateOfVisit By YrMo: Format([Patient_Data].[DateOfVisit], 'yyyymm')

DateOfVisit By MoYr: Format([Patient_Data].[DateOfVisit], 'mmmm yyyy')

Use the first for report grouping (does not need to be in a textbox or textbox can be set not visible). Use the second for display.

[gacapp]

Ok got it!! Thank you soooo much. Great forum. Nice to know that people respond to us newbies. Have a nice day!!