Northwind Summary of Sales by Year

[C Tate]

I refer to the Northwind database and the report called Summary of Sales by
Year.

The last footer calculates as follows:

="Totals for " & [Year] & ":"

I am trying to recreate this grouping in my own reports but I cannot get the
[Year] bit to work. My report doesn't seem to understand where to get the
[Year] bit as it keeps asking for it as a parameter. What could I be doing
wrong?

 

[Rob Parker]

The report in the Northwind database uses the ShippedDate field from the
report's record source to group the data, in two group footers - one for
Year, and one for Quarter. The first of these (the outer group) contains the
calculated control you mention. If you open the Sorting and Grouping dialog
for the report, you'll see that the top ShippedDate field has the Group On
property set to Year; this is where the [Year] field in the expression is
obtained from.

You might also notice that in the ShippedData header section of the report,
the year is displayed via the expression:
=DatePart("yyyy",[ShippedDate])
This is an alternative way of displaying the year of the orders. This
expression could have been entered in the Field/Expression to group on, with
the Group On property set to Each Value, to give the same report.

HTH,

Rob

 

[C Tate]

Thanks. I have also grouped on Year in my own report and also used the
DatePart expression you refer to. However, my report doesn't display the
Year in this expression ="Totals for " & [Year] & ":"
.. Indeed Access seems confused as to what this [Year] is! What could I be
doing wrong?

The report in the Northwind database uses the ShippedDate field from the
report's record source to group the data, in two group footers - one for
Year, and one for Quarter. The first of these (the outer group) contains
the calculated control you mention. If you open the Sorting and Grouping
dialog for the report, you'll see that the top ShippedDate field has the
Group On property set to Year; this is where the [Year] field in the
expression is obtained from.

You might also notice that in the ShippedData header section of the
report, the year is displayed via the expression:
=DatePart("yyyy",[ShippedDate])
This is an alternative way of displaying the year of the orders. This
expression could have been entered in the Field/Expression to group on,
with the Group On property set to Each Value, to give the same report.

HTH,

Rob

I refer to the Northwind database and the report called Summary of Sales
by Year.

The last footer calculates as follows:

="Totals for " & [Year] & ":"

I am trying to recreate this grouping in my own reports but I cannot get
the [Year] bit to work. My report doesn't seem to understand where to get
the [Year] bit as it keeps asking for it as a parameter. What could I be
doing wrong?

 

[Rob Parker]

I need a bit more detail to be able to help further. As a start, what is
the SQL for the record source of your report? It presumably contains a date
field, which is what you should enter into the Field/Expression section of
the sort/group dialog; then in the lower section of the sort/group dialog,
you need to set the Group On value to Year (selectable from the drop-down
list). Then all should be fine.

Alternatively, if you have entered a DataPart expression as the
Field/Expression in the sort/group dialog, with the Group On value set to
Each Value, you will need to use the DatePart expression in the textbox in
the group footer; something like:
="Totals for " & DatePart("yyyy",[ShippedDate]) & ":"


Rob

Thanks. I have also grouped on Year in my own report and also used the
DatePart expression you refer to. However, my report doesn't display the
Year in this expression ="Totals for " & [Year] & ":"
. Indeed Access seems confused as to what this [Year] is! What could I be
doing wrong?

The report in the Northwind database uses the ShippedDate field from the
report's record source to group the data, in two group footers - one for
Year, and one for Quarter. The first of these (the outer group) contains
the calculated control you mention. If you open the Sorting and Grouping
dialog for the report, you'll see that the top ShippedDate field has the
Group On property set to Year; this is where the [Year] field in the
expression is obtained from.

You might also notice that in the ShippedData header section of the
report, the year is displayed via the expression:
=DatePart("yyyy",[ShippedDate])
This is an alternative way of displaying the year of the orders. This
expression could have been entered in the Field/Expression to group on,
with the Group On property set to Each Value, to give the same report.

HTH,

Rob

I refer to the Northwind database and the report called Summary of Sales
by Year.

The last footer calculates as follows:

="Totals for " & [Year] & ":"

I am trying to recreate this grouping in my own reports but I cannot get
the [Year] bit to work. My report doesn't seem to understand where to
get the [Year] bit as it keeps asking for it as a parameter. What could
I be doing wrong?

 

[C Tate]

This is the SQL for the record source, a query:

SELECT TableNonClassroomTrainingDelegates.TrainingRequestMadeDate,
TableNonClassroomTrainingDelegates.ActualTrainingDate,
DateDiff("d",[TrainingRequestMadeDate],[ActualTrainingDate]) AS [Number of
Days], TableNonClassroomTrainingDelegates.Trainer
FROM TableNonClassroomTrainingDelegates;


I didn't use the DatePart expression in the sort/group dialog. I just used
the trainingdaterequestmade field.

I need a bit more detail to be able to help further. As a start, what is
the SQL for the record source of your report? It presumably contains a
date field, which is what you should enter into the Field/Expression
section of the sort/group dialog; then in the lower section of the
sort/group dialog, you need to set the Group On value to Year (selectable
from the drop-down list). Then all should be fine.

Alternatively, if you have entered a DataPart expression as the
Field/Expression in the sort/group dialog, with the Group On value set to
Each Value, you will need to use the DatePart expression in the textbox in
the group footer; something like:
="Totals for " & DatePart("yyyy",[ShippedDate]) & ":"


Rob

Thanks. I have also grouped on Year in my own report and also used the
DatePart expression you refer to. However, my report doesn't display the
Year in this expression ="Totals for " & [Year] & ":"
. Indeed Access seems confused as to what this [Year] is! What could I be
doing wrong?

The report in the Northwind database uses the ShippedDate field from the
report's record source to group the data, in two group footers - one for
Year, and one for Quarter. The first of these (the outer group) contains
the calculated control you mention. If you open the Sorting and
Grouping dialog for the report, you'll see that the top ShippedDate
field has the Group On property set to Year; this is where the [Year]
field in the expression is obtained from.

You might also notice that in the ShippedData header section of the
report, the year is displayed via the expression:
=DatePart("yyyy",[ShippedDate])
This is an alternative way of displaying the year of the orders. This
expression could have been entered in the Field/Expression to group on,
with the Group On property set to Each Value, to give the same report.

HTH,

Rob

I refer to the Northwind database and the report called Summary of Sales
by Year.

The last footer calculates as follows:

="Totals for " & [Year] & ":"

I am trying to recreate this grouping in my own reports but I cannot
get the [Year] bit to work. My report doesn't seem to understand where
to get the [Year] bit as it keeps asking for it as a parameter. What
could I be doing wrong?

 

[Rob Parker]

I've just set up a little test database, and find the same thing as you
reported. So I looked a little closer at the Northwind example, and the key
is there:

The [Year] field, used in the group footer, is the textbox control on the
header which contains the DatePart expression. So you can either add a
control named "Year" to the group header, and set its content to the
appropriate DatePart expression, or (easier) change the expression in your
control in the group footer to:
="Totals for " & DatePart("yyyy",[TrainingRequestMadeDate]) & ":"

Sorry for the original confusion; I didn't look closely enough at the
Northwind example, or (more importantly) test it separately.

Rob

This is the SQL for the record source, a query:

SELECT TableNonClassroomTrainingDelegates.TrainingRequestMadeDate,
TableNonClassroomTrainingDelegates.ActualTrainingDate,
DateDiff("d",[TrainingRequestMadeDate],[ActualTrainingDate]) AS [Number of
Days], TableNonClassroomTrainingDelegates.Trainer
FROM TableNonClassroomTrainingDelegates;


I didn't use the DatePart expression in the sort/group dialog. I just used
the trainingdaterequestmade field.

I need a bit more detail to be able to help further. As a start, what is
the SQL for the record source of your report? It presumably contains a
date field, which is what you should enter into the Field/Expression
section of the sort/group dialog; then in the lower section of the
sort/group dialog, you need to set the Group On value to Year (selectable
from the drop-down list). Then all should be fine.

Alternatively, if you have entered a DataPart expression as the
Field/Expression in the sort/group dialog, with the Group On value set to
Each Value, you will need to use the DatePart expression in the textbox
in the group footer; something like:
="Totals for " & DatePart("yyyy",[ShippedDate]) & ":"


Rob

Thanks. I have also grouped on Year in my own report and also used the
DatePart expression you refer to. However, my report doesn't display the
Year in this expression ="Totals for " & [Year] & ":"
. Indeed Access seems confused as to what this [Year] is! What could I
be doing wrong?
message The report in the Northwind database uses the ShippedDate field from
the report's record source to group the data, in two group footers -
one for Year, and one for Quarter. The first of these (the outer group)
contains the calculated control you mention. If you open the Sorting
and Grouping dialog for the report, you'll see that the top ShippedDate
field has the Group On property set to Year; this is where the [Year]
field in the expression is obtained from.

You might also notice that in the ShippedData header section of the
report, the year is displayed via the expression:
=DatePart("yyyy",[ShippedDate])
This is an alternative way of displaying the year of the orders. This
expression could have been entered in the Field/Expression to group on,
with the Group On property set to Each Value, to give the same report.

HTH,

Rob

I refer to the Northwind database and the report called Summary of
Sales by Year.

The last footer calculates as follows:

="Totals for " & [Year] & ":"

I am trying to recreate this grouping in my own reports but I cannot
get the [Year] bit to work. My report doesn't seem to understand where
to get the [Year] bit as it keeps asking for it as a parameter. What
could I be doing wrong?

 

[C Tate]

Thanks. I've now got it to work using your suggestion of
="Totals for " & DatePart("yyyy",[TrainingRequestMadeDate]) & ":"
Brilliant!

I've just set up a little test database, and find the same thing as you
reported. So I looked a little closer at the Northwind example, and the
key is there:

The [Year] field, used in the group footer, is the textbox control on the
header which contains the DatePart expression. So you can either add a
control named "Year" to the group header, and set its content to the
appropriate DatePart expression, or (easier) change the expression in your
control in the group footer to:
="Totals for " & DatePart("yyyy",[TrainingRequestMadeDate]) & ":"

Sorry for the original confusion; I didn't look closely enough at the
Northwind example, or (more importantly) test it separately.

Rob

This is the SQL for the record source, a query:

SELECT TableNonClassroomTrainingDelegates.TrainingRequestMadeDate,
TableNonClassroomTrainingDelegates.ActualTrainingDate,
DateDiff("d",[TrainingRequestMadeDate],[ActualTrainingDate]) AS [Number
of Days], TableNonClassroomTrainingDelegates.Trainer
FROM TableNonClassroomTrainingDelegates;


I didn't use the DatePart expression in the sort/group dialog. I just
used the trainingdaterequestmade field.

I need a bit more detail to be able to help further. As a start, what is
the SQL for the record source of your report? It presumably contains a
date field, which is what you should enter into the Field/Expression
section of the sort/group dialog; then in the lower section of the
sort/group dialog, you need to set the Group On value to Year (selectable
from the drop-down list). Then all should be fine.

Alternatively, if you have entered a DataPart expression as the
Field/Expression in the sort/group dialog, with the Group On value set
to Each Value, you will need to use the DatePart expression in the
textbox in the group footer; something like:
="Totals for " & DatePart("yyyy",[ShippedDate]) & ":"


Rob

Thanks. I have also grouped on Year in my own report and also used the
DatePart expression you refer to. However, my report doesn't display
the Year in this expression ="Totals for " & [Year] & ":"
. Indeed Access seems confused as to what this [Year] is! What could I
be doing wrong?
message The report in the Northwind database uses the ShippedDate field from
the report's record source to group the data, in two group footers -
one for Year, and one for Quarter. The first of these (the outer
group) contains the calculated control you mention. If you open the
Sorting and Grouping dialog for the report, you'll see that the top
ShippedDate field has the Group On property set to Year; this is where
the [Year] field in the expression is obtained from.

You might also notice that in the ShippedData header section of the
report, the year is displayed via the expression:
=DatePart("yyyy",[ShippedDate])
This is an alternative way of displaying the year of the orders. This
expression could have been entered in the Field/Expression to group
on, with the Group On property set to Each Value, to give the same
report.

HTH,

Rob

I refer to the Northwind database and the report called Summary of
Sales by Year.

The last footer calculates as follows:

="Totals for " & [Year] & ":"

I am trying to recreate this grouping in my own reports but I cannot
get the [Year] bit to work. My report doesn't seem to understand
where to get the [Year] bit as it keeps asking for it as a parameter.
What could I be doing wrong?