Can i have 'Sum(Distinct table.fldname)' in a query?????

[Rmck87]

Alright i am trying to create a query in Microsoft Access 2000. The query i want to create is to sum up all of the hours for a specific time period. It works fine, but the hours are huge. I found the problem, i was debugging in the SQL Server Query Analyzer and i found that i needed to place a distinct in my 'sum' clause. Like this:


Select sum(distinct e.hours_actual), p.date_start
From e, p
group by p


Now this works perfectly, in query analyzer. But an error comes up saying that there is a missing operator in the select clause, in Access. But it works fine in SQL Server. Why doesn't this work in Acces? Any ideas on how to fix this problem? Thanks in advance.

-Ryan

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[r937]

first of all, may i caution you that using sum(distinct) might give you incorrect answers

for example, if you have hours worked by date --

hours date
7.5 mon
7.5 tue
7.5 wed
8.0 thu
7.5 fri

sum(distinct hours) in the above example will yield 15.5

to answer your question, you may have to do your query in access as a query of a query

define a query called "distincthours" as

select distinct hours_actual as distincthours from e

then your query becomes

select sum(distincthours) from distinct_hours, ...

rudy

http://r937.com/

 

[Rmck87]

I see what you mean, but it seems to work fine, in the Query Analyzer. I tried it the way i started with and that works, but whenever i call a query, it does what you were saying to watch for, and whenever i try a subquery, it does the same mistake. so it only seems to work whenever i say:

Select sum(distinct e.hourst_actual), p.date_start
From e, p
group by p

But, i will keep my eye out for errors. Well, i tried calling a query making that routine and it still calls the same error. Also i tried a subquery, like this:

select p.date_start (select distinct sum(e.hours_actual) from e)
from p
group by p

and that didn't work either. Any ideas? I dont think it likes the idea of Distinct being in there again. But it has to be in ther in order for it to work. But maybe there is another way i can do it?

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

ANYONE???

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.