I've spent my whole day trying to come up with a solution for this problem, but I still have no clue:

I have in one table users the following fields:

• id
• name
• city_id
• score

and in another table city two fields:

• id
• users_number

What I have to do is to get the top rows from the users table as specified by the users_number field in city but first, ordering the fetched rows by score. The users quantity is far larger than the value specified by users_number.

My question is:

is there any possibility to select from the table users where criteria is number of rows in table city?

thank you

Here's an answer in T-SQL should that be of any help in figuring it out for Access?

declare @users table
(
    id int primary key clustered
    , name nvarchar(64)
    , city_id int
    , score int
)
declare @city table (
    id int primary key clustered
    , name nvarchar(64)
    , users_number int
)

insert @city
      select 1, 'San Fran', 2
union select 2, 'NY', 5

insert @users
      select 1, 'Steve Fields', 1, 10
union select 2, 'Sarah Felcher', 1, 20
union select 3, 'Nick Yarnton', 2, 12
union select 4, 'Nigel Yardsman', 2, 12
union select 5, 'Nicki Yakatou', 2, 13
union select 6, 'Nicola Yates', 2, 23
union select 7, 'Steph Freeman', 1, 15
union select 8, 'Ned Yount', 2, 18
union select 9, 'Neil Yorke', 2, 1

select *
from @city c
left outer join
(
    select id, name, city_id, score
    , ROW_NUMBER() over (partition by city_id order by score desc) r
    from @users
) u
on c.id = u.city_id
where c.users_number >= u.r
order by c.id, u.score desc

more