Calculating Totals in Query

[dualvba]

Hi

I'm trying to build some queries with calculations in, and I'm not able to do what I had hoped. Am I doing it wrong?

This is about sales.

I'd like to firstly, be able to have a quick query, to see how many items a seller listed, with total price (this seems fine, if I count the item IDs, and sum hammer price in a query).

And vice versa, by buyers.

But, I'd like to sum for sellers, number of items Sold, Returned or Relisted.

I have a status (blank for active, "sold, "relisted" or "returned", and thought, I could "count", by the status = "Sold" (ie), but it won't accept the expression.

Ideally, for sellers, I had hoped to have one query, with total items listed, total money made, then totals for still active, sold, returned and relisted items.

Am sure I'm doing something basic wrong, but I can't think what.



Thank you Access guru's in advance.
Clair

[DavidCunnah]

Sounds like you need a SUM(iif(condition,truevalue,nullvalue)) statement. For example, if I want to count the incidences of age = 16 it would read:

sum(iif(person.age=16,1,0))

[dualvba]

Thank you so much David, I'll try that.