Continuous Form and Query

  • Thread starter Thread starter Wes H.
  • Start date Start date
W

Wes H.

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.
 
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
 
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.
 
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
 
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 said:
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. said:
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.
Click to expand...
Click to expand...
 
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. said:
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 said:
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. said:
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

:

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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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. said:
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 said:
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

:

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

:

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.
Click to expand...
Click to expand...
Click to expand...
 
I'm guessing it's not working because you're using grouping to get the Sum to
work and you have no aggregate on the Rank column. I didn't know you needed
to do summing. I would suggest taking the Rank out of this query. Create a
new query based on this query and add the rank column there.

Barry

Wes H. said:
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 said:
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. said:
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

:

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

:

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.
Click to expand...
Click to expand...
Click to expand...
 
Barry, great idea...didn't even think of that. I had to tweak your
suggestion just a bit because since i have the query listing the summed
column descending it is giving it a rank of the count minus 1 and the
last item in the group is zero. So what I did was to write it something
like this:

Rank: (SELECT Count(*)*-1+33 FROM tbl_mainquery AS MyTable1 WHERE
[sumofhome_points] < [tbl_mainquery].[sumofhome_points]+1)

I have 32 items in the query so I multiplied the count of 32 * -1 to get
-32 then added 33 to give me 1 for the top record. Then each record
after that has another 1 added to it to give me 32 for the last record.
Thanks for all the help Barry.

Wes

Barry said:
I'm guessing it's not working because you're using grouping to get the Sum to
work and you have no aggregate on the Rank column. I didn't know you needed
to do summing. I would suggest taking the Rank out of this query. Create a
new query based on this query and add the rank column there.

Barry

Wes H. said:
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 said:
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

:

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

:

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

:

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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top