Recordcount

  • Thread starter Thread starter Paul Blair
  • Start date Start date
P

Paul Blair

I'm working with a 3 column table - the first column has many repeat
items, so I aggregate them into unique values, and total the third
column for each value. The table holds maybe 2000 rows, but there are
only maybe 60 unique values.

I can query the table OK, but I can't count the rows that are generated
- maybe only 35 of the 60 will be represented, and that's the number I
want. I've searched my books and on the web, but can't find a solution.
Suggestions, anyone?

Paul Blair
 
Chris2 said:
Paul Blair,

1) What number of rows are actually returned by your query?

2) What number is displayed by Count(*) (when you include that in a
column on the query grid or in the SELECT clause)?


Sincerely,

Chris O.
Click to expand...
1. There are about 35 (but this varies with time, but that's the current
set)
2. The same as the summary - eg, customer 66 might have made 12
purchases. The count of his purchases is 12, the total of them is $x,
and Count(*) also shows 12.

The SQL currently says: SELECT LA.Field1 AS Recipient, Sum(LA.Field3) AS
[Dollar purchased], Count(LA.Field1) AS [Number purchased]
FROM WA
GROUP BY LA.Field1
ORDER BY LA.Field1;

Cheers

Paul
 
Paul Blair said:
I'm working with a 3 column table - the first column has many repeat
items, so I aggregate them into unique values, and total the third
column for each value. The table holds maybe 2000 rows, but there are
only maybe 60 unique values.

I can query the table OK, but I can't count the rows that are generated
- maybe only 35 of the 60 will be represented, and that's the number I
want. I've searched my books and on the web, but can't find a solution.
Suggestions, anyone?

Paul Blair
Click to expand...

Paul Blair,

1) What number of rows are actually returned by your query?

2) What number is displayed by Count(*) (when you include that in a
column on the query grid or in the SELECT clause)?


Sincerely,

Chris O.
 
The only way to do this would be to have a separate query that just counted
the rows. Where do you need this information?

If you need it in a report then you can add a control to the report's
report footer that has a control source of =Count(*) and that will count the
number of rows in the detail section. You can get it in a query by using a
UNION query..

UNION method:
SELECT LA.Field1 AS Recipient,
Sum(LA.Field3) AS [Dollar purchased],
Count(LA.Field1) AS [Number purchased],
"1" as MyOrder
FROM WA, "1"
GROUP BY LA.Field1
UNION ALL
SELECT "Total", Sum(LA.Field3) AS
[Dollar purchased], Count(LA.Field1) AS [Number purchased],
"2"
FROM WA
GROUP BY "Total","2"
ORDER BY MyOrder, LA.Field1

Paul Blair said:
Chris2 said:
Paul Blair,

1) What number of rows are actually returned by your query?

2) What number is displayed by Count(*) (when you include that in a
column on the query grid or in the SELECT clause)?


Sincerely,

Chris O.
Click to expand...
1. There are about 35 (but this varies with time, but that's the current
set)
2. The same as the summary - eg, customer 66 might have made 12 purchases.
The count of his purchases is 12, the total of them is $x, and Count(*)
also shows 12.

The SQL currently says: SELECT LA.Field1 AS Recipient, Sum(LA.Field3) AS
[Dollar purchased], Count(LA.Field1) AS [Number purchased]
FROM WA
GROUP BY LA.Field1
ORDER BY LA.Field1;

Cheers

Paul
Click to expand...
 
Back
Top