Field showing record number

[Pele]

I created a simple query that contains just the [Product
Name] and [Gross Margin %] and I have sorted them by
ascending [Gross Margin %]. Another field that I need in
this query is one that indicates the record number of a
particular record in the query (after the sorting has been
completed). Let's call that field [RecordNumb].

I am trying to plot this query on a Chart form but what I
want to plot is the [gross Margin %] on the Y-axis and
[RecordNum] on the X-axis. Note that I am not plotting
[Product Name] since the graph will be too messy.

Can anybody help me with how to create a field called
[recordnumb] that will take on the value of the
recordposition on the query.

Toks

 

[Tom Ellison]

Dear Toks:

The concept of the "number" of a record has no meaning except when a sort
order has been specified. You have said that this is to be done "after
sorting has been completed" so I assume you have exactly this in mind.

The value you seed, then, is what I could call a "rank" of each row. This
is accomplished using a subquery that counts all the rows that preceed the
row using the key column(s) by which you are sorting.

Using the Jet database engine this may be fairly slow, so if you are
producing a report you may want to use a feature of reports to produce a
running sum of a column containing a constant value "1" to more quickly
produce this. If it MUST be done in a query, it can be done as I have said.

If you need this applied to a specific situation you have, I would need the
details of your exact situation in order to supply a specific solution for
you. One of the best ways to specify this would be to provide the SQL of a
query that produces all the desired columns except this "rank" column and
includes the ORDER BY under which you want the "rank" to be counted. I
could readily add the additional column to that in most cases.

 

[Tom Ellison]

Dear Pele (not Toks?):

SELECT [Product ID], CummPctSKUGrossProfit_Descend,
(SELECT COUNT(*) FROM [4Product Quantitative Stage 3_table] T1
WHERE T.CummPctSKUGrossProfit_Descend >
T1.CummPctSKUGrossProfit_Descend)
AS Rank
FROM [4Product Quantitative Stage 3_table] T
ORDER BY CummPctSKUGrossProfit_Descend;

I have changed your query as follows:

1. Alias the table in the FROM clause with T

2. Add a new column for Rank which is a subquery on the same table, aliased
T1, to count how many rows have a lower CummPctSKUGrossProfit_Descend value.

The rank value will start with zero. If you want it to start from 1, just
add one by inserting " + 1" just before where it says " AS Rank"

If there are two or more rows with identical values for
CummPctSKUGrossProfit_Descend then you have a "tie" for the associated rank
in the table. Naturally enough, the query will then return multiple rows of
the same rank. If undesirable, you need to add more columns to the ORDER BY
and to the subquery in order to break the tie, while retaining the order of
the rows returned by the query in a sequence matching the rank values.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Tom,

Thanks for your reply. I need to create the [rank] in the
query (and not a report) since I will then use the Chart
wizard to create a plot using the [rank] field.

Anyway, below is an SQL of the query. It just selects two
fields and sorts by one of them. I plan on plotting the
[rank] field (when created) on the X-axis and the
[CummPctSKUGrossProfit_Descend] field will be on the Y-
axis.

I hope the SQL will make it possible to help me write the
code to do this. Thanks.

Pele


SELECT [4Product Quantitative Stage 3_table].[Product ID],
[4Product Quantitative Stage
3_table].CummPctSKUGrossProfit_Descend
FROM [4Product Quantitative Stage 3_table]
ORDER BY [4Product Quantitative Stage
3_table].CummPctSKUGrossProfit_Descend;

-----Original Message-----
Dear Toks:

The concept of the "number" of a record has no meaning except when a sort
order has been specified. You have said that this is to be done "after
sorting has been completed" so I assume you have exactly this in mind.

The value you seed, then, is what I could call a "rank" of each row. This
is accomplished using a subquery that counts all the rows that preceed the
row using the key column(s) by which you are sorting.

Using the Jet database engine this may be fairly slow, so if you are
producing a report you may want to use a feature of reports to produce a
running sum of a column containing a constant value "1" to more quickly
produce this. If it MUST be done in a query, it can be done as I have said.

If you need this applied to a specific situation you have, I would need the
details of your exact situation in order to supply a specific solution for
you. One of the best ways to specify this would be to provide the SQL of a
query that produces all the desired columns except this "rank" column and
includes the ORDER BY under which you want the "rank" to be counted. I
could readily add the additional column to that in most cases.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

I created a simple query that contains just the [Product
Name] and [Gross Margin %] and I have sorted them by
ascending [Gross Margin %]. Another field that I need in
this query is one that indicates the record number of a
particular record in the query (after the sorting has been
completed). Let's call that field [RecordNumb].

I am trying to plot this query on a Chart form but what I
want to plot is the [gross Margin %] on the Y-axis and
[RecordNum] on the X-axis. Note that I am not plotting
[Product Name] since the graph will be too messy.

Can anybody help me with how to create a field called
[recordnumb] that will take on the value of the
recordposition on the query.

Toks

.

 

[Pele]

Thanks Tom,

I was away for awhile and just got back. I will give it a
short.

Pele

-----Original Message-----
Dear Pele (not Toks?):

SELECT [Product ID], CummPctSKUGrossProfit_Descend,
(SELECT COUNT(*) FROM [4Product Quantitative Stage 3_table] T1
WHERE T.CummPctSKUGrossProfit_Descend >
T1.CummPctSKUGrossProfit_Descend)
AS Rank
FROM [4Product Quantitative Stage 3_table] T
ORDER BY CummPctSKUGrossProfit_Descend;

I have changed your query as follows:

1. Alias the table in the FROM clause with T

2. Add a new column for Rank which is a subquery on the same table, aliased
T1, to count how many rows have a lower

CummPctSKUGrossProfit_Descend value.

The rank value will start with zero. If you want it to start from 1, just
add one by inserting " + 1" just before where it says " AS Rank"

If there are two or more rows with identical values for
CummPctSKUGrossProfit_Descend then you have a "tie" for the associated rank
in the table. Naturally enough, the query will then return multiple rows of
the same rank. If undesirable, you need to add more columns to the ORDER BY
and to the subquery in order to break the tie, while retaining the order of
the rows returned by the query in a sequence matching the rank values.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Tom,

Thanks for your reply. I need to create the [rank] in the
query (and not a report) since I will then use the Chart
wizard to create a plot using the [rank] field.

Anyway, below is an SQL of the query. It just selects two
fields and sorts by one of them. I plan on plotting the
[rank] field (when created) on the X-axis and the
[CummPctSKUGrossProfit_Descend] field will be on the Y-
axis.

I hope the SQL will make it possible to help me write the
code to do this. Thanks.

Pele


SELECT [4Product Quantitative Stage 3_table].[Product ID],
[4Product Quantitative Stage
3_table].CummPctSKUGrossProfit_Descend
FROM [4Product Quantitative Stage 3_table]
ORDER BY [4Product Quantitative Stage
3_table].CummPctSKUGrossProfit_Descend;

-----Original Message-----
Dear Toks:

The concept of the "number" of a record has no meaning except when a sort
order has been specified. You have said that this is

to
be done "after

sorting has been completed" so I assume you have

exactly
this in mind.

The value you seed, then, is what I could call a "rank" of each row. This
is accomplished using a subquery that counts all the

rows
that preceed the

row using the key column(s) by which you are sorting.

Using the Jet database engine this may be fairly slow,

so
if you are

producing a report you may want to use a feature of reports to produce a
running sum of a column containing a constant value "1" to more quickly
produce this. If it MUST be done in a query, it can be done as I have said.

If you need this applied to a specific situation you have, I would need the
details of your exact situation in order to supply a specific solution for
you. One of the best ways to specify this would be to provide the SQL of a
query that produces all the desired columns except this "rank" column and
includes the ORDER BY under which you want the "rank"

to
be counted. I

could readily add the additional column to that in most cases.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

I created a simple query that contains just the [Product
Name] and [Gross Margin %] and I have sorted them by
ascending [Gross Margin %]. Another field that I need in
this query is one that indicates the record number of a
particular record in the query (after the sorting has been
completed). Let's call that field [RecordNumb].

I am trying to plot this query on a Chart form but

what
I

want to plot is the [gross Margin %] on the Y-axis and
[RecordNum] on the X-axis. Note that I am not plotting
[Product Name] since the graph will be too messy.

Can anybody help me with how to create a field called
[recordnumb] that will take on the value of the
recordposition on the query.

Toks


.

.

 

[Pele]

Tom,

I created the SQL based on your suggestion (see below),
but Access won't run it because it says it has syntax
error. I have isolated the sections where Access had
highlighted as being problematic.

Is there anything I should do?

Pele




SELECT [4Product Quantitative Stage 3_table].ProductNME,
[4Product Quantitative Stage
3_table].CummPctSKUGrossProfit_Descend, [4Product
Quantitative Stage 3_table].PctSKUVolumeCE,

PROBLEM IN THIS SECTION=====
(SELECT COUNT(*) FROM [4Product Quantitative stage
3_table] T1 WHERE T.CummpctSKUGrossProfit_Descend >
T1.CummPctSKUGross Profit_Descend)
PROBLEM IN SECTION ABOVE=======

As Rank
FROM [4Product Quantitative Stage 3_table] T
ORDER BY [4Product Quantitative Stage
3_table].CummPctSKUGrossProfit_Descend, [4Product
Quantitative Stage 3_table].PctSKUVolumeCE;

-----Original Message-----
Dear Pele (not Toks?):

SELECT [Product ID], CummPctSKUGrossProfit_Descend,
(SELECT COUNT(*) FROM [4Product Quantitative Stage 3_table] T1
WHERE T.CummPctSKUGrossProfit_Descend >
T1.CummPctSKUGrossProfit_Descend)
AS Rank
FROM [4Product Quantitative Stage 3_table] T
ORDER BY CummPctSKUGrossProfit_Descend;

I have changed your query as follows:

1. Alias the table in the FROM clause with T

2. Add a new column for Rank which is a subquery on the same table, aliased
T1, to count how many rows have a lower

CummPctSKUGrossProfit_Descend value.

The rank value will start with zero. If you want it to start from 1, just
add one by inserting " + 1" just before where it says " AS Rank"

If there are two or more rows with identical values for
CummPctSKUGrossProfit_Descend then you have a "tie" for the associated rank
in the table. Naturally enough, the query will then return multiple rows of
the same rank. If undesirable, you need to add more columns to the ORDER BY
and to the subquery in order to break the tie, while retaining the order of
the rows returned by the query in a sequence matching the rank values.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Tom,

Thanks for your reply. I need to create the [rank] in the
query (and not a report) since I will then use the Chart
wizard to create a plot using the [rank] field.

Anyway, below is an SQL of the query. It just selects two
fields and sorts by one of them. I plan on plotting the
[rank] field (when created) on the X-axis and the
[CummPctSKUGrossProfit_Descend] field will be on the Y-
axis.

I hope the SQL will make it possible to help me write the
code to do this. Thanks.

Pele


SELECT [4Product Quantitative Stage 3_table].[Product ID],
[4Product Quantitative Stage
3_table].CummPctSKUGrossProfit_Descend
FROM [4Product Quantitative Stage 3_table]
ORDER BY [4Product Quantitative Stage
3_table].CummPctSKUGrossProfit_Descend;

-----Original Message-----
Dear Toks:

The concept of the "number" of a record has no meaning except when a sort
order has been specified. You have said that this is

to
be done "after

sorting has been completed" so I assume you have

exactly
this in mind.

The value you seed, then, is what I could call a "rank" of each row. This
is accomplished using a subquery that counts all the

rows
that preceed the

row using the key column(s) by which you are sorting.

Using the Jet database engine this may be fairly slow,

so
if you are

producing a report you may want to use a feature of reports to produce a
running sum of a column containing a constant value "1" to more quickly
produce this. If it MUST be done in a query, it can be done as I have said.

If you need this applied to a specific situation you have, I would need the
details of your exact situation in order to supply a specific solution for
you. One of the best ways to specify this would be to provide the SQL of a
query that produces all the desired columns except this "rank" column and
includes the ORDER BY under which you want the "rank"

to
be counted. I

could readily add the additional column to that in most cases.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

I created a simple query that contains just the [Product
Name] and [Gross Margin %] and I have sorted them by
ascending [Gross Margin %]. Another field that I need in
this query is one that indicates the record number of a
particular record in the query (after the sorting has been
completed). Let's call that field [RecordNumb].

I am trying to plot this query on a Chart form but

what
I

want to plot is the [gross Margin %] on the Y-axis and
[RecordNum] on the X-axis. Note that I am not plotting
[Product Name] since the graph will be too messy.

Can anybody help me with how to create a field called
[recordnumb] that will take on the value of the
recordposition on the query.

Toks


.

.