Access SQL count() query

Question

This task should be done in Access with 1 query. There are 3 tables:

address {id, address, city, postalcode, country, supplierid}
supplier {supplierID, supplierName, nameid}
contactname {nameid, firstname, lastname, phone}

I need to create query, that results with the following table:

{country, supplierNumber, supplierName, firstname, lastname, phone}

Where supplierNumber is number of companies in specific country.

For countries with only 1 company country, supplierName, firstname, lastname, phone fields must be filled; with 2 or more companies country, supplierNumber fields must be filled.

So I had straightforward idea to use:

SELECT COUNT() ... 
FROM ... INNER JOIN ... ON ...
HAVING COUNT() ...
GROUP BY ...
UNION
...

But I got stucked with a lot of problems. GROUP BY requires all fields that SELECT takes because of COUNT(); UNION requires equal number of fields in tables, so maybe the second table should be complemented with NULL fields or something. So I have no idea, which way this task should be implemented. Please, help me.

I wrote I tried to create query with structure I described above, but all in vain. — gillz1
– gillz1, Commented Jul 27, 2018 at 17:26
Why downvote? Firstly, It's no use publishing my attempt, because it's absolutely non-working, secondly, I precisely described the structure and the mistakes I had during my attempt. Lastly, I unsuccessfully searched the web thouroughly for similar examples and doesn't even know the principle this task should be implemented. — gillz1
– gillz1, Commented Jul 27, 2018 at 18:36

Wolfgang Kais · Accepted Answer · 2018-07-28 17:13:28Z

1

Assuming that address.supplierid is always filled with a valid supplierid and supplier.nameid is always filled with a valid nameid, you can achieve the desired result using a UNION query like the following. The first part selects the country and supplier/contact for countries with only one supplier, the second part selects all contries with more that one supplier (countries with more than one address):

SELECT a.country, 1 AS supplierNumber, s.supplierName, c.firstname, c.lastname, c.phone
FROM (address AS a INNER JOIN supplier AS s ON a.supplierid = s.supplierid) 
  INNER JOIN contactname AS c ON s.nameid = c.nameid
WHERE ((a.country) In (SELECT country FROM address GROUP BY country HAVING Count(*)=1))
UNION
SELECT a.country, Count(*), Null, Null, Null, Null
FROM address AS a GROUP BY country HAVING Count(*)>1

edited Jul 28, 2018 at 17:13

answered Jul 28, 2018 at 15:00

Wolfgang Kais

4,1002 gold badges13 silver badges19 bronze badges

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

1 Comment

gillz1 Over a year ago

WolfgangK, works perfectly, just amazing... I am so much grateful to you! So many implicit features in sql to use!

Parfait · Accepted Answer · 2018-07-27 23:19:44Z

0

Consider joining your unit level tables with aggregate level query:

SELECT a.country, agg.supplierNumber, s.supplierName, c.firstname, c.lastname, c.phone
FROM ((supplier s
INNER JOIN contact c ON c.nameid = s.nameid)
INNER JOIN address a ON s.supplierid = a.supplierid)
LEFT JOIN 
   (SELECT sub_a.country, COUNT(*) AS supplierNumber
    FROM address sub_a
    INNER JOIN supplier sub_s ON sub_a.supplierid = sub_s.supplier_id
    GROUP BY sub_a.country) AS agg
ON agg.country = a.country

answered Jul 27, 2018 at 23:19

Parfait

108k19 gold badges103 silver badges138 bronze badges

1 Comment

gillz1 Over a year ago

Wow, very smart idea and it seems to work well, but there are all fields filled up in the resulting table for each country!

Collectives™ on Stack Overflow

Access SQL count() query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related