Re: Continuous Form and Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Oh, I see. That makes more sense than the way I was reading it. Unfortunately I can't get this to work. I set my query up to look like this in sql:

SELECT DISTINCTROW tbl_main.Home_Name, Sum(tbl_main.Home_Points) AS [Sum Of Home_Points], (SELECT Count(*) FROM tbl_main AS MyTable1 WHERE [home_points] <[tbl_main].[home_points]-1) AS Rank
FROM tbl_main
GROUP BY tbl_main.Home_Name;

I've tried a couple of different things but not sure what exactly it is that I am missing that is keeping this from working. Any ideas? Thanks again for the help Barry.

Wes


Barry Gilbert wrote:
Not exactly. This isn't procedural code. This statement is evaluated for each record of your table. As each record is evaluated, it looks at all records in the same table and finds the number of records where the UnitsSold value is less than the UnitsSold value in the current record.

Barry

"Wes H." wrote:

Thanks for the help Barry. So, let me make sure I understand. If I am reading this right, which I hope I am, then it counts the records in the table and lets say it equals 35. Then it starts at the end and starts numbering backwards, but it seems like each column would have the same number, or am I reading this wrong? Thanks again for the help Barry.

Wes

Barry Gilbert wrote:
Let's assume the column you want to rank by is called UnitsSold. In your query, add a column called Rank. It will look like this:
Rank: (SELECT Count(*) FROM tblMyTable AS MyTable1 WHERE [UnitsSold] < [MyTable].[UnitsSold]-1)

HTH,
Barry

"Wes H." wrote:

I realize that. But once it is sorted and being displayed on the form I want to be able to look and see that dallas is ranked 3rd, or denver is ranked 17th, etc. without having to count line by line each time i want to figure out what rank a team is in. That's the reason for the numbers, not for sorting.

Barry Gilbert wrote:
I'm not sure why you'd need the extra column. You could simply set your query to sort by the value in descending order.

HTH,
Barry

"Wes H." wrote:

I have a query that is summing up numbers. This query is then being accessed in a continuous form so that it looks something like this:

Denver 496
Dallas 451
St. Louis 399
Arizona 251

Since this info is constantly changing, what I am looking for is a way of having each of these items to be numbered respectively from top to bottom, so that it looks something like this:

1 Denver 496
2 Dallas 451
3 St. Louis 399
4 Arizona 251

And that way when it changes the respectable ranking number is also changed to be something like this:

1 St. Louis 821
2 Arizona 733
3 Denver 601
4 Dallas 455

Not sure if this can even be done and if so then can it be done via the query or the form or vba, but any info on how I can do this would be greatly appreciated. Thanks for the help in advance.

.

Relevant Pages

  • Re: optimizer behaviour changed
    ... Given you have also done an order by which is not in the select list - the server must build an index to reorder the data which he is reading directly from the index. ... QUERY PLAN FOR STATEMENT 1. ... Using I/O Size 2 Kbytes for index leaf pages. ... With LRU Buffer Replacement Strategy for index leaf pages. ...
    (comp.databases.sybase)
  • Re: Programming in standard c
    ... may grow larger between the query and the reading. ... That wider programming environment can provide guarantees ... and learned my craft in the days when memory was ...
    (comp.lang.c)
  • RE: Combo Box
    ... The query isn't updateable. ... "Barry Gilbert" wrote: ... "PHisaw" wrote: ... need to make sure that the combobox control's Locked property is set to False. ...
    (microsoft.public.access.forms)
  • Re: CRIT: Eyes Query
    ... has a grudge against mothers who do illegal gene-splicing on their own ... readers are reading has a tendency to really grab my attention. ... published writers posted successful query letters, ... favour when I see a weakness and ignore it - because I want to see you ...
    (rec.arts.sf.composition)
  • Re: What happens when data in control is updated
    ... and been reading about 'recordsets'.... ... predecessor query which used yet another query (the one i coined ... could assign/pass it in to the query when i wrote it as expressed in my SQL ... >> control whilst the user is contuing to use/view the information on ...
    (microsoft.public.access.formscoding)