Using Aggregate fields in an Access Report

[Apschminkey]

I have created a query which produces a list of customers and their sum-totals (Expr1: Sum([price])of sales for a given period of time (using "where"). The datasheet view gives me the correct results. If I create a report based on the above query, everything seems ok until Access tries to display the report. At that point, it gives me the following error: [ODBC Visual Foxpro Driver] SQL Column' is not found. (#806). I get exactly the same message if, in the datasheet view of the query, I try to sort the calculated column ascending or descending. If I sort by customer name the report will display. I assume that by attempting to sort the "sum" column I am asking to sort something that doesn't really exist. In the end what I want to do is produce a report which shows total sales in decreasing order so that it is easy to identify, fox example, the top 10 or 50 clients. Seems like it should be easy, but I'm just a biologist. Thanks!

 

[dhookom]

Did you use a column alias in another column expression? I always avoid this since it isn't reliable. If this doesn't help, come back with your SQL view.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Apschminkey]

Duane:

I didn't use the alias in anotehr column but I tried to sort the "aliased" column. Here is the SQL view. EXP (the sum of prices) is the value which I would like to sort.

SELECT comp.cnum, Sum(samp_lns.price) AS EXP
FROM (samp INNER JOIN samp_lns ON samp.labnum = samp_lns.labnum) INNER JOIN [comp] ON samp.cnum = comp.cnum
WHERE (((samp_lns.reportdate) Between #7/1/2004# And #7/31/2004#) AND ((samp.area)="GEN"))
GROUP BY comp.cnum;

Thanks. dan

 

[dhookom]

Exp" is a function name in Access. You might want to change the alias to something different like SumOfPrice.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.