Simple SQL COUNT query in MS Access

Question

I need to do it in MS Access. I have two tables: A {id, name, FK to B} B {id, name, limit}

Now I need to create query which would give me Bs that have less A than its limit:

SELECT * FROM B WHERE B.limit <
   (SELECT COUNT(A.id) FROM A WHERE A.[FK to B] = B.id)

I have no idea how can I make query simple as this in MS Access.

sgeddes · Accepted Answer · 2013-06-04 16:51:38Z

2

Maybe you can do this with an OUTER JOIN and subquery instead:

SELECT B.*
FROM B 
    LEFT JOIN (
        SELECT Count(*) cnt, BID
        FROM A
        GROUP BY BID
    ) A ON B.Id = A.BID
WHERE B.limit < NZ(A.cnt,0)

answered Jun 4, 2013 at 16:51

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Simple SQL COUNT query in MS Access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related