syntax error in query

[zoe.ohara]

Hi,



I have a table that has a numeric field 'TransferNo'
Im trying to write a query that calculates the total number of records that have a TransferNo that is GREATER than the average of all the TransferNo in that table.

My expression is:

SELECT Count(*) AS Result
FROM Table1
WHERE (
([Table1.TransferNo])>(AVG[Table1.TransferNo])
);


I keep getting syntax errors. Anyone know what the problem is?

Thanks,

Zoe

[weekend00]

SELECT Count(*) AS Result
FROM Table1
WHERE (
([Table1.TransferNo])>DAVG("TransferNo","table1")
);

[zoe.ohara]

Hi, thanks for the help! Unfortunatley that gives me:

Data Mismatch in criteria expression

[weekend00]

is "transferNo" a number field?
if is, data type should match; if not, there is no average on transferNo.

[zoe.ohara]

is "transferNo" a number field?
if is, data type should match; if not, there is no average on transferNo.

Yes its a number field

Cant understand why its not working

[weekend00]

Please convert DAVG to the same data type of transferNo, e.g. if transferNo is long integer:

SELECT Count(*) AS Result
FROM Table1
WHERE (
([Table1.TransferNo])>clng(DAVG("TransferNo","table1"))
);

[ivanver]

Try, if you are still interested:

SELECT Count(*) AS Result
FROM Table1 t
WHERE
t.TransferNo>(select AVG (tn.TransferNo)
from Table1 tn);