MySQL – How to Create an Access Query to Find # Symbol in a Field

The only way to get complete control over the query for ODBC is to create them as pass-through queries.

In that mode, Access will not touch them and the SQL will be passed verbatim to the server (so your queries will need to be written in that server's particular SQL syntax or you'll get errors).

In ODBC mode, Access will decompose queries that are bound to controls into batches so it never returns more results than it need to display (that is what you see: it will decompose queries based on the primary key to return specific records only).

So you have to be careful of what you wish: if you optimise your queries using pass-through, you will get less calls, but all the data will be returned.

On the other hand, if you leave Access do the querying for you, it may be chattier, but the net performance may be better since less data may be returned more quickly.

You need to test both and choose what works best depending on the exact query and how it is used.

If I get it right something like:

select c.code_id
from codes c
join used u
    on c.code_id = u.code_id
group by c.code_id, c.amount
having count(1) < c.amount;

should do (Not sure why MySQL requires c.amount in the group by clause for it to be used in having)

If you want to show the remaining amount:

select c.code_id, c.amount - count(1) as remains
from codes c
join used u
    on c.code_id = u.code_id
group by c.code_id, c.amount
having count(1) < c.amount

I assumed here that amount is functionally dependent of code_id. Another option is to add a "fake" aggregate to c.amount:

select c.code_id, max(c.amount) - count(1) as remains
from codes c
join used u
    on c.code_id = u.code_id
group by c.code_id
having count(1) < max(c.amount);

There is only one unique amount per code_id so the purpose of max is to "legalize" the group by.

SQL92 demanded that all columns in the select beside aggregates where included in the group by clause. SQL99 loosened this restriction and only requires that all columns in the select clause is functionally dependent of the group by.

I haven't tested it my self, but I hear that the latest version of MySQL (or was it MariaDB?) implements it this way. I've also heard that recent versions of postgreSQL does it that way.