Data Definition queries

[Tlewman]

I am trying to alter a table with a data definition query automatically adds a column that ranks another column in the table (say sales) from highest to lowest. I am able to get the query to add the column named rank, but am having trouble getting it to automatically populate the column with the rankings. This info will feed a report that I will need to update every month. Any thoughts ???

 

[Russell Hauf]

Here is an example that should help. Below is a sample of my table:

<table BORDER="1" BGCOLOR="#ffffff" CELLSPACING="0"><tbody><tr><td>Customer</td><td>Contract</td></tr><tr VALIGN="TOP"><td>A</td><td>c13514</td></tr><tr VALIGN="TOP"><td>A</td><td>c15097</td></tr><tr VALIGN="TOP"><td>A</td><td>c21201</td></tr><tr VALIGN="TOP"><td>A</td><td>c27169</td></tr><tr VALIGN="TOP"><td>A</td><td>c31652</td></tr><tr VALIGN="TOP"><td>A</td><td>c34277</td></tr><tr VALIGN="TOP"><td>A</td><td>c46318</td></tr><tr VALIGN="TOP"><td>A</td><td>c46421</td></tr><tr VALIGN="TOP"><td>A</td><td>c46517</td></tr><tr VALIGN="TOP"><td>B</td><td>c12399</td></tr><tr VALIGN="TOP"><td>B</td><td>c1495</td></tr></tbody> </table>

My table name is tblCustomers. Create a new query, add the 2 fields, and sort ascending by the first field and descending by the second (your "sales" field - this is because you want to rank from highest to lowest). Then right-click on the table in your query and select Properties. Under Alias, put X (or something else to your liking). Then in a new column in your query put something like this:

Rank: (SELECT Count(*) FROM tblCustomers WHERE tblCustomers.Customer = X.Customer AND tblCustomers.Contract > X.Contract) + 1

Here is my entire query:

SELECT X.Customer, X.Contract, (SELECT Count(*) FROM tblCustomers WHERE tblCustomers.Customer = X.Customer AND tblCustomers.Contract > X.Contract)+1 AS Rank
FROM tblCustomers AS X
ORDER BY X.Customer, X.Contract DESC;

And here are my query results:

<table BORDER="1" BGCOLOR="#ffffff" CELLSPACING="0"><tbody><tr VALIGN="TOP"><td>Customer</td><td>Contract</td><td>Rank</td></tr><tr VALIGN="TOP"><td>A</td><td>c46517</td><td ALIGN="RIGHT">1</td></tr><tr VALIGN="TOP"><td>A</td><td>c46421</td><td ALIGN="RIGHT">2</td></tr><tr VALIGN="TOP"><td>A</td><td>c46318</td><td ALIGN="RIGHT">3</td></tr><tr VALIGN="TOP"><td>A</td><td>c34277</td><td ALIGN="RIGHT">4</td></tr><tr VALIGN="TOP"><td>A</td><td>c31652</td><td ALIGN="RIGHT">5</td></tr><tr VALIGN="TOP"><td>A</td><td>c27169</td><td ALIGN="RIGHT">6</td></tr><tr VALIGN="TOP"><td>A</td><td>c21201</td><td ALIGN="RIGHT">7</td></tr><tr VALIGN="TOP"><td>A</td><td>c15097</td><td ALIGN="RIGHT">8</td></tr><tr VALIGN="TOP"><td>A</td><td>c13514</td><td ALIGN="RIGHT">9</td></tr><tr VALIGN="TOP"><td>B</td><td>c50947</td><td ALIGN="RIGHT">1</td></tr><tr VALIGN="TOP"><td>B</td><td>c2485</td><td ALIGN="RIGHT">2</td></tr><tr VALIGN="TOP"><td>B</td><td>c21751</td><td ALIGN="RIGHT">3</td></tr><tr VALIGN="TOP"><td>B</td><td>c1495</td><td ALIGN="RIGHT">4</td></tr><tr VALIGN="TOP"><td>B</td><td>c12399</td><td ALIGN="RIGHT">5</td></tr></tbody> </table>

HTH,

Russell

 

[Tlewman]

Perfect....
Now another question. I have a couple of instances where I may have five of the same customer but only three of those five have sales for the week. The other two customers have blank cells under the sales column so when the rank is added, it ranks each of them as 1 instead of 4 or 5.

So my info looks like

cust1 500 1
cust1 400 2
cust1 300 3
cust1 1
cust1 1

How do I fix this?

 

[Russell Hauf]

Do you really want them included in the ranking? You could exclude them by typing "is not null" in the criteria line for SALES.

If you really want them in your ranking, let me know and I'll see what I can do.

-rh

 

[Tlewman]

I have to have them included because I have another column in this report that shows year to date sales. It is ranked by the current weeks sales but I have to show YTD info as well.