A new One on RANKING

[Guest]

Hi -
I used the prior (Jan 13 and 14) 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

 

[Gary]

Just a quick suggestion here - I didn't have time to review your problem in
depth - but it appears maybe you need to change the second equality in your
nested SELECT to '<'?

I had a (possibly) similar ranking problem - here was the snippet suggested
to me (see my post "newbie to crosstabs" on 1/14/04: 11:39am).
SELECT Author, Title,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Author = T.Author AND T1.Title < T.Title) AS Rank
FROM YourTable T
ORDER BY Author, Title

 

[Ken Snell]

The query statement that you posted appears to have one major typo to
start... in the third line, you have this:
FROM [T:Store Sales Data] AS T1
but I think you want this:
FROM T.[Store Sales Data] AS T1

However, the problem with your query's result isn't the above. It's that
you're always going to get a value of 1 for the ranking because the subquery
will only find one record every time and thus give a count of 1 every time,
because you're WHERE statement is always finding the exact match:
(SELECT COUNT(*)
FROM [T:Store Sales Data] AS T1
WHERE T1.[YTDTY Sales] = T.[YTDTY Sales]
AND T1.SalesDate = T.SalesDate) AS RANK

Try this as the subquery:
(SELECT COUNT(*)
FROM [T:Store Sales Data] AS T1
WHERE T1.[YTDTY Sales] >= T.[YTDTY Sales]
AND T1.SalesDate = T.SalesDate) AS RANK

Note that addition of the "greater than" operator in the third line (I may
have this backwards; if the ranking is opposite what you expect, then change
it to the "lesser than" operator).

 

[Guest]

Thanks. I tried both suggestions:
Count +1 makes all the ranks "2", instead of "1".
< or <= for the Select has no impact, or makes the Rank
Column "0".
The table has sale date, StoreNumber,
TotalSalesfortheDay. I have to select the single DAY I'm
interested in, which is a parameter input, which is why I
did SalesDate = SalesDate.
Any help?
Thanks

-----Original Message-----
Just a quick suggestion here - I didn't have time to review your problem in
depth - but it appears maybe you need to change the second equality in your
nested SELECT to '<'?

I had a (possibly) similar ranking problem - here was the snippet suggested
to me (see my post "newbie to crosstabs" on 1/14/04: 11:39am).
SELECT Author, Title,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Author = T.Author AND T1.Title < T.Title) AS Rank
FROM YourTable T
ORDER BY Author, Title

Hi -
I used the prior (Jan 13 and 14) 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

.

 

[Guest]

I tried the change to < and all RANK comes out "0". COUNT
+1 makes all RANK "2". In short, I need to rank the
store's sales for the day in descending order. The Table
contains SaleDate,StoreNumber and SalesfortheDay. I just
need to be able to use the Rank # in other reports.I feel
like it can be done, just don't know how!
Thanks so much.

-----Original Message-----
Just a quick suggestion here - I didn't have time to review your problem in
depth - but it appears maybe you need to change the second equality in your
nested SELECT to '<'?

I had a (possibly) similar ranking problem - here was the snippet suggested
to me (see my post "newbie to crosstabs" on 1/14/04: 11:39am).
SELECT Author, Title,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Author = T.Author AND T1.Title < T.Title) AS Rank
FROM YourTable T
ORDER BY Author, Title

Hi -
I used the prior (Jan 13 and 14) 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

.

 

[Ken Snell]

The problem is that we don't understand your table's data and what the query
is returning without the ranking field. I made a guess about what is
contained in the fields in the query.

Let's back up and let you tell us about the data that are in the tables and
what they mean. Then tell us in words what you're trying to display.

--
Ken Snell
<MS ACCESS MVP>

I tried the change to < and all RANK comes out "0". COUNT
+1 makes all RANK "2". In short, I need to rank the
store's sales for the day in descending order. The Table
contains SaleDate,StoreNumber and SalesfortheDay. I just
need to be able to use the Rank # in other reports.I feel
like it can be done, just don't know how!
Thanks so much.

-----Original Message-----
Just a quick suggestion here - I didn't have time to review your problem in
depth - but it appears maybe you need to change the second equality in your
nested SELECT to '<'?

I had a (possibly) similar ranking problem - here was the snippet suggested
to me (see my post "newbie to crosstabs" on 1/14/04: 11:39am).
SELECT Author, Title,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Author = T.Author AND T1.Title < T.Title) AS Rank
FROM YourTable T
ORDER BY Author, Title

Hi -
I used the prior (Jan 13 and 14) 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

.

 

[Guest]

OK, Here we go (and sorry about the other post in the
wrong place; I wasn't sure whether you'd see it in the
original thread or not).

Data: Simple Table that contains:
SalesDate, StoreNumber, YTDSales
(There are also fields for the day's sales, and LastYear's
date and sales).

Need: Ranking of Store, descending order, based on YTD
Sales. The Rank# needs to be used in another report/query.

Reason: The Merchant for Dept #4 wants to see HIS
department in EACH store, in Ranked order. (This is easy;
sorting by YTD Sales for the Dept - from another table).
He ALSO wants to see the RANK ORDER of the TOTAL STORE to
see where his department's sales fit related to the
ranking of the stores overall. For instance, if Dept #4
sales for Store #11 are the Highest in the Chain, yet
Store #11 ranks 6th in total store sales (all YTD), he
wants to figure out what he's doing in Store #11 to
have "Beat the Overall Store" for his department.
Conversely, if Store #3 is Ranked #1 in Sales, but his
Department #4 is #12 in Sales for his Department, he wants
to look into why he isn't performing as well as the store
overall.

To do this, I have a report that shows:
For Paul, Dept 4, SalesDate
YTD Sales (for the deptin the store) in Decending Order -
It shows StoreNumber and $$.
He wants ANOTHER COLUMN to the Right that is Store Rank #.
StoreNumber YTDSales$ StoreRank
4 $27,665 1
6 $22,100 12
1 $19,665 3
10 $12,550 15

Where Store Rank is the number I'm trying to get by
somehow ranking the YTDSales for the store.

Is this clear now? I do so appreciate the help.
Sara

-----Original Message-----
The problem is that we don't understand your table's data and what the query
is returning without the ranking field. I made a guess about what is
contained in the fields in the query.

Let's back up and let you tell us about the data that are in the tables and
what they mean. Then tell us in words what you're trying to display.

--
Ken Snell
<MS ACCESS MVP>

I tried the change to < and all RANK comes out "0". COUNT
+1 makes all RANK "2". In short, I need to rank the
store's sales for the day in descending order. The Table
contains SaleDate,StoreNumber and SalesfortheDay. I just
need to be able to use the Rank # in other reports.I feel
like it can be done, just don't know how!
Thanks so much.

-----Original Message-----
Just a quick suggestion here - I didn't have time to review your problem in
depth - but it appears maybe you need to change the second equality in your
nested SELECT to '<'?

I had a (possibly) similar ranking problem - here was

the
snippet suggested

to me (see my post "newbie to crosstabs" on 1/14/04: 11:39am).
SELECT Author, Title,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Author = T.Author AND T1.Title <

T.Title)
AS Rank

FROM YourTable T
ORDER BY Author, Title

Hi -
I used the prior (Jan 13 and 14) 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



.

.

 

[Ken Snell]

I'm still a bit fuzzy, but we'll see if I can get you closer. One thing: are
you actually storing the YTDSales in a field, instead of storing individual
sales amounts and then using a query to add them up to give you a YTD total?
I don't see how you have a SalesDate and a YTDSales in the same record,
unless you're constantly recalculating the YTDSales value for each new
record?

Anyway, your ranking expression (calculated field in the query) should be
something like this (the expression will wrap in your newsreader, but it's
all one line):

StoreRank: (SELECT Count(*) FROM TableName AS T WHERE T.YTDSales >=
TableName.YTDSales)

This calculated field (change the name of TableName to the actual name of
the table) can be added as another field in your query.

Now note that this expression is not using the SalesDate value, although
your initial post does include it. If you need to compare exact SalesDate
values, then the above expression would need to have that added. HOWEVER, if
your data do not have the same SalesDate value for ALL stores, then you're
not going to get a good ranking (likely, this is where you're getting the
result of 1 for everything). This is one drawback to what I've pointed out
above for your table/record structure. However, if you were just recording
sales for each salesdate, and then used your query to sum up the individual
daily sales, we'd be better off.

Can you comment on the questions/items that I've noted?
--
Ken Snell
<MS ACCESS MVP>

OK, Here we go (and sorry about the other post in the
wrong place; I wasn't sure whether you'd see it in the
original thread or not).

Data: Simple Table that contains:
SalesDate, StoreNumber, YTDSales
(There are also fields for the day's sales, and LastYear's
date and sales).

Need: Ranking of Store, descending order, based on YTD
Sales. The Rank# needs to be used in another report/query.

Reason: The Merchant for Dept #4 wants to see HIS
department in EACH store, in Ranked order. (This is easy;
sorting by YTD Sales for the Dept - from another table).
He ALSO wants to see the RANK ORDER of the TOTAL STORE to
see where his department's sales fit related to the
ranking of the stores overall. For instance, if Dept #4
sales for Store #11 are the Highest in the Chain, yet
Store #11 ranks 6th in total store sales (all YTD), he
wants to figure out what he's doing in Store #11 to
have "Beat the Overall Store" for his department.
Conversely, if Store #3 is Ranked #1 in Sales, but his
Department #4 is #12 in Sales for his Department, he wants
to look into why he isn't performing as well as the store
overall.

To do this, I have a report that shows:
For Paul, Dept 4, SalesDate
YTD Sales (for the deptin the store) in Decending Order -
It shows StoreNumber and $$.
He wants ANOTHER COLUMN to the Right that is Store Rank #.
StoreNumber YTDSales$ StoreRank
4 $27,665 1
6 $22,100 12
1 $19,665 3
10 $12,550 15

Where Store Rank is the number I'm trying to get by
somehow ranking the YTDSales for the store.

Is this clear now? I do so appreciate the help.
Sara

-----Original Message-----
The problem is that we don't understand your table's data and what the query
is returning without the ranking field. I made a guess about what is
contained in the fields in the query.

Let's back up and let you tell us about the data that are in the tables and
what they mean. Then tell us in words what you're trying to display.

--
Ken Snell
<MS ACCESS MVP>

I tried the change to < and all RANK comes out "0". COUNT
+1 makes all RANK "2". In short, I need to rank the
store's sales for the day in descending order. The Table
contains SaleDate,StoreNumber and SalesfortheDay. I just
need to be able to use the Rank # in other reports.I feel
like it can be done, just don't know how!
Thanks so much.
-----Original Message-----
Just a quick suggestion here - I didn't have time to
review your problem in
depth - but it appears maybe you need to change the
second equality in your
nested SELECT to '<'?

I had a (possibly) similar ranking problem - here was the
snippet suggested
to me (see my post "newbie to crosstabs" on 1/14/04:
11:39am).
SELECT Author, Title,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Author = T.Author AND T1.Title < T.Title)
AS Rank
FROM YourTable T
ORDER BY Author, Title

Hi -
I used the prior (Jan 13 and 14) 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



.

.

 

[Sara]

Ken -
THANK YOU!! With your code and some creative thinking, I
have what I need. Here's how:

I entered your formula and I got the ranking, but the #s
are 288,305,416,...2175,2352 rather than 1,2,3...14,15.
SO, I changed things a bit...I first run a Make Table
query that pulls just the 15 records for the sale date
that I need. (SaleDate, StoreNum and YTDSales). Then I
use your code (THANK YOU AGAIN) to RANK that table - and
voila! The # is need is there. Now, I'll go put it all
in the report. I never could have done this without you.

Now, on the data, YES, we have one record per store per
day with all the totals (it's even worse...it has Week to
Date and Month to Date as well!). We receive the data
from an outside source, and dump it into Access, then use
it. This is an unusual company, where PCs are brand new,
and talent quite limited, so I have tried to keep
everything as simple as possible, so others can (maybe)
learn. Just a note of background: When I came here full
time 2 years ago, they still typed invoices on a
typewriter! They had an archaic accounting system that
could not be modified - at all - and reports were printed
on 4-ply green bar paper! We've made HUGE progress, but
it's slow and painful. The president and chairman still
don't accept the need for technology, so everything is
done as simply and cheaply as possible.

If I can EVER return the favor, please let me know.

-----Original Message-----
I'm still a bit fuzzy, but we'll see if I can get you closer. One thing: are
you actually storing the YTDSales in a field, instead of storing individual
sales amounts and then using a query to add them up to give you a YTD total?
I don't see how you have a SalesDate and a YTDSales in the same record,
unless you're constantly recalculating the YTDSales value for each new
record?

Anyway, your ranking expression (calculated field in the query) should be
something like this (the expression will wrap in your newsreader, but it's
all one line):

StoreRank: (SELECT Count(*) FROM TableName AS T WHERE T.YTDSales >=
TableName.YTDSales)

This calculated field (change the name of TableName to the actual name of
the table) can be added as another field in your query.

Now note that this expression is not using the SalesDate value, although
your initial post does include it. If you need to compare exact SalesDate
values, then the above expression would need to have that added. HOWEVER, if
your data do not have the same SalesDate value for ALL stores, then you're
not going to get a good ranking (likely, this is where you're getting the
result of 1 for everything). This is one drawback to what I've pointed out
above for your table/record structure. However, if you were just recording
sales for each salesdate, and then used your query to sum up the individual
daily sales, we'd be better off.

Can you comment on the questions/items that I've noted?
--
Ken Snell
<MS ACCESS MVP>

OK, Here we go (and sorry about the other post in the
wrong place; I wasn't sure whether you'd see it in the
original thread or not).

Data: Simple Table that contains:
SalesDate, StoreNumber, YTDSales
(There are also fields for the day's sales, and LastYear's
date and sales).

Need: Ranking of Store, descending order, based on YTD
Sales. The Rank# needs to be used in another report/query.

Reason: The Merchant for Dept #4 wants to see HIS
department in EACH store, in Ranked order. (This is easy;
sorting by YTD Sales for the Dept - from another table).
He ALSO wants to see the RANK ORDER of the TOTAL STORE to
see where his department's sales fit related to the
ranking of the stores overall. For instance, if Dept #4
sales for Store #11 are the Highest in the Chain, yet
Store #11 ranks 6th in total store sales (all YTD), he
wants to figure out what he's doing in Store #11 to
have "Beat the Overall Store" for his department.
Conversely, if Store #3 is Ranked #1 in Sales, but his
Department #4 is #12 in Sales for his Department, he wants
to look into why he isn't performing as well as the store
overall.

To do this, I have a report that shows:
For Paul, Dept 4, SalesDate
YTD Sales (for the deptin the store) in Decending Order -
It shows StoreNumber and $$.
He wants ANOTHER COLUMN to the Right that is Store Rank #.
StoreNumber YTDSales$ StoreRank
4 $27,665 1
6 $22,100 12
1 $19,665 3
10 $12,550 15

Where Store Rank is the number I'm trying to get by
somehow ranking the YTDSales for the store.

Is this clear now? I do so appreciate the help.
Sara

-----Original Message-----
The problem is that we don't understand your table's

data
and what the query

is returning without the ranking field. I made a guess about what is
contained in the fields in the query.

Let's back up and let you tell us about the data that

are
in the tables and

what they mean. Then tell us in words what you're

trying
to display.

--
Ken Snell
<MS ACCESS MVP>

I tried the change to < and all RANK comes out "0". COUNT
+1 makes all RANK "2". In short, I need to rank the
store's sales for the day in descending order. The Table
contains SaleDate,StoreNumber and SalesfortheDay. I just
need to be able to use the Rank # in other reports.I feel
like it can be done, just don't know how!
Thanks so much.
-----Original Message-----
Just a quick suggestion here - I didn't have time to
review your problem in
depth - but it appears maybe you need to change the
second equality in your
nested SELECT to '<'?

I had a (possibly) similar ranking problem - here

was
the

snippet suggested
to me (see my post "newbie to crosstabs" on 1/14/04:
11:39am).
SELECT Author, Title,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Author = T.Author AND T1.Title < T.Title)
AS Rank
FROM YourTable T
ORDER BY Author, Title

Hi -
I used the prior (Jan 13 and 14) 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

 

[Ken Snell]

Ah, the fun of nonnormalized databases.......

good luck!
--
Ken Snell

With your code and some creative thinking, I
have what I need. Here's how:

I entered your formula and I got the ranking, but the #s
are 288,305,416,...2175,2352 rather than 1,2,3...14,15.
SO, I changed things a bit...I first run a Make Table
query that pulls just the 15 records for the sale date
that I need. (SaleDate, StoreNum and YTDSales). Then I
use your code (THANK YOU AGAIN) to RANK that table - and
voila! The # is need is there. Now, I'll go put it all
in the report. I never could have done this without you.

Now, on the data, YES, we have one record per store per
day with all the totals (it's even worse...it has Week to
Date and Month to Date as well!). We receive the data
from an outside source, and dump it into Access, then use
it. This is an unusual company, where PCs are brand new,
and talent quite limited, so I have tried to keep
everything as simple as possible, so others can (maybe)
learn. Just a note of background: When I came here full
time 2 years ago, they still typed invoices on a
typewriter! They had an archaic accounting system that
could not be modified - at all - and reports were printed
on 4-ply green bar paper! We've made HUGE progress, but
it's slow and painful. The president and chairman still
don't accept the need for technology, so everything is
done as simply and cheaply as possible.

If I can EVER return the favor, please let me know.

< snip >