Couting several columns with parameter

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

Is there a way to create a query where I can count the
following:

988 where 988 is >.05
110 where 110 is > .03
330 where 330 is > .04

I've tried the following and I get the wrong results.

SELECT Count([2004_April].
[133_40_988/834/836_G_EEF_Upper_Hitch]) AS
[CountOf133_40_988/834/836_G_EEF_Upper_Hitch], Count
([2004_April].
[108_00_988/834/836_G_EEF_Upper_Middle_Hitch]) AS
[CountOf108_00_988/834/836_G_EEF_Upper_Middle_Hitch], Count
([2004_April].
[90_025_988/834/836_G_EEF_Lower_Middle_Hitch]) AS
[CountOf90_025_988/834/836_G_EEF_Lower_Middle_Hitch]
FROM 2004_April;

and I've tried:

SELECT Count([2004_April].
[133_40_988/834/836_G_EEF_Upper_Hitch]) AS
[CountOf133_40_988/834/836_G_EEF_Upper_Hitch], Count
([2004_April].
[108_00_988/834/836_G_EEF_Upper_Middle_Hitch]) AS
[CountOf108_00_988/834/836_G_EEF_Upper_Middle_Hitch]
FROM 2004_April
WHERE ((([2004_April].
[133_40_988/834/836_G_EEF_Upper_Hitch])>0.05)) OR
((([2004_April].
[108_00_988/834/836_G_EEF_Upper_Middle_Hitch])>0.3));


Thank you very much,
Alex
 
Hi,


SELECT x, COUNT(*)

FROM myTable

WHERE (x=988 AND y>0.05) OR
(x=110 AND y>0.03) OR
(x=330 AND y>0.04)

GROUP BY x



If your (x, y) 'points' are in a table:


xy ' table
xg, ylim ' fields, primary key on xg
988 0.05
110 0.03
330 0.04 ' data


then the query is cleaner (without magical constants) and run probably much
faster:


SELECT x, COUNT(*)

FROM myTable INNER JOIN xy
ON myTable.x=xy.xg AND myTable.y > xy.ylim

GROUP BY x




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top