Calculated field on results in the same query

[becka11]

Hi,



I am wondering if it is possible to make a calculated field on a field in the same query. For example, say I have a products table with products and prices. Then I make a select query to pull all products with a price of zero. In that same query can I also count how many products there are with the price of zero. Or do I NEED to make a second query on top of the first to count all of the products listed in query 1.

This is a simple application, but there are many instances where I have wanted to do this, but always end up having to do two separate queries.

Any help is GREATLY appreciated!!

Thank you

[June7]

Options:

1. the two query method you already know

2. a GROUP BY (Totals) aggregate query, try:
SELECT Product, Price, Count(ID) As CountOfID FROM tablename GROUP BY Product HAVING Price=0;

3. domain aggregate function

4. a report using grouping & sorting features with aggregate calcs in group footer