DSUM criteria

[Kirk P.]

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!

 

[Dale Fye]

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

 

[Kirk P.]

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,

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
--
Email address is not valid.
Please reply to newsgroup only.

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!

 

[Dale Fye]

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
--
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.

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,

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
--
Email address is not valid.
Please reply to newsgroup only.

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!