Help with GROUP BY and COUNT

[Nick HK]

I have a table of SequenceCodes and a table of Colours, both in a
many-to-many relation in the table SequenceCodes_Colours
</sample data
(ID's used in the table, but value displyed for clarity)

ID SequenceCodeID ColourID
3 00 Clear
4 06 Blue
5 06 Green
6 0B Blue
7 0B Green
8 0B Red

/sample data>

Trying to see if a particular colour combination is already used, returning
that SequenceCodeID, excluding combinations that also have more colours than
those requested.

<SQL

SELECT TheseColours.TheseCodes
FROM

((SELECT SequenceCodes.Code AS AllCodes, COUNT(SequenceCodes.Code) as
TotalAllColours
FROM SequenceCodes INNER JOIN (Colours INNER JOIN SequenceCodes_Colours ON
Colours.ID = SequenceCodes_Colours.ColourID) ON SequenceCodes.ID =
SequenceCodes_Colours.SequenceCodeID
GROUP BY SequenceCodes.Code)
AS AllColours),

((SELECT SequenceCodes.Code AS TheseCodes, COUNT(SequenceCodes.Code) as
TotalTheseColours
FROM SequenceCodes INNER JOIN (Colours INNER JOIN SequenceCodes_Colours ON
Colours.ID = SequenceCodes_Colours.ColourID) ON SequenceCodes.ID =
SequenceCodes_Colours.SequenceCodeID
WHERE ((Colours.EName="Blue") Or (Colours.EName="Green"))
GROUP BY SequenceCodes.Code)
AS TheseColours)

WHERE TheseColours.TotalTheseColours= AllColours.TotalAllColours

/SQL>

This still returns "06" and "OB", rather than excluding "OB" (where
AllColours.TotalAllColours=3).

I seem to remember seeing an example of this before, but the correct SQL
eludes me.

TIA

NickHK

 

[Nick HK]

Apologises meant to send to microsoft.public.access.queries.

NickHK


| I have a table of SequenceCodes and a table of Colours, both in a
| many-to-many relation in the table SequenceCodes_Colours
| </sample data
| (ID's used in the table, but value displyed for clarity)
|
| ID SequenceCodeID ColourID
| 3 00 Clear
| 4 06 Blue
| 5 06 Green
| 6 0B Blue
| 7 0B Green
| 8 0B Red
|
| /sample data>
|
| Trying to see if a particular colour combination is already used,
returning
| that SequenceCodeID, excluding combinations that also have more colours
than
| those requested.
|
| <SQL
|
| SELECT TheseColours.TheseCodes
| FROM
|
| ((SELECT SequenceCodes.Code AS AllCodes, COUNT(SequenceCodes.Code) as
| TotalAllColours
| FROM SequenceCodes INNER JOIN (Colours INNER JOIN SequenceCodes_Colours ON
| Colours.ID = SequenceCodes_Colours.ColourID) ON SequenceCodes.ID =
| SequenceCodes_Colours.SequenceCodeID
| GROUP BY SequenceCodes.Code)
| AS AllColours),
|
| ((SELECT SequenceCodes.Code AS TheseCodes, COUNT(SequenceCodes.Code) as
| TotalTheseColours
| FROM SequenceCodes INNER JOIN (Colours INNER JOIN SequenceCodes_Colours ON
| Colours.ID = SequenceCodes_Colours.ColourID) ON SequenceCodes.ID =
| SequenceCodes_Colours.SequenceCodeID
| WHERE ((Colours.EName="Blue") Or (Colours.EName="Green"))
| GROUP BY SequenceCodes.Code)
| AS TheseColours)
|
| WHERE TheseColours.TotalTheseColours= AllColours.TotalAllColours
|
| /SQL>
|
| This still returns "06" and "OB", rather than excluding "OB" (where
| AllColours.TotalAllColours=3).
|
| I seem to remember seeing an example of this before, but the correct SQL
| eludes me.
|
| TIA
|
| NickHK
|
|

 

[Tim Ferguson]

Trying to see if a particular colour combination is already used,
returning that SequenceCodeID, excluding combinations that also have
more colours than those requested.

Try googling for SQL DIVIDE

Tim F