CODEWITHSUNDEEP
sqlms-accesscount
gillz1
gillz1

Reputation: 23

Access SQL count() query

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

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.

Upvotes: 1

Views: 143

Answers (2)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

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

Upvotes: 1

Parfait
Parfait

Reputation: 107767

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

Upvotes: 0

Related Questions