Summary Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ok, so I created this summary report by month but when I run the report it is
being sorted by month in ascending form. How do I get this to sort by
calander month?
 
You'll need to add a field to the query (from which the report is being
created) that gives the report the month number; then, in the Sorting &
Grouping box (View | Sorting & Grouping), select that calculated field as
the first field on which to do the sorting.
 
Post the SQL statement of the query that is the report's Recordsource. (Open
the query in design view, click on View icon on toolbar and select SQL, and
copy the entire statement that you see there; paste it into a reply message
in this thread.)
 
Here it is:

SELECT DISTINCTROW [2005].[RMA#], Format$([2005].[Date Notified],'mmmm
yyyy') AS [Date Notified By Month], Sum([2005].[Raw Mat'l Cost]) AS [Sum Of
Raw Mat'l Cost], Sum([2005].[Labor Cost]) AS [Sum Of Labor Cost],
Sum([2005].[In / Out Frt Cost]) AS [Sum Of In / Out Frt Cost],
Sum([2005].[Misc Cost]) AS [Sum Of Misc Cost], Count(*) AS [Count Of 2005]
FROM 2005
GROUP BY [2005].[RMA#], Format$([2005].[Date Notified],'mmmm yyyy'),
Year([2005].[Date Notified])*12+DatePart('m',[2005].[Date Notified])-1;
 
OK - I've added the calculated field (MonthNum) to the query, with the
assumption that you want it to get the month of the "Date Notified" field
(modify as needed if you're using a different date field):

SELECT DISTINCTROW [2005].[RMA#], Format$([2005].[Date Notified],'mmmm
yyyy') AS [Date Notified By Month], Sum([2005].[Raw Mat'l Cost]) AS [Sum Of
Raw Mat'l Cost], Sum([2005].[Labor Cost]) AS [Sum Of Labor Cost],
Sum([2005].[In / Out Frt Cost]) AS [Sum Of In / Out Frt Cost],
Sum([2005].[Misc Cost]) AS [Sum Of Misc Cost], Count(*) AS [Count Of 2005],
Month([Date Notified]) AS MonthNum
FROM 2005
GROUP BY [2005].[RMA#], Format$([2005].[Date Notified],'mmmm yyyy'),
Year([2005].[Date Notified])*12+DatePart('m',[2005].[Date Notified])-1,
Month([Date Notified]);

--

Ken Snell
<MS ACCESS MVP>



Secret Squirrel said:
Here it is:

SELECT DISTINCTROW [2005].[RMA#], Format$([2005].[Date Notified],'mmmm
yyyy') AS [Date Notified By Month], Sum([2005].[Raw Mat'l Cost]) AS [Sum
Of
Raw Mat'l Cost], Sum([2005].[Labor Cost]) AS [Sum Of Labor Cost],
Sum([2005].[In / Out Frt Cost]) AS [Sum Of In / Out Frt Cost],
Sum([2005].[Misc Cost]) AS [Sum Of Misc Cost], Count(*) AS [Count Of 2005]
FROM 2005
GROUP BY [2005].[RMA#], Format$([2005].[Date Notified],'mmmm yyyy'),
Year([2005].[Date Notified])*12+DatePart('m',[2005].[Date Notified])-1;


Ken Snell said:
Post the SQL statement of the query that is the report's Recordsource.
(Open
the query in design view, click on View icon on toolbar and select SQL,
and
copy the entire statement that you see there; paste it into a reply
message
in this thread.)
Click to expand...
Click to expand...
 
That didn't seem to work unless I did something wrong. I added your code to
the SQL but when I run the report it is still not in order by month. If you
run that query in datasheet view you will see some of the months are out of
order. Is there something that we are missing?

Ken Snell said:
OK - I've added the calculated field (MonthNum) to the query, with the
assumption that you want it to get the month of the "Date Notified" field
(modify as needed if you're using a different date field):

SELECT DISTINCTROW [2005].[RMA#], Format$([2005].[Date Notified],'mmmm
yyyy') AS [Date Notified By Month], Sum([2005].[Raw Mat'l Cost]) AS [Sum Of
Raw Mat'l Cost], Sum([2005].[Labor Cost]) AS [Sum Of Labor Cost],
Sum([2005].[In / Out Frt Cost]) AS [Sum Of In / Out Frt Cost],
Sum([2005].[Misc Cost]) AS [Sum Of Misc Cost], Count(*) AS [Count Of 2005],
Month([Date Notified]) AS MonthNum
FROM 2005
GROUP BY [2005].[RMA#], Format$([2005].[Date Notified],'mmmm yyyy'),
Year([2005].[Date Notified])*12+DatePart('m',[2005].[Date Notified])-1,
Month([Date Notified]);

--

Ken Snell
<MS ACCESS MVP>



Secret Squirrel said:
Here it is:

SELECT DISTINCTROW [2005].[RMA#], Format$([2005].[Date Notified],'mmmm
yyyy') AS [Date Notified By Month], Sum([2005].[Raw Mat'l Cost]) AS [Sum
Of
Raw Mat'l Cost], Sum([2005].[Labor Cost]) AS [Sum Of Labor Cost],
Sum([2005].[In / Out Frt Cost]) AS [Sum Of In / Out Frt Cost],
Sum([2005].[Misc Cost]) AS [Sum Of Misc Cost], Count(*) AS [Count Of 2005]
FROM 2005
GROUP BY [2005].[RMA#], Format$([2005].[Date Notified],'mmmm yyyy'),
Year([2005].[Date Notified])*12+DatePart('m',[2005].[Date Notified])-1;


Ken Snell said:
Post the SQL statement of the query that is the report's Recordsource.
(Open
the query in design view, click on View icon on toolbar and select SQL,
and
copy the entire statement that you see there; paste it into a reply
message
in this thread.)

--

Ken Snell
<MS ACCESS MVP>

message Can you show me how to write this all out? I'm a little unclear how to
do
it.

:

You'll need to add a field to the query (from which the report is
being
created) that gives the report the month number; then, in the Sorting
&
Grouping box (View | Sorting & Grouping), select that calculated field
as
the first field on which to do the sorting.

--

Ken Snell
<MS ACCESS MVP>


message Ok, so I created this summary report by month but when I run the
report
it
is
being sorted by month in ascending form. How do I get this to sort
by
calander month?
Click to expand...
Click to expand...
Click to expand...
 
Depends... I stated that I assumed that Date Notified was the correct field
that you wanted to sort on ... if that is the wrong one, change the query to
use the correct field. Also, did you set the MonthNum as the first field in
the Sorting & Grouping window as I'd indicated in my earlier reply?
--

Ken Snell
<MS ACCESS MVP>



Secret Squirrel said:
That didn't seem to work unless I did something wrong. I added your code
to
the SQL but when I run the report it is still not in order by month. If
you
run that query in datasheet view you will see some of the months are out
of
order. Is there something that we are missing?

Ken Snell said:
OK - I've added the calculated field (MonthNum) to the query, with the
assumption that you want it to get the month of the "Date Notified" field
(modify as needed if you're using a different date field):

SELECT DISTINCTROW [2005].[RMA#], Format$([2005].[Date Notified],'mmmm
yyyy') AS [Date Notified By Month], Sum([2005].[Raw Mat'l Cost]) AS [Sum
Of
Raw Mat'l Cost], Sum([2005].[Labor Cost]) AS [Sum Of Labor Cost],
Sum([2005].[In / Out Frt Cost]) AS [Sum Of In / Out Frt Cost],
Sum([2005].[Misc Cost]) AS [Sum Of Misc Cost], Count(*) AS [Count Of
2005],
Month([Date Notified]) AS MonthNum
FROM 2005
GROUP BY [2005].[RMA#], Format$([2005].[Date Notified],'mmmm yyyy'),
Year([2005].[Date Notified])*12+DatePart('m',[2005].[Date Notified])-1,
Month([Date Notified]);

--

Ken Snell
<MS ACCESS MVP>



Secret Squirrel said:
Here it is:

SELECT DISTINCTROW [2005].[RMA#], Format$([2005].[Date Notified],'mmmm
yyyy') AS [Date Notified By Month], Sum([2005].[Raw Mat'l Cost]) AS
[Sum
Of
Raw Mat'l Cost], Sum([2005].[Labor Cost]) AS [Sum Of Labor Cost],
Sum([2005].[In / Out Frt Cost]) AS [Sum Of In / Out Frt Cost],
Sum([2005].[Misc Cost]) AS [Sum Of Misc Cost], Count(*) AS [Count Of
2005]
FROM 2005
GROUP BY [2005].[RMA#], Format$([2005].[Date Notified],'mmmm yyyy'),
Year([2005].[Date Notified])*12+DatePart('m',[2005].[Date Notified])-1;


:

Post the SQL statement of the query that is the report's Recordsource.
(Open
the query in design view, click on View icon on toolbar and select
SQL,
and
copy the entire statement that you see there; paste it into a reply
message
in this thread.)

--

Ken Snell
<MS ACCESS MVP>

message Can you show me how to write this all out? I'm a little unclear how
to
do
it.

:

You'll need to add a field to the query (from which the report is
being
created) that gives the report the month number; then, in the
Sorting
&
Grouping box (View | Sorting & Grouping), select that calculated
field
as
the first field on which to do the sorting.

--

Ken Snell
<MS ACCESS MVP>


in
message Ok, so I created this summary report by month but when I run the
report
it
is
being sorted by month in ascending form. How do I get this to
sort
by
calander month?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top