DSUM criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kirk P. - 21 Aug 2006 21:11 GMT

I've got this SQL in a select query called 8_qselWF_Prod:

SELECT Year, Period, Entity, Function, Prod, AllocMeth, Sum([Sum Monetary
Amount]) AS ProdAmt, CDbl(DSum("[ProdAmt]","8_qselWF_Prod")) AS ProdTtl,
CDbl([ProdAmt]/[ProdTtl]) AS Pct
FROM tblISData INNER JOIN tblAllocMeth_Func ON Function = Function
GROUP BY Year, Period, Entity, Function, Prod, AllocMeth
HAVING (((Entity)="Optum") AND ((Prod) Not Like "9*") AND ((AllocMeth)="WF"))
ORDER BY Function, Prod, AllocMeth;

I want the DSum function to return the product totals by function. I've
tried critiera in the DSum function such as "[Function] = " & [Function], but
so far no luck. Help!

Reply to this Message

Dale Fye - 21 Aug 2006 21:38 GMT

Kirk,

Unfortunately, you cannot use the alias of columns that are computed within
a query as the source for another column within your query, so your
references to [ProdAmt] and [ProdTtl] are not going to work.

My recommendation would be to create several subqueries, on to get the basic
ProdAmt by AllocMethod, another to get the ProdTtl values, then join the two
on the appropriate fields and do your division.

HTH
Dale

Signature

Email address is not valid.
Please reply to newsgroup only.

> I've got this SQL in a select query called 8_qselWF_Prod:
>
[quoted text clipped - 9 lines]
> tried critiera in the DSum function such as "[Function] = " & [Function], but
> so far no luck. Help!

Reply to this Message

Kirk P. - 21 Aug 2006 22:14 GMT

I'm trying to get this result using one select query. The ProdTtl field is
the sum of ProdAmt within each Function.

Function    Prod    ProdAmt    ProdTtl
1010    100    7,782.36    279,559.20
1010    110    136,448.37 279,559.20
1010    315    126,519.59 279,559.20
1010    345    8,808.88    279,559.20
1045    100    36,480.91    96,222.96
1045    110    1,506.30    96,222.96
1045    300    43,330.25    96,222.96
1045    325    14,905.50    96,222.96

I'm playing around with subqueries, but thus far haven't come up with
anything that works.

> Kirk,
>
[quoted text clipped - 22 lines]
> > tried critiera in the DSum function such as "[Function] = " & [Function], but
> > so far no luck. Help!

Reply to this Message

Dale Fye - 22 Aug 2006 19:58 GMT

You could do it with nested sub-queries, but it would not be pretty and would
probably not run as quickly as doing it this way. Your example below does
not quite match the original SQL, so I'll use your latest example and you can
work from there.

Query1:

SELECT Function, Prod, Sum([Sum Monetary Amount]) AS ProdAmt
FROM yourTable
GROUP BY Function, Prod

Query 2:

SELECT Function, Sum([Sum Monetary Amount]) as FunctionTtl
FROM yourTable
GROUP BY Function

Query 3:

Select Q1.Function, Q1.Prod, Q1.ProdAmt, Q2.FunctionTtl,
Cdbl(Q1.ProdAmt)/cdbl(Q2.FunctionTtl) as ProdPct
FROM Query1 Q1 JOIN Query2 Q2
ON Q1.Function = Q2.Function

HTH
Dale

Signature

Email address is not valid.
Please reply to newsgroup only.

> I'm trying to get this result using one select query. The ProdTtl field is
> the sum of ProdAmt within each Function.
[quoted text clipped - 38 lines]
> > > tried critiera in the DSum function such as "[Function] = " & [Function], but
> > > so far no luck. Help!

Reply to this Message