Counting help.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi ,
I have a table with fields:
ID,Field1
1 1
1 1
1
1 3
2 1
2 2
2 1

How can I count field ID=1 have 3 record and ID=2 have 3 records ?
Like: Count to ID Where not empty(field1) as F1 ?
Thank in advange.
MN
 
SELECT ID, Count(Field1) as CountField
FROM Table
GROUP BY ID

Since Count counts fields that have a value (whatever the value is) the
above should work.

In the query grid
-- add in your two fields
-- Select View: Totals
-- Change Group By to Count for Field1
 
Thank for reply,
I tried this query before, but the result is not right. It counts:
ID Field1
1 4
2 3
I want result like this:
ID Field
1 3
2 3
-- All fields are numeric.
Any advise?
MN
 
MN said:
I have a table with fields:
ID,Field1
1 1
1 1
1
1 3
2 1
2 2
2 1

How can I count field ID=1 have 3 record and ID=2 have 3 records ?
Like: Count to ID Where not empty(field1) as F1 ?
Click to expand...


SELECT ID, Count(Field1) As F1
FROM atable
GROUP BY ID
 
I would guess that the fields are not numeric, but are text and contain
number characters or contain a zero-length string.

Try the following
SELECT ID, Abs(Sum(IsNumeric(Field1)))
FROM YourTable

If that gives you the right count, then your field is almost sure to be text
and storing a zero-length string.
 
Back
Top