how to do this query

  • Thread starter Thread starter coedxiao via AccessMonster.com
  • Start date Start date
C

coedxiao via AccessMonster.com

I have one data source as following. Can I get the following calcualtion in
one qry?

select sum(ingred_cost) where priceing='mod' / sum(ingred_cost) where
durg_type='generic'

data:

pricing TYPE ingred_cost
awp BRAND 71835.65
awp GENERIC 7485.07
mod BRAND 162
mod GENERIC 9618.87

Thanks!
 
Try:

SELECT Sum(Abs([Pricing]="mod") * [Ingred_cost])/Sum([ingred_cost]) as
ModPctForGeneric
FROM [one data source]
WHERE [Type] = "generic";
 
Try these queries --

Coedxaio-GENERIC --
SELECT Null AS X, Null AS Y, Coedxiao.TYPE, Sum(Coedxiao.ingred_cost) AS
SumOfingred_cost
FROM Coedxiao
GROUP BY Null, Null, Coedxiao.TYPE
HAVING (((Coedxiao.TYPE)="generic"));

Coedxaio-MOD --
SELECT Coedxiao.pricing, Sum(Coedxiao.ingred_cost) AS SumOfingred_cost, Null
AS X, Null AS Y
FROM Coedxiao
GROUP BY Coedxiao.pricing, Null, Null
HAVING (((Coedxiao.pricing)="mod"));

SELECT [Coedxaio-GENERIC].TYPE AS [Criteria],
[Coedxaio-GENERIC].SumOfingred_cost AS Cost
FROM [Coedxaio-GENERIC]
UNION SELECT [Coedxaio-MOD].pricing, [Coedxaio-MOD].SumOfingred_cost
FROM [Coedxaio-MOD];
 
Re-read post and see my error --
Coedxaio-GENERIC --
SELECT 1 AS X, Sum(Coedxiao.ingred_cost) AS SumOfingred_cost
FROM Coedxiao
GROUP BY 1, Coedxiao.TYPE
HAVING (((Coedxiao.TYPE)="generic"));

Coedxaio-MOD --
SELECT 1 AS X, Sum(Coedxiao.ingred_cost) AS SumOfingred_cost
FROM Coedxiao
GROUP BY 1, Coedxiao.pricing
HAVING (((Coedxiao.pricing)="mod"));

SELECT
[Coedxaio-MOD].[SumOfingred_cost]/[Coedxaio-GENERIC].[SumOfingred_cost] AS
Expr1
FROM [Coedxaio-GENERIC] INNER JOIN [Coedxaio-MOD] ON [Coedxaio-GENERIC].X =
[Coedxaio-MOD].X;
 
Thank you for replying. Karl's statements worked great. I could not get
Duane's statements to work though.

Thanks a lot!!


KARL said:
Re-read post and see my error --
Coedxaio-GENERIC --
SELECT 1 AS X, Sum(Coedxiao.ingred_cost) AS SumOfingred_cost
FROM Coedxiao
GROUP BY 1, Coedxiao.TYPE
HAVING (((Coedxiao.TYPE)="generic"));

Coedxaio-MOD --
SELECT 1 AS X, Sum(Coedxiao.ingred_cost) AS SumOfingred_cost
FROM Coedxiao
GROUP BY 1, Coedxiao.pricing
HAVING (((Coedxiao.pricing)="mod"));

SELECT
[Coedxaio-MOD].[SumOfingred_cost]/[Coedxaio-GENERIC].[SumOfingred_cost] AS
Expr1
FROM [Coedxaio-GENERIC] INNER JOIN [Coedxaio-MOD] ON [Coedxaio-GENERIC].X =
[Coedxaio-MOD].X;
I have one data source as following. Can I get the following calcualtion in
one qry?
Click to expand...
[quoted text clipped - 11 lines]
Thanks!
Click to expand...
Click to expand...
 
Back
Top