help with SQL statement

[Thai]

Hello I am working with an ms access 97 database and Table1 has two columns called ListPrice and CostPrice. Is there a way to return a recordset that only two rows with 2 columns, the columns would be "Field Name" and "Count". The "Field Name" would be ListPrice and CostPrice and "Count" would count how many times that field name had a higher price than the other. For example:

TABLE:
CostPrice ListPrice
2.95 1.95
3.95 5.95
.95 1.95

Returned Recordset:
CostPrice 1
ListPrice 2

Is that possible?

Thanks,
Thai

[Glenn]

I was able to do it by writing 2 queries in Access. The first queries determines which price is higher. The second query references the first one and simply counts the records. Here's the SQL for the queries:

Query 1:

SELECT IIf([listprice]>[costprice],"ListPrice","CostPrice") AS FieldName
FROM Table1;

Query 2:

SELECT Query1.FieldName, Count(Query1.FieldName) AS Count
FROM Query1
GROUP BY Query1.FieldName;

Hope this helps.

[RogerH]

Hi,

SELECT Count(*) AS Count, 'Costprice' as fieldname
FROM Table1
WHERE CostPrice>listprice union SELECT Count(*) AS Count, 'Listprice' as fieldname
FROM Table1
WHERE CostPrice<listprice

Roger