SORTING ON A SUB-TOTAL

[Guest]

Creating a report that totals quantity sold of part no's by the first 8
digits of the number (part no field contains style, color & size codes, but I
am only concerned with style which are the first 8 digits). First created a
query with Part no, description, and quantity fields. Quantity field is
totaled by "sum", other two are "group by". Then created report based on this
query, with one "stock no" group and with a "stock no" footer section in
which I placed text box containing "left$([stock no],8)", a text box for
"description" and summary box containing "sum(sumofquantity)". This works
fine - I get a nice report sorted by stock no, that summarized quantity sold
only by style (the 1st digits), but I want the report to be sorted by this
quantity sold, descending order, so most sold are at top. Tried to add
"sumofquantity" field to top of sort order, but no go 'cause it sorts on the
underlying data in the group, I guess, and results in wrong data (and
improper sort). Help/suggestions would be much appreciated. Thanks!

 

[Duane Hookom]

You need to create a totals query that groups by Left([Stock No], 8) and
sums the quantity. Add this query to your report's record source so you can
add the sum to the fields available for sorting in the report.

 

[Guest]

Thanks for quick response, Duane. Actually, that was my original approach,
but when I ad the Left function as described to the query (in the criteria
box for the [stock no] field), the query returns no results. FYI, a typical
stock no would like this "PAT11347-OCB~S" and is a text field in the
database. Changing the 8 to a 15 does return results, but obviously, includes
data I don't want.

You need to create a totals query that groups by Left([Stock No], 8) and
sums the quantity. Add this query to your report's record source so you can
add the sum to the fields available for sorting in the report.

--
Duane Hookom
MS Access MVP

Creating a report that totals quantity sold of part no's by the first 8
digits of the number (part no field contains style, color & size codes,
but I
am only concerned with style which are the first 8 digits). First created
a
query with Part no, description, and quantity fields. Quantity field is
totaled by "sum", other two are "group by". Then created report based on
this
query, with one "stock no" group and with a "stock no" footer section in
which I placed text box containing "left$([stock no],8)", a text box for
"description" and summary box containing "sum(sumofquantity)". This works
fine - I get a nice report sorted by stock no, that summarized quantity
sold
only by style (the 1st digits), but I want the report to be sorted by this
quantity sold, descending order, so most sold are at top. Tried to add
"sumofquantity" field to top of sort order, but no go 'cause it sorts on
the
underlying data in the group, I guess, and results in wrong data (and
improper sort). Help/suggestions would be much appreciated. Thanks!

 

[Duane Hookom]

Please share your SQL view of the totals query.
--
Duane Hookom
MS Access MVP

Thanks for quick response, Duane. Actually, that was my original approach,
but when I ad the Left function as described to the query (in the criteria
box for the [stock no] field), the query returns no results. FYI, a
typical
stock no would like this "PAT11347-OCB~S" and is a text field in the
database. Changing the 8 to a 15 does return results, but obviously,
includes
data I don't want.

You need to create a totals query that groups by Left([Stock No], 8) and
sums the quantity. Add this query to your report's record source so you
can
add the sum to the fields available for sorting in the report.

--
Duane Hookom
MS Access MVP

Creating a report that totals quantity sold of part no's by the first 8
digits of the number (part no field contains style, color & size codes,
but I
am only concerned with style which are the first 8 digits). First
created
a
query with Part no, description, and quantity fields. Quantity field is
totaled by "sum", other two are "group by". Then created report based
on
this
query, with one "stock no" group and with a "stock no" footer section
in
which I placed text box containing "left$([stock no],8)", a text box
for
"description" and summary box containing "sum(sumofquantity)". This
works
fine - I get a nice report sorted by stock no, that summarized quantity
sold
only by style (the 1st digits), but I want the report to be sorted by
this
quantity sold, descending order, so most sold are at top. Tried to add
"sumofquantity" field to top of sort order, but no go 'cause it sorts
on
the
underlying data in the group, I guess, and results in wrong data (and
improper sort). Help/suggestions would be much appreciated. Thanks!

 

[Guest]

Here's the sql:
SELECT [PAT S06 FILTERED].[STOCK NO], [PAT S06 FILTERED].DESCRIPTION,
Sum([PAT S06 FILTERED].QUANTITY) AS SumOfQUANTITY
FROM [PAT S06 FILTERED]
GROUP BY [PAT S06 FILTERED].[STOCK NO], [PAT S06 FILTERED].DESCRIPTION
HAVING ((([PAT S06 FILTERED].[STOCK NO])=Left([STOCK NO],8)));

The table the sql is referring (PAT S06 FILTERED) to was created from a
query that filtered out all stock no's that did not start with "PAT" from the
original table. I did that cause I wasn't sure how to create a criteria
within one query that would filter both the "PAT" and then return only the
first 8 characters. Thanks in advance for your help!

Please share your SQL view of the totals query.
--
Duane Hookom
MS Access MVP

Thanks for quick response, Duane. Actually, that was my original approach,
but when I ad the Left function as described to the query (in the criteria
box for the [stock no] field), the query returns no results. FYI, a
typical
stock no would like this "PAT11347-OCB~S" and is a text field in the
database. Changing the 8 to a 15 does return results, but obviously,
includes
data I don't want.

You need to create a totals query that groups by Left([Stock No], 8) and
sums the quantity. Add this query to your report's record source so you
can
add the sum to the fields available for sorting in the report.

--
Duane Hookom
MS Access MVP

Creating a report that totals quantity sold of part no's by the first 8
digits of the number (part no field contains style, color & size codes,
but I
am only concerned with style which are the first 8 digits). First
created
a
query with Part no, description, and quantity fields. Quantity field is
totaled by "sum", other two are "group by". Then created report based
on
this
query, with one "stock no" group and with a "stock no" footer section
in
which I placed text box containing "left$([stock no],8)", a text box
for
"description" and summary box containing "sum(sumofquantity)". This
works
fine - I get a nice report sorted by stock no, that summarized quantity
sold
only by style (the 1st digits), but I want the report to be sorted by
this
quantity sold, descending order, so most sold are at top. Tried to add
"sumofquantity" field to top of sort order, but no go 'cause it sorts
on
the
underlying data in the group, I guess, and results in wrong data (and
improper sort). Help/suggestions would be much appreciated. Thanks!

 

[Duane Hookom]

"You need to create a totals query that groups by Left([Stock No], 8) and
sums the quantity." This statement didn't say anything about setting a
criteria. Try:

SELECT Left([STOCK NO],8) As StockNum8,Sum(QUANTITY) AS SumOfQUANTITY
FROM [PAT S06 FILTERED]
GROUP BY Left([STOCK NO],8);


--
Duane Hookom
MS Access MVP

Here's the sql:
SELECT [PAT S06 FILTERED].[STOCK NO], [PAT S06 FILTERED].DESCRIPTION,
Sum([PAT S06 FILTERED].QUANTITY) AS SumOfQUANTITY
FROM [PAT S06 FILTERED]
GROUP BY [PAT S06 FILTERED].[STOCK NO], [PAT S06 FILTERED].DESCRIPTION
HAVING ((([PAT S06 FILTERED].[STOCK NO])=Left([STOCK NO],8)));

The table the sql is referring (PAT S06 FILTERED) to was created from a
query that filtered out all stock no's that did not start with "PAT" from
the
original table. I did that cause I wasn't sure how to create a criteria
within one query that would filter both the "PAT" and then return only the
first 8 characters. Thanks in advance for your help!

Please share your SQL view of the totals query.
--
Duane Hookom
MS Access MVP

Thanks for quick response, Duane. Actually, that was my original
approach,
but when I ad the Left function as described to the query (in the
criteria
box for the [stock no] field), the query returns no results. FYI, a
typical
stock no would like this "PAT11347-OCB~S" and is a text field in the
database. Changing the 8 to a 15 does return results, but obviously,
includes
data I don't want.

:

You need to create a totals query that groups by Left([Stock No], 8)
and
sums the quantity. Add this query to your report's record source so
you
can
add the sum to the fields available for sorting in the report.

--
Duane Hookom
MS Access MVP

Creating a report that totals quantity sold of part no's by the
first 8
digits of the number (part no field contains style, color & size
codes,
but I
am only concerned with style which are the first 8 digits). First
created
a
query with Part no, description, and quantity fields. Quantity field
is
totaled by "sum", other two are "group by". Then created report
based
on
this
query, with one "stock no" group and with a "stock no" footer
section
in
which I placed text box containing "left$([stock no],8)", a text
box
for
"description" and summary box containing "sum(sumofquantity)". This
works
fine - I get a nice report sorted by stock no, that summarized
quantity
sold
only by style (the 1st digits), but I want the report to be sorted
by
this
quantity sold, descending order, so most sold are at top. Tried to
add
"sumofquantity" field to top of sort order, but no go 'cause it
sorts
on
the
underlying data in the group, I guess, and results in wrong data
(and
improper sort). Help/suggestions would be much appreciated. Thanks!

 

[Guest]

Duane, that worked beautifully, and so simple! Thanks much!

"You need to create a totals query that groups by Left([Stock No], 8) and
sums the quantity." This statement didn't say anything about setting a
criteria. Try:

SELECT Left([STOCK NO],8) As StockNum8,Sum(QUANTITY) AS SumOfQUANTITY
FROM [PAT S06 FILTERED]
GROUP BY Left([STOCK NO],8);


--
Duane Hookom
MS Access MVP

Here's the sql:
SELECT [PAT S06 FILTERED].[STOCK NO], [PAT S06 FILTERED].DESCRIPTION,
Sum([PAT S06 FILTERED].QUANTITY) AS SumOfQUANTITY
FROM [PAT S06 FILTERED]
GROUP BY [PAT S06 FILTERED].[STOCK NO], [PAT S06 FILTERED].DESCRIPTION
HAVING ((([PAT S06 FILTERED].[STOCK NO])=Left([STOCK NO],8)));

The table the sql is referring (PAT S06 FILTERED) to was created from a
query that filtered out all stock no's that did not start with "PAT" from
the
original table. I did that cause I wasn't sure how to create a criteria
within one query that would filter both the "PAT" and then return only the
first 8 characters. Thanks in advance for your help!

Please share your SQL view of the totals query.
--
Duane Hookom
MS Access MVP

Thanks for quick response, Duane. Actually, that was my original
approach,
but when I ad the Left function as described to the query (in the
criteria
box for the [stock no] field), the query returns no results. FYI, a
typical
stock no would like this "PAT11347-OCB~S" and is a text field in the
database. Changing the 8 to a 15 does return results, but obviously,
includes
data I don't want.

:

You need to create a totals query that groups by Left([Stock No], 8)
and
sums the quantity. Add this query to your report's record source so
you
can
add the sum to the fields available for sorting in the report.

--
Duane Hookom
MS Access MVP

Creating a report that totals quantity sold of part no's by the
first 8
digits of the number (part no field contains style, color & size
codes,
but I
am only concerned with style which are the first 8 digits). First
created
a
query with Part no, description, and quantity fields. Quantity field
is
totaled by "sum", other two are "group by". Then created report
based
on
this
query, with one "stock no" group and with a "stock no" footer
section
in
which I placed text box containing "left$([stock no],8)", a text
box
for
"description" and summary box containing "sum(sumofquantity)". This
works
fine - I get a nice report sorted by stock no, that summarized
quantity
sold
only by style (the 1st digits), but I want the report to be sorted
by
this
quantity sold, descending order, so most sold are at top. Tried to
add
"sumofquantity" field to top of sort order, but no go 'cause it
sorts
on
the
underlying data in the group, I guess, and results in wrong data
(and
improper sort). Help/suggestions would be much appreciated. Thanks!