SQL problem, help if possible please.

[MMcCarthy]

SELECT LEVERANC.LEV_NA AM, Count(PLANTEN.S OORT) As CountSOORT, Count(LIST_SOOR T.SOORT) As TotalSOORT
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO , (SELECT DISTINCT SOORT FROM PLANTEN) As LIST_SOORT
GROUP BY LEVERANC.LEV_NA AM;


See if this will work ...

If it does give you the count then change it as follows to only return suppliers who can match count.

SELECT LEVERANC.LEV_NA AM, Count(PLANTEN.S OORT) As CountSOORT, Count(LIST_SOOR T.SOORT) As TotalSOORT
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO , (SELECT DISTINCT SOORT FROM PLANTEN) As LIST_SOORT
WHERE Count(PLANTEN.S OORT)=Count(LIS T_SOORT.SOORT)
GROUP BY LEVERANC.LEV_NA AM;

[MMcCarthy]

Or you could try.

SELECT LEVERANC.LEV_NA AM, Count(PLANTEN.S OORT) As CountSOORT
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO , (SELECT DISTINCT SOORT FROM PLANTEN) As LIST_SOORT
WHERE Count(PLANTEN.S OORT)=Count(LIS T_SOORT.SOORT)
GROUP BY LEVERANC.LEV_NA AM;[/QUOTE]

[L1sa]

Nope still no luck =(

I have tried other ways of doing it such as:

SELECT l.LEV_NAAM, Count(p.SOORT) AS SOORT
FROM PLANTEN p, LEVERANC l
GROUP BY l.LEV_NAAM
HAVING SOORT =
(SELECT COUNT(*) FROM PLANTEN)


But this fails to work, grrr must say it is rather annoying, I might just leave it out :P

[MMcCarthy]

What happened when you tried by last one. Did it fail or just give the wrong result?

Don't give up yet there are always (well nearly always) more things to try.

[L1sa]

There is a problem with this line:

WHERE Count(PLANTEN.S OORT)=PLANTEN.S OORT

'Can't have static functions using WHERE'

I also tried replacing the WHERE with HAVING but no luck :(

[MMcCarthy]

There is a problem with this line:

WHERE Count(PLANTEN.S OORT)=PLANTEN.S OORT

'Can't have static functions using WHERE'

I also tried replacing the WHERE with HAVING but no luck :(

That's not what I gave you. Copy and paste the following exactly as it is.

SELECT LEVERANC.LEV_NA AM, Count(PLANTEN.S OORT) As CountSOORT
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO , (SELECT DISTINCT SOORT FROM PLANTEN) As LIST_SOORT
WHERE Count(PLANTEN.S OORT)=Count(LIS T_SOORT.SOORT)
GROUP BY LEVERANC.LEV_NA AM;

[L1sa]

I know, the version you gave me also claims there is a problem with the same line (the same problem) I was just trying different variations of it etc =(

[MMcCarthy]

I know, the version you gave me also claims there is a problem with the same line (the same problem) I was just trying different variations of it etc =(

OK Access doesn't really like nested queries and can through in square brackets where they don't belong so we'll try it like this instead.

qry1
SELECT DISTINCT SOORT FROM PLANTEN;

SELECT LEVERANC.LEV_NA AM, Count(PLANTEN.S OORT) As CountSOORT, Count(qry1.SOOR T)
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO , qry1
WHERE PLANTEN.SOORT=q ry1.SOORT
GROUP BY LEVERANC.LEV_NA AM;

I'm not sure if it will work but let me know and I'll look at something else.

I know there is a way of doing this.

[L1sa]

This is what it brought up:




The arrow is meant to show which field should be in the final result (since this supplier is the only supplier to sell all species of plants) so there must be a way of showing this.

However, I can't simply define this supplier in the sql because more suppliers could be added who also sell every plant species :s

[MMcCarthy]

SELECT LEVERANC.LEV_NA AM, Count(PLANTEN.S OORT) As CountSOORT, Count(qry1.SOOR T)
FROM PLANTEN INNER JOIN (LEVERANC INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO , qry1
WHERE Count(PLANTEN.S OORT)=Count(qry 1.SOORT)
GROUP BY LEVERANC.LEV_NA AM;

Try this one...

[L1sa]

Nope =( (same error as before with the where clause) I will keep on at it, I am sure you have better things to do with your time then attempt to solve this query with me!

I will try different variations and see if it works.

[L1sa]

SELECT DISTINCT LEVERANC.LEV_NA AM, qryCountLEV_NAA M.LEV_CODE, qryCountLEV_NAA M.SPECIES
FROM PLANTEN INNER JOIN ((LEVERANC INNER JOIN qryCountLEV_NAA M ON LEVERANC.LEV_CO DE = qryCountLEV_NAA M.LEV_CODE) INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO
GROUP BY LEVERANC.LEV_NA AM, qryCountLEV_NAA M.LEV_CODE, qryCountLEV_NAA M.SPECIES
HAVING (((qryCountLEV_ NAAM.LEV_CODE)= '013'));

This displays the correct supplier and count of species, however, as you can see from the last line I selected the right supplier by giving the LEV_CODE which will work for now if there are no more suppliers added :s perhaps this last line can be adapted somehow?

[MMcCarthy]

SELECT DISTINCT LEVERANC.LEV_NA AM, qryCountLEV_NAA M.LEV_CODE, qryCountLEV_NAA M.SPECIES
FROM PLANTEN INNER JOIN ((LEVERANC INNER JOIN qryCountLEV_NAA M ON LEVERANC.LEV_CO DE = qryCountLEV_NAA M.LEV_CODE) INNER JOIN OFFERTES ON LEVERANC.LEV_CO DE = OFFERTES.LEV_CO DE) ON PLANTEN.ART_COD E = OFFERTES.ART_CO
GROUP BY LEVERANC.LEV_NA AM, qryCountLEV_NAA M.LEV_CODE, qryCountLEV_NAA M.SPECIES
HAVING (((qryCountLEV_ NAAM.LEV_CODE) IN (SELECT DISTINCT LEV_CODE FROM qyrCountLEV_NAA M HAVING MAX(COUNT(SPECI ES)))));

You might have to play with this a bit as I'm not sure what was in the final version of qryCountLEV_NAA M. If you can't work it out post the full query and I'll try to work it out.

[NeoPa]

If you want a list of all suppliers (Leveranc) and all the species (Soort) they sell, as well as a count of all the plants (Planten) found in each of those species - try :

SELECT LEVERANC.LEV_NAAM, PLANTEN.SOORT, _
    Count(PLANTEN.ART_CODE) As CountPlant
FROM (OFFERTES INNER JOIN PLANTEN ON OFFERTES.ART_CO = PLANTEN.ART_CODE) _
    INNER JOIN LEVERANC ON OFFERTES.LEV_CODE = LEVERANC.LEV_CODE
GROUP BY OFFERTES.LEV_CODE, PLANTEN.SOORT

[NeoPa]

Try this.
It should give you the right selection of records and you can add fields to it when you're comfortable you're getting them.

SELECT LEVERANC.LEV_NAAM,
    Count(PLANTEN.PLANTNAAM) AS PlantCount
FROM (OFFERTES INNER JOIN PLANTEN ON 
    OFFERTES.ART_CO = PLANTEN.ART_CODE)
    INNER JOIN LEVERANC ON 
    OFFERTES.LEV_CODE = LEVERANC.LEV_CODE)
GROUP BY LEVERANC.LEV_NAAM
HAVING PlantCount =
    (SELECT Count(PLANTNAAM)
    FROM (SELECT DISTINCT [PLANTNAAM]
    FROM PLANTEN))

BTW I missed one of your answers earlier which gave me the info I needed.
So, sorry for the delay.