Incorrect subtotals in a report

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

Guest

I am trying to create a report based on a flat file to show the cumulative
sales by day of the week. The problem I am now having is that I cannot get
some of my subtotals to calculate correctly.

The table I am using contains the total daily sales quantities for each
product, together with the corresponding product details. I have then
created a simple query on this to produce the following fields. These are
listed with samples of the data contained:

Weekday (to identify the day number) eg. 1, 2, 3
Day, eg. Sun, Mon, Tue
WeekNo, eg. 31, 32, 33
DepartmentCode, eg, 94, 95, 96
DepartmentDesc, eg. Homewares, Ambient, Grocery
SectionCode, eg. 41, 42, 43
SectionDesc, eg. Cookware, Tinned, Fresh produce
SubsectionCode, eg. 02, 03, 04
SubsectionDesc, eg. Pans, Baked beans, Tomatoes
BuyerName, eg. James Richardson, Tim Reynolds, Bob Taylor
ProductCode, eg. 415745, 254471, 958764
ProductDesc, eg, Stainless steel 24cm, Heinz 450g, Spanish pack of 6
SalesQuantity, eg, 4, 258, 65
SalesValue, eg. 30.66, 64.50, 45.50

Next, I based a report on this query and grouped it in order of WeekNo,
BuyerName, DepartmentCode, SectionCode, SubsectionCode and ProductCode.

In the ProductCode footer, I created a text box to show the total sales for
day 1 (Sunday) through to Saturday by using variants of the following formula:

=IIf([Weekday]=1,[SalesQuantity],0)

To the right of these, I created two boxes with the following criteria to
show the total sales values for the week:

=Sum([SalesQuantity])
=Sum([SalesValue])

These 9 fields were then copied into two footers I had created for the
SubsectionCode and SectionCode. I am now left with the weekly totals that
appear to be correct, whilst the daily totals are incorrect.

I think the problem here lies with the fact that I need to somehow summarise
the data by week, but also retain the ‘day’ information in order to produce
the daily totals. Unfortunately, my experience with Access is insufficient
to know whether this is in any way possible.

Any assistance offered will be much appreciated.

Thanks in advance.
 
If I understand what you need, you can try something like:
=Sum(Abs(...Your Expression...) * [ValueToSum])
or maybe
=Sum(Abs([Weekday]=1) * [SalesQuantity])
 
Duane,

Thanks for your reply, however, that doesn't seem to solve my problem.
Rather, what I'm looking to do is create something like a crosstab query
(although the standard method of this doesn't seem to allow sufficient row
headings!). Basically, the report needs to look something like this:

Week Number x,
Buyer: Joe Bloggs,
Department 01,
Section 01,
Subsection 01,

Product A, Total quantity of sales for Sunday (Weekday 1),...and so on
to...Total quantity of sales for Saturday (Weekday 7), Week total quantity of
sales, Week total value of sales.

Product B, Total quantity of sales for Sunday, etc., until,...

Subsection 01 subtotals for all of the above quantities.

Then Products and subtotals for Subsection 02 etc., until...

Section 01 subtotals for all of the above quantities.

Then Products and subtotals for all Subsections under Section 02, etc.,
until...

Department 02 (no Department subtotals are necessary), and so on and so forth.

The difficulty I'm finding is trying to summarise the total sales for the
week, whilst still being able to list the daily sales by product, within the
same report.

Thanks again,

Jedster


Duane Hookom said:
If I understand what you need, you can try something like:
=Sum(Abs(...Your Expression...) * [ValueToSum])
or maybe
=Sum(Abs([Weekday]=1) * [SalesQuantity])

--
Duane Hookom
MS Access MVP


Jedster said:
I am trying to create a report based on a flat file to show the cumulative
sales by day of the week. The problem I am now having is that I cannot get
some of my subtotals to calculate correctly.

The table I am using contains the total daily sales quantities for each
product, together with the corresponding product details. I have then
created a simple query on this to produce the following fields. These are
listed with samples of the data contained:

Weekday (to identify the day number) eg. 1, 2, 3
Day, eg. Sun, Mon, Tue
WeekNo, eg. 31, 32, 33
DepartmentCode, eg, 94, 95, 96
DepartmentDesc, eg. Homewares, Ambient, Grocery
SectionCode, eg. 41, 42, 43
SectionDesc, eg. Cookware, Tinned, Fresh produce
SubsectionCode, eg. 02, 03, 04
SubsectionDesc, eg. Pans, Baked beans, Tomatoes
BuyerName, eg. James Richardson, Tim Reynolds, Bob Taylor
ProductCode, eg. 415745, 254471, 958764
ProductDesc, eg, Stainless steel 24cm, Heinz 450g, Spanish pack of 6
SalesQuantity, eg, 4, 258, 65
SalesValue, eg. 30.66, 64.50, 45.50

Next, I based a report on this query and grouped it in order of WeekNo,
BuyerName, DepartmentCode, SectionCode, SubsectionCode and ProductCode.

In the ProductCode footer, I created a text box to show the total sales for
day 1 (Sunday) through to Saturday by using variants of the following formula:

=IIf([Weekday]=1,[SalesQuantity],0)

To the right of these, I created two boxes with the following criteria to
show the total sales values for the week:

=Sum([SalesQuantity])
=Sum([SalesValue])

These 9 fields were then copied into two footers I had created for the
SubsectionCode and SectionCode. I am now left with the weekly totals that
appear to be correct, whilst the daily totals are incorrect.

I think the problem here lies with the fact that I need to somehow summarise
the data by week, but also retain the 'day' information in order to produce
the daily totals. Unfortunately, my experience with Access is insufficient
to know whether this is in any way possible.

Any assistance offered will be much appreciated.

Thanks in advance.
Click to expand...
Click to expand...
 
There is really no limit to the number of row headings in a crosstab. There
is a limit of 255 columns in any table or query. If you want multiple values
for columns then you can use a solution as this solution from an earlier
posting (below). There are samples of crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4

You can create multiple values by combining a crosstab and cartesian query.

Create a sample using the Northwind database to create both a Quantity and
Quantity * Price value for each column.
-Create a new table tblXtabColumns with a single field [FldName]
-add two records to this table
"Quantity"
"QtyXPrice"
-create a crosstab with the table [Products], [Orders], [Order Details], and
[tblXtabColumns]
-join the first three tables as expected but don't join tblXtabColumns to
any other table
-your Column Heading expression is
Expr1:[FldName] & Month([OrderDate])
-your Value expression is
DaVal:IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])
Sum
-I set OrderDate for the first three months of 1998
The full SQL is:

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Orders INNER JOIN (Products INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID =
[Order Details].OrderID
WHERE (((Orders.OrderDate) Between #1/1/1998# And #3/31/1998#))
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);

You will get two columns per month QtyXPriceN and QuantityN.

--
Duane Hookom
MS Access MVP
--

Jedster said:
Duane,

Thanks for your reply, however, that doesn't seem to solve my problem.
Rather, what I'm looking to do is create something like a crosstab query
(although the standard method of this doesn't seem to allow sufficient row
headings!). Basically, the report needs to look something like this:

Week Number x,
Buyer: Joe Bloggs,
Department 01,
Section 01,
Subsection 01,

Product A, Total quantity of sales for Sunday (Weekday 1),...and so on
to...Total quantity of sales for Saturday (Weekday 7), Week total quantity of
sales, Week total value of sales.

Product B, Total quantity of sales for Sunday, etc., until,...

Subsection 01 subtotals for all of the above quantities.

Then Products and subtotals for Subsection 02 etc., until...

Section 01 subtotals for all of the above quantities.

Then Products and subtotals for all Subsections under Section 02, etc.,
until...

Department 02 (no Department subtotals are necessary), and so on and so forth.

The difficulty I'm finding is trying to summarise the total sales for the
week, whilst still being able to list the daily sales by product, within the
same report.

Thanks again,

Jedster


Duane Hookom said:
If I understand what you need, you can try something like:
=Sum(Abs(...Your Expression...) * [ValueToSum])
or maybe
=Sum(Abs([Weekday]=1) * [SalesQuantity])

--
Duane Hookom
MS Access MVP


Jedster said:
I am trying to create a report based on a flat file to show the cumulative
sales by day of the week. The problem I am now having is that I
Click to expand...
cannot
get
some of my subtotals to calculate correctly.

The table I am using contains the total daily sales quantities for each
product, together with the corresponding product details. I have then
created a simple query on this to produce the following fields. These are
listed with samples of the data contained:

Weekday (to identify the day number) eg. 1, 2, 3
Day, eg. Sun, Mon, Tue
WeekNo, eg. 31, 32, 33
DepartmentCode, eg, 94, 95, 96
DepartmentDesc, eg. Homewares, Ambient, Grocery
SectionCode, eg. 41, 42, 43
SectionDesc, eg. Cookware, Tinned, Fresh produce
SubsectionCode, eg. 02, 03, 04
SubsectionDesc, eg. Pans, Baked beans, Tomatoes
BuyerName, eg. James Richardson, Tim Reynolds, Bob Taylor
ProductCode, eg. 415745, 254471, 958764
ProductDesc, eg, Stainless steel 24cm, Heinz 450g, Spanish pack of 6
SalesQuantity, eg, 4, 258, 65
SalesValue, eg. 30.66, 64.50, 45.50

Next, I based a report on this query and grouped it in order of WeekNo,
BuyerName, DepartmentCode, SectionCode, SubsectionCode and ProductCode.

In the ProductCode footer, I created a text box to show the total
Click to expand...
sales
for
day 1 (Sunday) through to Saturday by using variants of the following formula:

=IIf([Weekday]=1,[SalesQuantity],0)

To the right of these, I created two boxes with the following criteria to
show the total sales values for the week:

=Sum([SalesQuantity])
=Sum([SalesValue])

These 9 fields were then copied into two footers I had created for the
SubsectionCode and SectionCode. I am now left with the weekly totals that
appear to be correct, whilst the daily totals are incorrect.

I think the problem here lies with the fact that I need to somehow summarise
the data by week, but also retain the 'day' information in order to produce
the daily totals. Unfortunately, my experience with Access is insufficient
to know whether this is in any way possible.

Any assistance offered will be much appreciated.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Duane,

Thanks again for your response. I have had a look at your example however,
unfortunately, it doesn't appear to solve my problem. The total values for
each individual product within my report are fine, it's just the subtotals
(SubsectCode and SectionCode) that are not totalling correctly for some
reason.

The website you kindly mentioned probably contains some useful samples, but
unfortunately, I haven't been able to access it due to a proxy server error.

If there's any further information that you'd like me to add about this,
then please let me know.

Thanks again.

Regards,

Jedster


Duane Hookom said:
There is really no limit to the number of row headings in a crosstab. There
is a limit of 255 columns in any table or query. If you want multiple values
for columns then you can use a solution as this solution from an earlier
posting (below). There are samples of crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4

You can create multiple values by combining a crosstab and cartesian query.

Create a sample using the Northwind database to create both a Quantity and
Quantity * Price value for each column.
-Create a new table tblXtabColumns with a single field [FldName]
-add two records to this table
"Quantity"
"QtyXPrice"
-create a crosstab with the table [Products], [Orders], [Order Details], and
[tblXtabColumns]
-join the first three tables as expected but don't join tblXtabColumns to
any other table
-your Column Heading expression is
Expr1:[FldName] & Month([OrderDate])
-your Value expression is
DaVal:IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])
Sum
-I set OrderDate for the first three months of 1998
The full SQL is:

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Orders INNER JOIN (Products INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID =
[Order Details].OrderID
WHERE (((Orders.OrderDate) Between #1/1/1998# And #3/31/1998#))
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);

You will get two columns per month QtyXPriceN and QuantityN.

--
Duane Hookom
MS Access MVP
--

Jedster said:
Duane,

Thanks for your reply, however, that doesn't seem to solve my problem.
Rather, what I'm looking to do is create something like a crosstab query
(although the standard method of this doesn't seem to allow sufficient row
headings!). Basically, the report needs to look something like this:

Week Number x,
Buyer: Joe Bloggs,
Department 01,
Section 01,
Subsection 01,

Product A, Total quantity of sales for Sunday (Weekday 1),...and so on
to...Total quantity of sales for Saturday (Weekday 7), Week total quantity of
sales, Week total value of sales.

Product B, Total quantity of sales for Sunday, etc., until,...

Subsection 01 subtotals for all of the above quantities.

Then Products and subtotals for Subsection 02 etc., until...

Section 01 subtotals for all of the above quantities.

Then Products and subtotals for all Subsections under Section 02, etc.,
until...

Department 02 (no Department subtotals are necessary), and so on and so forth.

The difficulty I'm finding is trying to summarise the total sales for the
week, whilst still being able to list the daily sales by product, within the
same report.

Thanks again,

Jedster


Duane Hookom said:
If I understand what you need, you can try something like:
=Sum(Abs(...Your Expression...) * [ValueToSum])
or maybe
=Sum(Abs([Weekday]=1) * [SalesQuantity])

--
Duane Hookom
MS Access MVP


I am trying to create a report based on a flat file to show the cumulative
sales by day of the week. The problem I am now having is that I cannot
get
some of my subtotals to calculate correctly.

The table I am using contains the total daily sales quantities for each
product, together with the corresponding product details. I have then
created a simple query on this to produce the following fields. These are
listed with samples of the data contained:

Weekday (to identify the day number) eg. 1, 2, 3
Day, eg. Sun, Mon, Tue
WeekNo, eg. 31, 32, 33
DepartmentCode, eg, 94, 95, 96
DepartmentDesc, eg. Homewares, Ambient, Grocery
SectionCode, eg. 41, 42, 43
SectionDesc, eg. Cookware, Tinned, Fresh produce
SubsectionCode, eg. 02, 03, 04
SubsectionDesc, eg. Pans, Baked beans, Tomatoes
BuyerName, eg. James Richardson, Tim Reynolds, Bob Taylor
ProductCode, eg. 415745, 254471, 958764
ProductDesc, eg, Stainless steel 24cm, Heinz 450g, Spanish pack of 6
SalesQuantity, eg, 4, 258, 65
SalesValue, eg. 30.66, 64.50, 45.50

Next, I based a report on this query and grouped it in order of WeekNo,
BuyerName, DepartmentCode, SectionCode, SubsectionCode and ProductCode.

In the ProductCode footer, I created a text box to show the total sales
for
day 1 (Sunday) through to Saturday by using variants of the following
formula:

=IIf([Weekday]=1,[SalesQuantity],0)

To the right of these, I created two boxes with the following criteria to
show the total sales values for the week:

=Sum([SalesQuantity])
=Sum([SalesValue])

These 9 fields were then copied into two footers I had created for the
SubsectionCode and SectionCode. I am now left with the weekly totals that
appear to be correct, whilst the daily totals are incorrect.

I think the problem here lies with the fact that I need to somehow
summarise
the data by week, but also retain the 'day' information in order to
produce
the daily totals. Unfortunately, my experience with Access is
insufficient
to know whether this is in any way possible.

Any assistance offered will be much appreciated.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
I'm having great difficulty identifying your report. However, if you have
=IIf([Weekday]=1,[SalesQuantity],0)
and want to total this in a group footer, it should be:
=Sum(Abs([Weekday]=1) * [SalesQuantity])

--
Duane Hookom
MS Access MVP


Jedster said:
Duane,

Thanks again for your response. I have had a look at your example however,
unfortunately, it doesn't appear to solve my problem. The total values for
each individual product within my report are fine, it's just the subtotals
(SubsectCode and SectionCode) that are not totalling correctly for some
reason.

The website you kindly mentioned probably contains some useful samples, but
unfortunately, I haven't been able to access it due to a proxy server error.

If there's any further information that you'd like me to add about this,
then please let me know.

Thanks again.

Regards,

Jedster


Duane Hookom said:
There is really no limit to the number of row headings in a crosstab. There
is a limit of 255 columns in any table or query. If you want multiple values
for columns then you can use a solution as this solution from an earlier
posting (below). There are samples of crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4

You can create multiple values by combining a crosstab and cartesian query.

Create a sample using the Northwind database to create both a Quantity and
Quantity * Price value for each column.
-Create a new table tblXtabColumns with a single field [FldName]
-add two records to this table
"Quantity"
"QtyXPrice"
-create a crosstab with the table [Products], [Orders], [Order Details], and
[tblXtabColumns]
-join the first three tables as expected but don't join tblXtabColumns to
any other table
-your Column Heading expression is
Expr1:[FldName] & Month([OrderDate])
-your Value expression is
DaVal:IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])
Sum
-I set OrderDate for the first three months of 1998
The full SQL is:

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Orders INNER JOIN (Products INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID =
[Order Details].OrderID
WHERE (((Orders.OrderDate) Between #1/1/1998# And #3/31/1998#))
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);

You will get two columns per month QtyXPriceN and QuantityN.

--
Duane Hookom
MS Access MVP
--

Jedster said:
Duane,

Thanks for your reply, however, that doesn't seem to solve my problem.
Rather, what I'm looking to do is create something like a crosstab query
(although the standard method of this doesn't seem to allow sufficient row
headings!). Basically, the report needs to look something like this:

Week Number x,
Buyer: Joe Bloggs,
Department 01,
Section 01,
Subsection 01,

Product A, Total quantity of sales for Sunday (Weekday 1),...and so on
to...Total quantity of sales for Saturday (Weekday 7), Week total
Click to expand...
quantity
of
sales, Week total value of sales.

Product B, Total quantity of sales for Sunday, etc., until,...

Subsection 01 subtotals for all of the above quantities.

Then Products and subtotals for Subsection 02 etc., until...

Section 01 subtotals for all of the above quantities.

Then Products and subtotals for all Subsections under Section 02, etc.,
until...

Department 02 (no Department subtotals are necessary), and so on and
Click to expand...
so
forth.
The difficulty I'm finding is trying to summarise the total sales for the
week, whilst still being able to list the daily sales by product,
Click to expand...
within
the
same report.

Thanks again,

Jedster


:

If I understand what you need, you can try something like:
=Sum(Abs(...Your Expression...) * [ValueToSum])
or maybe
=Sum(Abs([Weekday]=1) * [SalesQuantity])

--
Duane Hookom
MS Access MVP


I am trying to create a report based on a flat file to show the cumulative
sales by day of the week. The problem I am now having is that I cannot
get
some of my subtotals to calculate correctly.

The table I am using contains the total daily sales quantities for each
product, together with the corresponding product details. I have then
created a simple query on this to produce the following fields.
Click to expand...
These
are
listed with samples of the data contained:

Weekday (to identify the day number) eg. 1, 2, 3
Day, eg. Sun, Mon, Tue
WeekNo, eg. 31, 32, 33
DepartmentCode, eg, 94, 95, 96
DepartmentDesc, eg. Homewares, Ambient, Grocery
SectionCode, eg. 41, 42, 43
SectionDesc, eg. Cookware, Tinned, Fresh produce
SubsectionCode, eg. 02, 03, 04
SubsectionDesc, eg. Pans, Baked beans, Tomatoes
BuyerName, eg. James Richardson, Tim Reynolds, Bob Taylor
ProductCode, eg. 415745, 254471, 958764
ProductDesc, eg, Stainless steel 24cm, Heinz 450g, Spanish pack of 6
SalesQuantity, eg, 4, 258, 65
SalesValue, eg. 30.66, 64.50, 45.50

Next, I based a report on this query and grouped it in order of WeekNo,
BuyerName, DepartmentCode, SectionCode, SubsectionCode and ProductCode.

In the ProductCode footer, I created a text box to show the total sales
for
day 1 (Sunday) through to Saturday by using variants of the following
formula:

=IIf([Weekday]=1,[SalesQuantity],0)

To the right of these, I created two boxes with the following
Click to expand...
criteria
to
show the total sales values for the week:

=Sum([SalesQuantity])
=Sum([SalesValue])

These 9 fields were then copied into two footers I had created for the
SubsectionCode and SectionCode. I am now left with the weekly
Click to expand...
totals
that
appear to be correct, whilst the daily totals are incorrect.

I think the problem here lies with the fact that I need to somehow
summarise
the data by week, but also retain the 'day' information in order to
produce
the daily totals. Unfortunately, my experience with Access is
insufficient
to know whether this is in any way possible.

Any assistance offered will be much appreciated.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Duane,

Thanks, for your help. I think I found the solution for this by using a
crosstab query and redesigning the report.

Regards,

Jared


Duane Hookom said:
I'm having great difficulty identifying your report. However, if you have
=IIf([Weekday]=1,[SalesQuantity],0)
and want to total this in a group footer, it should be:
=Sum(Abs([Weekday]=1) * [SalesQuantity])

--
Duane Hookom
MS Access MVP


Jedster said:
Duane,

Thanks again for your response. I have had a look at your example however,
unfortunately, it doesn't appear to solve my problem. The total values for
each individual product within my report are fine, it's just the subtotals
(SubsectCode and SectionCode) that are not totalling correctly for some
reason.

The website you kindly mentioned probably contains some useful samples, but
unfortunately, I haven't been able to access it due to a proxy server error.

If there's any further information that you'd like me to add about this,
then please let me know.

Thanks again.

Regards,

Jedster


Duane Hookom said:
There is really no limit to the number of row headings in a crosstab. There
is a limit of 255 columns in any table or query. If you want multiple values
for columns then you can use a solution as this solution from an earlier
posting (below). There are samples of crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4

You can create multiple values by combining a crosstab and cartesian query.

Create a sample using the Northwind database to create both a Quantity and
Quantity * Price value for each column.
-Create a new table tblXtabColumns with a single field [FldName]
-add two records to this table
"Quantity"
"QtyXPrice"
-create a crosstab with the table [Products], [Orders], [Order Details], and
[tblXtabColumns]
-join the first three tables as expected but don't join tblXtabColumns to
any other table
-your Column Heading expression is
Expr1:[FldName] & Month([OrderDate])
-your Value expression is
DaVal:IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])
Sum
-I set OrderDate for the first three months of 1998
The full SQL is:

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Orders INNER JOIN (Products INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID =
[Order Details].OrderID
WHERE (((Orders.OrderDate) Between #1/1/1998# And #3/31/1998#))
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);

You will get two columns per month QtyXPriceN and QuantityN.

--
Duane Hookom
MS Access MVP
--

Duane,

Thanks for your reply, however, that doesn't seem to solve my problem.
Rather, what I'm looking to do is create something like a crosstab query
(although the standard method of this doesn't seem to allow sufficient row
headings!). Basically, the report needs to look something like this:

Week Number x,
Buyer: Joe Bloggs,
Department 01,
Section 01,
Subsection 01,

Product A, Total quantity of sales for Sunday (Weekday 1),...and so on
to...Total quantity of sales for Saturday (Weekday 7), Week total quantity
of
sales, Week total value of sales.

Product B, Total quantity of sales for Sunday, etc., until,...

Subsection 01 subtotals for all of the above quantities.

Then Products and subtotals for Subsection 02 etc., until...

Section 01 subtotals for all of the above quantities.

Then Products and subtotals for all Subsections under Section 02, etc.,
until...

Department 02 (no Department subtotals are necessary), and so on and so
forth.

The difficulty I'm finding is trying to summarise the total sales for the
week, whilst still being able to list the daily sales by product, within
the
same report.

Thanks again,

Jedster


:

If I understand what you need, you can try something like:
=Sum(Abs(...Your Expression...) * [ValueToSum])
or maybe
=Sum(Abs([Weekday]=1) * [SalesQuantity])

--
Duane Hookom
MS Access MVP


I am trying to create a report based on a flat file to show the
cumulative
sales by day of the week. The problem I am now having is that I
cannot
get
some of my subtotals to calculate correctly.

The table I am using contains the total daily sales quantities for
each
product, together with the corresponding product details. I have then
created a simple query on this to produce the following fields. These
are
listed with samples of the data contained:

Weekday (to identify the day number) eg. 1, 2, 3
Day, eg. Sun, Mon, Tue
WeekNo, eg. 31, 32, 33
DepartmentCode, eg, 94, 95, 96
DepartmentDesc, eg. Homewares, Ambient, Grocery
SectionCode, eg. 41, 42, 43
SectionDesc, eg. Cookware, Tinned, Fresh produce
SubsectionCode, eg. 02, 03, 04
SubsectionDesc, eg. Pans, Baked beans, Tomatoes
BuyerName, eg. James Richardson, Tim Reynolds, Bob Taylor
ProductCode, eg. 415745, 254471, 958764
ProductDesc, eg, Stainless steel 24cm, Heinz 450g, Spanish pack of 6
SalesQuantity, eg, 4, 258, 65
SalesValue, eg. 30.66, 64.50, 45.50

Next, I based a report on this query and grouped it in order of
WeekNo,
BuyerName, DepartmentCode, SectionCode, SubsectionCode and
ProductCode.

In the ProductCode footer, I created a text box to show the total
sales
for
day 1 (Sunday) through to Saturday by using variants of the following
formula:

=IIf([Weekday]=1,[SalesQuantity],0)

To the right of these, I created two boxes with the following criteria
to
show the total sales values for the week:

=Sum([SalesQuantity])
=Sum([SalesValue])

These 9 fields were then copied into two footers I had created for the
SubsectionCode and SectionCode. I am now left with the weekly totals
that
appear to be correct, whilst the daily totals are incorrect.

I think the problem here lies with the fact that I need to somehow
summarise
the data by week, but also retain the 'day' information in order to
produce
the daily totals. Unfortunately, my experience with Access is
insufficient
to know whether this is in any way possible.

Any assistance offered will be much appreciated.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top