Counting records

[Jock W]

On a form, how can I display how many "1"'s "2"'s or "3"'s are in a certain
field in a table?

tia

 

[fredg]

On a form, how can I display how many "1"'s "2"'s or "3"'s are in a certain
field in a table?

tia

In the table? The value to be counted is the only data in the field,
i.e. 2, not 829?
=DCount("*","TableName","[FieldName] = 2")

If you mean something different, post back with more information and a
few examples of what you mean.

 

[Albert D. Kallal]

do you want a complete break down of the counts of all numbers?

Build the following query in query builder:

1st column 2nd column
CertainFieldInTable C:1

now, look at the tool bar while in query design mode, and click on the
summation button (the sizedays M button).

now, you will see:

1st column 2nd column
CertainFieldInTable C:1

Group by Group by

click on the 2nd column group by, and change it to sum...

1st column 2nd column
CertainFieldInTable C:1

Group by Sum

Now,simply save the query.

On your form, use the wizard to build a listbox, and use the above query as
the datasource (table) for the listbox.

It will display all of the 1, 2 , 3 and more totals...

 

[Jock W]

The table field holds data which shows which person is responsible for the
record; ie 1 is person A, 2 would be person B and so on.
I would like (on the form rather than in the table or query) a count of how
many 'A' has, how many 'B' has etc.

Thanks
--
Jock Waddington

On a form, how can I display how many "1"'s "2"'s or "3"'s are in a certain
field in a table?

tia

In the table? The value to be counted is the only data in the field,
i.e. 2, not 829?
=DCount("*","TableName","[FieldName] = 2")

If you mean something different, post back with more information and a
few examples of what you mean.

 

[fredg]

The table field holds data which shows which person is responsible for the
record; ie 1 is person A, 2 would be person B and so on.
I would like (on the form rather than in the table or query) a count of how
many 'A' has, how many 'B' has etc.

Thanks

That's what I gave you.
Add an unbound control to your form.
Set it's control source to:
=DCount("*","TableName","[FieldName] = 1")

Add another unbound control.
Set it's control source to:
=DCount("*","TableName","[FieldName] = 2")

..... etc... for the 3rd control.

Change TableName and FieldName to whatever the actual table and field
names are.

Look up DCount in VBA help.

Also look up, in VBA help,
Where clause + Restrict data to a subset of records.

 

[Jock W]

Works a treat, Thanks.
Field was set to 'text' rather than 'numeric' which was why I couldn't get
it to work.

Thanks to all.
--
Jock Waddington

The table field holds data which shows which person is responsible for the
record; ie 1 is person A, 2 would be person B and so on.
I would like (on the form rather than in the table or query) a count of how
many 'A' has, how many 'B' has etc.

Thanks

That's what I gave you.
Add an unbound control to your form.
Set it's control source to:
=DCount("*","TableName","[FieldName] = 1")

Add another unbound control.
Set it's control source to:
=DCount("*","TableName","[FieldName] = 2")

..... etc... for the 3rd control.

Change TableName and FieldName to whatever the actual table and field
names are.

Look up DCount in VBA help.

Also look up, in VBA help,
Where clause + Restrict data to a subset of records.

 

[Jock W]

Found an issue now.
In a report which uses the same table to retreive data, there's a filter
thus:-
(qryJointWater.fldSanitising)="3" or ISNULL (qryJointWater.fldSanitising)
Since I have changed the data type from 'text' to 'numeric' the report
doesn't work; In fact I get an error message stating that there is a "Data
type mismatch in criteria expression"
Can I alter this expression to work with a numeric field?
--
Jock Waddington

Works a treat, Thanks.
Field was set to 'text' rather than 'numeric' which was why I couldn't get
it to work.

Thanks to all.
--
Jock Waddington

The table field holds data which shows which person is responsible for the
record; ie 1 is person A, 2 would be person B and so on.
I would like (on the form rather than in the table or query) a count of how
many 'A' has, how many 'B' has etc.

Thanks

That's what I gave you.
Add an unbound control to your form.
Set it's control source to:
=DCount("*","TableName","[FieldName] = 1")

Add another unbound control.
Set it's control source to:
=DCount("*","TableName","[FieldName] = 2")

..... etc... for the 3rd control.

Change TableName and FieldName to whatever the actual table and field
names are.

Look up DCount in VBA help.

Also look up, in VBA help,
Where clause + Restrict data to a subset of records.

 

[Jock W]

Removed the "" from around the 3 and it works ok!!

--
Jock Waddington

Found an issue now.
In a report which uses the same table to retreive data, there's a filter
thus:-
(qryJointWater.fldSanitising)="3" or ISNULL (qryJointWater.fldSanitising)
Since I have changed the data type from 'text' to 'numeric' the report
doesn't work; In fact I get an error message stating that there is a "Data
type mismatch in criteria expression"
Can I alter this expression to work with a numeric field?
--
Jock Waddington

Works a treat, Thanks.
Field was set to 'text' rather than 'numeric' which was why I couldn't get
it to work.

Thanks to all.
--
Jock Waddington

On Fri, 16 Feb 2007 08:51:00 -0800, Jock W wrote:

The table field holds data which shows which person is responsible for the
record; ie 1 is person A, 2 would be person B and so on.
I would like (on the form rather than in the table or query) a count of how
many 'A' has, how many 'B' has etc.

Thanks

That's what I gave you.
Add an unbound control to your form.
Set it's control source to:
=DCount("*","TableName","[FieldName] = 1")

Add another unbound control.
Set it's control source to:
=DCount("*","TableName","[FieldName] = 2")

..... etc... for the 3rd control.

Change TableName and FieldName to whatever the actual table and field
names are.

Look up DCount in VBA help.

Also look up, in VBA help,
Where clause + Restrict data to a subset of records.