RANKING

  • Thread starter Thread starter CAROL
  • Start date Start date
C

CAROL

sorry but my post from yesterday has vanished from
bulletin board!! so let's try again...

need to create query that ranks records by start date for
each item#. item# not unique. need to select first
record in item# group for most recent date (descending
order). thx!
 
Dear Carol:

Here's a way to do it, but not based on your exact column and table
names, as I do not have that information:

SELECT Item, StartDate,
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Item = T.Item AND T1.StartDate > T.StartDate) AS Rank
FROM YourTable T
ORDER BY Item, StartDate DESC;

In a horse race, when there is a tie for first place you have two
"winners" and then "show". It skips from first place to third place,
doesn't it.

If you have two rows in the table with the same Item and StartDate
then the Rank will follow the same rule. If this is not desirable,
there are some alternatives we can discuss.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
thank you tom, however, i am a little confused with
reference to table and table1. do i have to first make a
copy of table named table1 to run this query? would prefer
not to create additional data if not necessary. thx
again.
 
Dear Carol:

As I did not know the name of your table, I just used YourTable to
refer to it in two places. Both are references to the same table.

Where I used T and T1, these are "aliases" that allow the query to
distinguish between two independent references (instances) to the same
table.

There is no need to change anything, and no two tables involved. Just
substitute you actual column names and your (one) table name (in two
places). Let me know how this does for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
sorry to be such a bother, but i cannot locate the problem
here. can you review my code and tell me what i did wrong
as i followed your code below. i am getting the
message "database engine can't find input table or query
item "item pricing1". my input table is spelled correctly
with a space between the two words. thx.

SELECT [Item Pricing].vendor, [Item Pricing].[PFG#], [Item
Pricing].[start date], [Item Pricing].Price, [Item
Pricing].[end date], [Item Pricing].open, [Item Pricing].
[open date], [Item Pricing].[contract sent], [Item
Pricing].[contract recd], [Item Pricing].FOB,
(SELECT COUNT(*) from [item pricing1] where [item
pricing1].[pfg#] = [item pricing].[pfg#] AND [item
pricing1].[start date] > [item pricing].[start date]) as
rank
FROM [Item Pricing]
ORDER BY VENDOR, [PFG#], [start date] DESC
 
Dear Carol:

Sure.

You've dropped the aliasing. Must have:

SELECT T.vendor, T.[PFG#], T.[start date], T.Price,
T.[end date], T.open, T.[open date], T.[contract sent],
T.[contract recd], T.FOB,
(SELECT COUNT(*) from [item pricing1] T1
where T1.[pfg#] = T.[pfg#]
AND T1.[start date] > T.[start date]) as rank
FROM [Item Pricing] T
ORDER BY VENDOR, [PFG#], [start date] DESC

Now, the fact that you've added vendor to the sort makes me
suspicious. I'll be you want the ranking by vendor and PFG#. If so,
here's the change:

SELECT T.vendor, T.[PFG#], T.[start date], T.Price,
T.[end date], T.open, T.[open date], T.[contract sent],
T.[contract recd], T.FOB,
(SELECT COUNT(*) from [item pricing1] T1
where T1.vendor = T.vendor AND T1.[pfg#] = T.[pfg#]
AND T1.[start date] > T.[start date]) as rank
FROM [Item Pricing] T
ORDER BY VENDOR, [PFG#], [start date] DESC

This changes the "group" from item to vendor and item.

This ranking starts from 0. You can add 1 to it thus:

(SELECT COUNT(*) + 1 from [item pricing1] T1

that is, a small change in the one line.

Hope I've got this right. It's so much easier for you if I can write
the SQL for your exact situation rather than have you try to interpret
code I've written which you may not understand.

For reference, this is a "correlated subquery" - something you may
want to study. Also, check out what an "alias" is.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


sorry to be such a bother, but i cannot locate the problem
here. can you review my code and tell me what i did wrong
as i followed your code below. i am getting the
message "database engine can't find input table or query
item "item pricing1". my input table is spelled correctly
with a space between the two words. thx.

SELECT [Item Pricing].vendor, [Item Pricing].[PFG#], [Item
Pricing].[start date], [Item Pricing].Price, [Item
Pricing].[end date], [Item Pricing].open, [Item Pricing].
[open date], [Item Pricing].[contract sent], [Item
Pricing].[contract recd], [Item Pricing].FOB,
(SELECT COUNT(*) from [item pricing1] where [item
pricing1].[pfg#] = [item pricing].[pfg#] AND [item
pricing1].[start date] > [item pricing].[start date]) as
rank
FROM [Item Pricing]
ORDER BY VENDOR, [PFG#], [start date] DESC
-----Original Message-----
Dear Carol:

As I did not know the name of your table, I just used YourTable to
refer to it in two places. Both are references to the same table.

Where I used T and T1, these are "aliases" that allow the query to
distinguish between two independent references (instances) to the same
table.

There is no need to change anything, and no two tables involved. Just
substitute you actual column names and your (one) table name (in two
places). Let me know how this does for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts




.
Click to expand...
Click to expand...
 
Hi,


You forgot the aliases. The syntax for an alias (of a table) is


RealTableName As AliasYouChoosed


Once you have aliased the table, you have to use the alias:


SELECT T.vendor,
T.[PFG#],
T.[start date],
T.Price,
T.[end date],
T.[open],
T.[open date],
T.[contract sent],
T.[contract recd],
T.FOB,
(SELECT COUNT(*)
FROM [item pricing] AS T1
WHERE T1.[pfg#] = T.[pfg#]
AND T1.[start date] > T.[start date])
As rank

FROM [Item Pricing] As T
ORDER BY VENDOR, [PFG#], [start date] DESC


You previously changed the table name: "from [item pricing1] where"
while it basically is: " FROM [item pricing] AS SomeAlias where"




Hoping it may help,
Vanderghast, Access MVP


CAROL said:
sorry to be such a bother, but i cannot locate the problem
here. can you review my code and tell me what i did wrong
as i followed your code below. i am getting the
message "database engine can't find input table or query
item "item pricing1". my input table is spelled correctly
with a space between the two words. thx.

SELECT [Item Pricing].vendor, [Item Pricing].[PFG#], [Item
Pricing].[start date], [Item Pricing].Price, [Item
Pricing].[end date], [Item Pricing].open, [Item Pricing].
[open date], [Item Pricing].[contract sent], [Item
Pricing].[contract recd], [Item Pricing].FOB,
(SELECT COUNT(*) from [item pricing1] where [item
pricing1].[pfg#] = [item pricing].[pfg#] AND [item
pricing1].[start date] > [item pricing].[start date]) as
rank
FROM [Item Pricing]
ORDER BY VENDOR, [PFG#], [start date] DESC
-----Original Message-----
Dear Carol:

As I did not know the name of your table, I just used YourTable to
refer to it in two places. Both are references to the same table.

Where I used T and T1, these are "aliases" that allow the query to
distinguish between two independent references (instances) to the same
table.

There is no need to change anything, and no two tables involved. Just
substitute you actual column names and your (one) table name (in two
places). Let me know how this does for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts




.
Click to expand...
Click to expand...
 
thank you for your patience with walking me thru this.
will work on correcting this tonite. your support is very
much appreciated!
 
Absolutely correct (not that I expected any less from Vanderghast).

The SQL I posted omitted the AS which is optional anyway. I got tired
or putting them in long ago!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,


You forgot the aliases. The syntax for an alias (of a table) is


RealTableName As AliasYouChoosed


Once you have aliased the table, you have to use the alias:


SELECT T.vendor,
T.[PFG#],
T.[start date],
T.Price,
T.[end date],
T.[open],
T.[open date],
T.[contract sent],
T.[contract recd],
T.FOB,
(SELECT COUNT(*)
FROM [item pricing] AS T1
WHERE T1.[pfg#] = T.[pfg#]
AND T1.[start date] > T.[start date])
As rank

FROM [Item Pricing] As T
ORDER BY VENDOR, [PFG#], [start date] DESC


You previously changed the table name: "from [item pricing1] where"
while it basically is: " FROM [item pricing] AS SomeAlias where"




Hoping it may help,
Vanderghast, Access MVP


CAROL said:
sorry to be such a bother, but i cannot locate the problem
here. can you review my code and tell me what i did wrong
as i followed your code below. i am getting the
message "database engine can't find input table or query
item "item pricing1". my input table is spelled correctly
with a space between the two words. thx.

SELECT [Item Pricing].vendor, [Item Pricing].[PFG#], [Item
Pricing].[start date], [Item Pricing].Price, [Item
Pricing].[end date], [Item Pricing].open, [Item Pricing].
[open date], [Item Pricing].[contract sent], [Item
Pricing].[contract recd], [Item Pricing].FOB,
(SELECT COUNT(*) from [item pricing1] where [item
pricing1].[pfg#] = [item pricing].[pfg#] AND [item
pricing1].[start date] > [item pricing].[start date]) as
rank
FROM [Item Pricing]
ORDER BY VENDOR, [PFG#], [start date] DESC
-----Original Message-----
Dear Carol:

As I did not know the name of your table, I just used YourTable to
refer to it in two places. Both are references to the same table.

Where I used T and T1, these are "aliases" that allow the query to
distinguish between two independent references (instances) to the same
table.

There is no need to change anything, and no two tables involved. Just
substitute you actual column names and your (one) table name (in two
places). Let me know how this does for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 13 Jan 2004 12:39:05 -0800,

thank you tom, however, i am a little confused with
reference to table and table1. do i have to first make a
copy of table named table1 to run this query? would prefer
not to create additional data if not necessary. thx
again.


-----Original Message-----
Dear Carol:

Here's a way to do it, but not based on your exact column
and table
names, as I do not have that information:

SELECT Item, StartDate,
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Item = T.Item AND T1.StartDate >
T.StartDate) AS Rank
FROM YourTable T
ORDER BY Item, StartDate DESC;

In a horse race, when there is a tie for first place you
have two
"winners" and then "show". It skips from first place to
third place,
doesn't it.

If you have two rows in the table with the same Item and
StartDate
then the Rank will follow the same rule. If this is not
desirable,
there are some alternatives we can discuss.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 13 Jan 2004 08:47:20 -0800, "CAROL"

sorry but my post from yesterday has vanished from
bulletin board!! so let's try again...

need to create query that ranks records by start date
for
each item#. item# not unique. need to select first
record in item# group for most recent date (descending
order). thx!

.


.
Click to expand...
Click to expand...
Click to expand...
 
Hi,

I didn't see your answer, that is why I took the liberty to jump in,
thinking you were already on vacation, on some sunny beach... :-)


Vanderghast, Access MVP



Tom Ellison said:
Absolutely correct (not that I expected any less from Vanderghast).

The SQL I posted omitted the AS which is optional anyway. I got tired
or putting them in long ago!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,


You forgot the aliases. The syntax for an alias (of a table) is


RealTableName As AliasYouChoosed


Once you have aliased the table, you have to use the alias:


SELECT T.vendor,
T.[PFG#],
T.[start date],
T.Price,
T.[end date],
T.[open],
T.[open date],
T.[contract sent],
T.[contract recd],
T.FOB,
(SELECT COUNT(*)
FROM [item pricing] AS T1
WHERE T1.[pfg#] = T.[pfg#]
AND T1.[start date] > T.[start date])
As rank

FROM [Item Pricing] As T
ORDER BY VENDOR, [PFG#], [start date] DESC


You previously changed the table name: "from [item pricing1] where"
while it basically is: " FROM [item pricing] AS SomeAlias where"




Hoping it may help,
Vanderghast, Access MVP


CAROL said:
sorry to be such a bother, but i cannot locate the problem
here. can you review my code and tell me what i did wrong
as i followed your code below. i am getting the
message "database engine can't find input table or query
item "item pricing1". my input table is spelled correctly
with a space between the two words. thx.

SELECT [Item Pricing].vendor, [Item Pricing].[PFG#], [Item
Pricing].[start date], [Item Pricing].Price, [Item
Pricing].[end date], [Item Pricing].open, [Item Pricing].
[open date], [Item Pricing].[contract sent], [Item
Pricing].[contract recd], [Item Pricing].FOB,
(SELECT COUNT(*) from [item pricing1] where [item
pricing1].[pfg#] = [item pricing].[pfg#] AND [item
pricing1].[start date] > [item pricing].[start date]) as
rank
FROM [Item Pricing]
ORDER BY VENDOR, [PFG#], [start date] DESC
-----Original Message-----
Dear Carol:

As I did not know the name of your table, I just used
YourTable to
refer to it in two places. Both are references to the
same table.

Where I used T and T1, these are "aliases" that allow the
query to
distinguish between two independent references
(instances) to the same
table.

There is no need to change anything, and no two tables
involved. Just
substitute you actual column names and your (one) table
name (in two
places). Let me know how this does for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 13 Jan 2004 12:39:05 -0800,

thank you tom, however, i am a little confused with
reference to table and table1. do i have to first make
a
copy of table named table1 to run this query? would
prefer
not to create additional data if not necessary. thx
again.


-----Original Message-----
Dear Carol:

Here's a way to do it, but not based on your exact
column
and table
names, as I do not have that information:

SELECT Item, StartDate,
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Item = T.Item AND T1.StartDate >
T.StartDate) AS Rank
FROM YourTable T
ORDER BY Item, StartDate DESC;

In a horse race, when there is a tie for first place
you
have two
"winners" and then "show". It skips from first place
to
third place,
doesn't it.

If you have two rows in the table with the same Item
and
StartDate
then the Rank will follow the same rule. If this is
not
desirable,
there are some alternatives we can discuss.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 13 Jan 2004 08:47:20 -0800, "CAROL"

sorry but my post from yesterday has vanished from
bulletin board!! so let's try again...

need to create query that ranks records by start date
for
each item#. item# not unique. need to select first
record in item# group for most recent date (descending
order). thx!

.


.
Click to expand...
Click to expand...
Click to expand...
 
Dear Michel:

Hey, not problem. I just wanted to make sure Carol would not be
confused, but rather that we were really agreeing on this. At least,
I think we agree.

WIll you please, please come to Seattle in April? Love to meet you
this time!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

I didn't see your answer, that is why I took the liberty to jump in,
thinking you were already on vacation, on some sunny beach... :-)


Vanderghast, Access MVP



Tom Ellison said:
Absolutely correct (not that I expected any less from Vanderghast).

The SQL I posted omitted the AS which is optional anyway. I got tired
or putting them in long ago!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,


You forgot the aliases. The syntax for an alias (of a table) is


RealTableName As AliasYouChoosed


Once you have aliased the table, you have to use the alias:


SELECT T.vendor,
T.[PFG#],
T.[start date],
T.Price,
T.[end date],
T.[open],
T.[open date],
T.[contract sent],
T.[contract recd],
T.FOB,
(SELECT COUNT(*)
FROM [item pricing] AS T1
WHERE T1.[pfg#] = T.[pfg#]
AND T1.[start date] > T.[start date])
As rank

FROM [Item Pricing] As T
ORDER BY VENDOR, [PFG#], [start date] DESC


You previously changed the table name: "from [item pricing1] where"
while it basically is: " FROM [item pricing] AS SomeAlias where"




Hoping it may help,
Vanderghast, Access MVP


sorry to be such a bother, but i cannot locate the problem
here. can you review my code and tell me what i did wrong
as i followed your code below. i am getting the
message "database engine can't find input table or query
item "item pricing1". my input table is spelled correctly
with a space between the two words. thx.

SELECT [Item Pricing].vendor, [Item Pricing].[PFG#], [Item
Pricing].[start date], [Item Pricing].Price, [Item
Pricing].[end date], [Item Pricing].open, [Item Pricing].
[open date], [Item Pricing].[contract sent], [Item
Pricing].[contract recd], [Item Pricing].FOB,
(SELECT COUNT(*) from [item pricing1] where [item
pricing1].[pfg#] = [item pricing].[pfg#] AND [item
pricing1].[start date] > [item pricing].[start date]) as
rank
FROM [Item Pricing]
ORDER BY VENDOR, [PFG#], [start date] DESC
-----Original Message-----
Dear Carol:

As I did not know the name of your table, I just used
YourTable to
refer to it in two places. Both are references to the
same table.

Where I used T and T1, these are "aliases" that allow the
query to
distinguish between two independent references
(instances) to the same
table.

There is no need to change anything, and no two tables
involved. Just
substitute you actual column names and your (one) table
name (in two
places). Let me know how this does for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 13 Jan 2004 12:39:05 -0800,

thank you tom, however, i am a little confused with
reference to table and table1. do i have to first make
a
copy of table named table1 to run this query? would
prefer
not to create additional data if not necessary. thx
again.


-----Original Message-----
Dear Carol:

Here's a way to do it, but not based on your exact
column
and table
names, as I do not have that information:

SELECT Item, StartDate,
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Item = T.Item AND T1.StartDate >
T.StartDate) AS Rank
FROM YourTable T
ORDER BY Item, StartDate DESC;

In a horse race, when there is a tie for first place
you
have two
"winners" and then "show". It skips from first place
to
third place,
doesn't it.

If you have two rows in the table with the same Item
and
StartDate
then the Rank will follow the same rule. If this is
not
desirable,
there are some alternatives we can discuss.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 13 Jan 2004 08:47:20 -0800, "CAROL"

sorry but my post from yesterday has vanished from
bulletin board!! so let's try again...

need to create query that ranks records by start date
for
each item#. item# not unique. need to select first
record in item# group for most recent date (descending
order). thx!

.


.
Click to expand...
Click to expand...
Click to expand...
 
Hi,


I have already made myself to the idea that I would not be able to
spend the free time... in fact, the span of time. Spring is a busy season,
for civil engineers... and potentially require fast reaction to problems.



Vanderghast, Access MVP
 
Dear Michel:

Having been there, I can guarantee you there won't be much free time
for us in Seattle.

And if an old electrical engineer like me can go, that should give you
enough excuse. And it gives just one more reason I'd enjoy meeting
you!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi -
I used this thread to try to get a ranking of Sales in our
Stores. What I want is the NUMBER the store is Ranked, so
I can print it on another report. For example, one
Department in one store may rank 4th in the chain (which I
show on the report via sorting/grouping descending order
of sales for the dept), but I want to show that the store
in which the dept ranked 4th was 17th in the chain OVERALL
YTD sales. So, I just want to PRINT the RANK # associated
with the descending order of YTD sales.

I tried to do a MAKE TABLE query and create and ID# on
each record, but I couldn't do that. So, I tried this for
Ranking. In the RANK field, I get all "1", not 1-18, as I
need.

Code:
SELECT T.SalesDate, T.[YTDTY Sales], T.StoreNum, (SELECT
COUNT(*)
FROM [T:Store Sales Data] AS T1
WHERE T1.[YTDTY Sales] = T.[YTDTY Sales]
AND T1.SalesDate = T.SalesDate) AS RANK
FROM [T:Store Sales Data] AS T
WHERE (((T.SalesDate)=[Enter Sales Date]) AND ((T.StoreNum)
<>99))
ORDER BY T.[YTDTY Sales] DESC;

Thanks - I am the only "IT" person in this techno-
challenged and techno-phobic company, so I have no one to
ask!
Sara
 
Dear Sara:

I'm going to try to get to the point you want one step at a time.

Below I've attempted to create my original query using your table and
column names:

SELECT SalesDate, [YTDTY Sales], StoreNum,
(SELECT COUNT(*) FROM [T:Store Sales Data] AS T1
WHERE T1.[YTDTY Sales] = T.[YTDTY Sales]
AND T1.SalesDate > T.SalesDate) AS RANK
FROM [T:Store Sales Data] AS T
WHERE StoreNum) <> 99
ORDER BY [YTDTY Sales] DESC;

Does this work just as written? Does you table name actually start
with "T:"? Or is the table named [Store Sales Data]? If the latter,
remove the "T:" both places it appears in the query. Now does it
work? Does it give any results at all?

What is your idea in entering a Sales Date and filtering to only that
date? That would seem to preclude doing any ranking, since the
ranking is by date, and since you've just prevented the query from
considering more than one date.

Anyway, starting from my query above, can you get any results at all?
Are the correct? If not, in what way?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi -
I used this thread to try to get a ranking of Sales in our
Stores. What I want is the NUMBER the store is Ranked, so
I can print it on another report. For example, one
Department in one store may rank 4th in the chain (which I
show on the report via sorting/grouping descending order
of sales for the dept), but I want to show that the store
in which the dept ranked 4th was 17th in the chain OVERALL
YTD sales. So, I just want to PRINT the RANK # associated
with the descending order of YTD sales.

I tried to do a MAKE TABLE query and create and ID# on
each record, but I couldn't do that. So, I tried this for
Ranking. In the RANK field, I get all "1", not 1-18, as I
need.

Code:
SELECT T.SalesDate, T.[YTDTY Sales], T.StoreNum, (SELECT
COUNT(*)
FROM [T:Store Sales Data] AS T1
WHERE T1.[YTDTY Sales] = T.[YTDTY Sales]
AND T1.SalesDate = T.SalesDate) AS RANK
FROM [T:Store Sales Data] AS T
WHERE (((T.SalesDate)=[Enter Sales Date]) AND ((T.StoreNum)
<>99))
ORDER BY T.[YTDTY Sales] DESC;

Thanks - I am the only "IT" person in this techno-
challenged and techno-phobic company, so I have no one to
ask!
Sara
-----Original Message-----
Dear Carol:

Here's a way to do it, but not based on your exact column and table
names, as I do not have that information:

SELECT Item, StartDate,
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Item = T.Item AND T1.StartDate > T.StartDate) AS Rank
FROM YourTable T
ORDER BY Item, StartDate DESC;

In a horse race, when there is a tie for first place you have two
"winners" and then "show". It skips from first place to third place,
doesn't it.

If you have two rows in the table with the same Item and StartDate
then the Rank will follow the same rule. If this is not desirable,
there are some alternatives we can discuss.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts




.
Click to expand...
Click to expand...
 
Back
Top