adding a field in a make table query

[pat67]

Hello. I have a make table query that shows players ranked by their
winning percentage. What I want to do is when making the table add a
field to the front of table that is called "Rank" and is autonumbered
so that the highest percentage is 1 and the next 2 and so on. What I
am doing now is adding the field manually after I create the table. I
want to do it at creation since I have other queries I run off of this
table that have to wait until the "rank" field is there. Can someone
explain to me how to do this? Thanks

 

[pat67]

Hello. I have a make table query that shows players ranked by their
winning percentage. What I want to do is when making the table add a
field to the front of table that is called "Rank" and is autonumbered
so that the highest percentage is 1 and the next 2 and so on. What I
am doing now is adding the field manually after I create the table. I
want to do it at creation since I have other queries I run off of this
table that have to wait until the "rank" field is there. Can someone
explain to me how to do this? Thanks

One thing I will add is that I tried using a delete query and then an
append query but the problem with that is because the "Rank" field is
autonumbered, it starts with the next number. In other words we have
64 players so when i use delete and append, the first player now has
65 instead of 1. I need it to show 1 thru 64.

 

[John W. Vinson]

Hello. I have a make table query that shows players ranked by their
winning percentage. What I want to do is when making the table add a
field to the front of table that is called "Rank" and is autonumbered
so that the highest percentage is 1 and the next 2 and so on. What I
am doing now is adding the field manually after I create the table. I
want to do it at creation since I have other queries I run off of this
table that have to wait until the "rank" field is there. Can someone
explain to me how to do this? Thanks

Why do you need a MakeTable query *at all*??? You can instead create a Select
query with a Rank field and extract the desired data at any time:

SELECT <other fields>, (SELECT Count(*) FROM tablename AS X WHERE X.WinPct <=
tablename.WinPct AND <criteria>) AS RANK WHERE <critirea>;

This query can itself be used as the basis for other queries or you can just
include the subquery wherever it's needed.

An Autonumber will not serve your turn, as you have seen. That's not its
purpose; the only role for an Autonumber is as a meaningless unique ID.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[pat67]

Why do you need a MakeTable query *at all*??? You can instead create a Select
query with a Rank field and extract the desired data at any time:

SELECT <other fields>, (SELECT Count(*) FROM tablename AS X WHERE X.WinPct <=
tablename.WinPct AND <criteria>) AS RANK WHERE <critirea>;

This query can itself be used as the basis for other queries or you can just
include the subquery wherever it's needed.

An Autonumber will not serve your turn, as you have seen. That's not its
purpose; the only role for an Autonumber is as a meaningless unique ID.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

I want to number the Rank field 1-64. How can I do that?

 

[pat67]

Use a calculated field something like:

Rank: DCount("*", "base table", "winpct<=" & winpct")

Replace the table and field names with your real names.

Ok. I have it partially figured out. this is what I have

SELECT t1.PlayerName, t1.Team, t1.Active, t1.GP, t1.Won, t1.Lost,
t1.Pct, (SELECT DISTINCT COUNT(Pct)
FROM tblPlayerStats_2 as t2
WHERE t2.Pct > t1.Pct)+1 AS Rank
FROM tblPlayerStats_2 AS t1
ORDER BY t1.Pct DESC, t1.Won DESC;

That gives each player a rank based on pct. What i now need to do is
for those who are tied with pct, have won as the tiebreaker. So player
a has 20 wins and 10 losses for a .667 pct but player b has 24 wins
and 12 losses also for .667 i want player be ranked ahead. right now
they both would be tied. So if the were 10th, they both would show 10
and the next player would be 12. want i want is player b as 10 and
then player a as 11. i tried putting an AND in the where but it didn't
work right. any ideas?

 

[pat67]

Ok. I have it partially figured out. this is what I have

SELECT t1.PlayerName, t1.Team, t1.Active, t1.GP, t1.Won, t1.Lost,
t1.Pct, (SELECT DISTINCT COUNT(Pct)
FROM tblPlayerStats_2 as t2
WHERE t2.Pct > t1.Pct)+1 AS Rank
FROM tblPlayerStats_2 AS t1
ORDER BY t1.Pct DESC, t1.Won DESC;

That gives each player a rank based on pct. What i now need to do is
for those who are tied with pct, have won as the tiebreaker. So player
a has 20 wins and 10 losses for a .667 pct but player b has 24 wins
and 12 losses also for .667 i want player be ranked ahead. right now
they both would be tied. So if the were 10th, they both would show 10
and the next player would be 12. want i want is player b as 10 and
then player a as 11. i tried putting an AND in the where but it didn't
work right. any ideas?

OK, you're up to using a subquery instead of DCount, good
(except in an Update query).  Note that Count(*) is faster
than Count(Pct)

I think you need this to get the player with the highest pct
to be ranked 1

SELECT t1.PlayerName, t1.Team, t1.Active, t1.GP,
                t1.Won, t1.Lost, t1.Pct,
              (SELECT COUNT(*)
               FROM tblPlayerStats_2 as t2
               WHERE t2.Pct < t1.Pct
                   OR (t2.Pct = t1.Pct AND t2.Won <= t1.Won
              )  AS Rank
FROM tblPlayerStats_2 AS t1
ORDER BY t1.Pct, t1.Won

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

thanks. that works perfectly.