Queries

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

How do I create a query for the the following situation;
two fields in the same table;
Field A; count the number of records
Field B; count the number of records where the result is
less than greater or equal to one
thank you
 
Hi,
I think something like this *might* work for you. Your criteria for Field B doesn't make sense to me.

But in gerneral you can do things like this:

SELECT TOP 1 (Select Count(FieldA) From yourTable) AS fieldCount,
(Select Count(FieldB) FROM yourTable Where FieldB = 1) AS B
FROM yourTable;

Make sense?
 
How do I create a query for the the following situation;
two fields in the same table;
Field A; count the number of records
Field B; count the number of records where the result is
less than greater or equal to one
thank you
Click to expand...

If I understand aright, you want four fields in the result: the count
of records; the count for which FieldB is less than one; the count for
which it is greater than one; and the count for which it is equal to 1
- right?

If so, create a Totals. Query based on the table. Include any field
you know won't be empty, such as the Primary Key; set its Totals
operator to Count to count records.

Then add three more fields:

LessThanOne: IIF([FieldB] < 1, 1, 0)
GreaterThanOne: IIF([FieldB] > 1, 1, 0)
EqualToOne: IIF([FieldB] = 1, 1, 0)

Sum (don't count) these fields.

John W. Vinson[MVP]
 
Back
Top